How do you add 2 SUMIFS in same function or SUM of Range based on 2 values in SUMIFS Criteria Range?
I have a schedule that has locations in different regions and a install status picklist. I need to create a sheet summary function that calculates a numerical value from one column based on the region and the status. I have two SUMIFS formulas that work separately but I can not add them together nor can I get the logic of AND,OR,CONTAINS to work when nested within the function. Here is what I have so far:
=SUMIFS([Actual # of Cams]1:[Actual # of Cams]235, [Install Status]1:[Install Status]235, "Complete", Region1:Region235, "Carolina") {Works}
=SUMIFS([Actual # of Cams]1:[Actual # of Cams]235, [Install Status]1:[Install Status]235, "Completed w/ Conditions", Region1:Region235, "Carolina") {Works}
=SUMIFS([Actual # of Cams]1:[Actual # of Cams]235, [Install Status]1:[Install Status]235, "Complete", Region1:Region235, "Carolina") + SUMIFS([Actual # of Cams]1:[Actual # of Cams]235, [Install Status]1:[Install Status]235, "Completed w/ Conditions", Region1:Region235, "Carolina") {Does NOT Work}
As you can see, I need to add the value in [Actual # of Cams]1:[Actual # of Cams]235 based on the the Region and the two Completion Statuses combined. Has anyone tried to do something similar before and have any advice on where the syntax is broken?
Best Answer
-
Fixed! Had the criteria in the wrong order. See below:
=SUMIFS([Actual # of Cams]1:[Actual # of Cams]235, Region1:Region235, "Carolina", [Install Status]1:[Install Status]235, "Complete") + SUMIFS([Actual # of Cams]1:[Actual # of Cams]235, Region1:Region235, "Carolina", [Install Status]1:[Install Status]235, "Completed w/ Conditions")
Answers
-
try:
=SUM(SUMIFS([Actual # of Cams]1:[Actual # of Cams]235, [Install Status]1:[Install Status]235, "Complete", Region1:Region235, "Carolina"), SUMIFS([Actual # of Cams]1:[Actual # of Cams]235, [Install Status]1:[Install Status]235, "Completed w/ Conditions", Region1:Region235, "Carolina"))
-
@Leibel S Thank you for the help. Unfortunately it doesn't completely work. When applied it calculates only this portion of the string: [Actual # of Cams]1:[Actual # of Cams]235, [Install Status]1:[Install Status]235, "Complete", Region1:Region235, "Carolina")
-
Try:
=SUMIFS([Actual # of Cams]1:[Actual # of Cams]235, [Install Status]1:[Install Status]235, or(@cell="Complete", @cell="Completed w/ Conditions"), Region1:Region235, "Carolina")
-
Fixed! Had the criteria in the wrong order. See below:
=SUMIFS([Actual # of Cams]1:[Actual # of Cams]235, Region1:Region235, "Carolina", [Install Status]1:[Install Status]235, "Complete") + SUMIFS([Actual # of Cams]1:[Actual # of Cams]235, Region1:Region235, "Carolina", [Install Status]1:[Install Status]235, "Completed w/ Conditions")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 396 Global Discussions
- 213 Industry Talk
- 448 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 133 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!