Need help with a SUMIF formula to sum the number of credits with multiple AND and OR functions
This is a continuation of the question that was answered here: https://community.smartsheet.com/discussion/91230/need-help-calculating-number-of-credits-with-multiple-and-and-or-functions#latest
The answerer found the correct formula! Results are in the screenshot below.
I need help creating the correct formula syntax in a different sheet (that would reference the above sheet) that follows the following logic:
Sum the credits from the January column (see above) if Assigned To = Cathy's email address AND (Start Month = 1 AND Start Year = 2022) OR (End Month = 1 AND End Year = 2022)
The goal is to sum the credits from the sheet above by Month, Year and colleague name (the Assigned To column above is a Contact List field) so that we can populate the grid below (which exists in a separate sheet):
Thanks!
Best Answer
-
Okay, you can't use AND() without two conditions. Since you removed those, you only have two OR() conditions. So we take out the AND() functions, and separate the OR() conditions with parentheses.
We're going to do it a simpler way. Create a helper column in the original sheet called Year 2022. Give it a formula similar to =IF(OR(Range 4@row = 2022, Range 5@row = 2022), 1, 0).
Now you can hide that column.
Again, I highly recommend changing your ranges to describe the column or range you are capturing information from. It is incredibly confusing to deal with values like Range1, Range 2, etc.
Try something like this:
=SUMIFS({Exec Services Backlog v3 Range 2}, {Exec Services Backlog v3 Range 1}, "catherine_a_gray@uhc.com", {Exec Services Backlog v3 Range Year 2022}, 1)
Now you don't have to deal with Complicated logic in your SUMIFS() function.
Hope this helps.
Answers
-
Tried this (after realizing I don't have to factor in Start Month or End Month columns since that's already factored into the initial credit calculation) and got #Incorrect Argument Set error:
=SUMIF({Exec Services Backlog v3 Range 2}, {Exec Services Backlog v3 Range 1}, "catherine_user@website.com", (AND(OR({Exec Services Backlog v3 Range 4}, @cell = 2022), {Exec Services Backlog v3 Range 5}, @cell = 2022)))
-
Hello @mariahneu,
It looks like you need to use SUMIFS instead of SUMIF due to your need for multiple conditions.
Please change your formula to something like the one below:
=SUMIFS({Exec Services Backlog v3 Range 2}, {Exec Services Backlog v3 Range 1}, "catherine_user@website.com", OR(AND({Exec Services Backlog v3 Range 4}, @cell = 2022, {Start Month (ESB)}, @cell = 1), AND({Exec Services Backlog v3 Range 5}, @cell = 2022, {End Month (ESB)}, @cell = 1)))
You might also consider naming your ranges to be something recognizable for when your formulas get so large. I like to name mine in a format like "Column Name(sheet acronym)" so for the sheet Exec Services Backlog, and column month, I would name it "Month (ESB)". Just a helpful tip to make life easier troubleshooting your own formulas.
I wasn't able to test this formula, but I think the logic behind the OR/AND should work. It also might not have the correct number of parenthesis.
Hope this helps!
-
Thank you! Found out I don't need to use Start Month or End Month since that's calculated in the previous formula, but tried this version with SUMIFS and I receive an #INVALID DATA TYPE error.
=SUMIFS({Exec Services Backlog v3 Range 2}, {Exec Services Backlog v3 Range 1}, "catherine_a_gray@uhc.com", OR(AND({Exec Services Backlog v3 Range 4}, @cell = 2022), AND({Exec Services Backlog v3 Range 5}, @cell = 2022)))
-
Okay, you can't use AND() without two conditions. Since you removed those, you only have two OR() conditions. So we take out the AND() functions, and separate the OR() conditions with parentheses.
We're going to do it a simpler way. Create a helper column in the original sheet called Year 2022. Give it a formula similar to =IF(OR(Range 4@row = 2022, Range 5@row = 2022), 1, 0).
Now you can hide that column.
Again, I highly recommend changing your ranges to describe the column or range you are capturing information from. It is incredibly confusing to deal with values like Range1, Range 2, etc.
Try something like this:
=SUMIFS({Exec Services Backlog v3 Range 2}, {Exec Services Backlog v3 Range 1}, "catherine_a_gray@uhc.com", {Exec Services Backlog v3 Range Year 2022}, 1)
Now you don't have to deal with Complicated logic in your SUMIFS() function.
Hope this helps.
-
It worked! Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
