Sumifs with multiple criteria
I am trying to sum the Open amount on another sheet if it meets 2 criteria. If the dispute box is checked in a column and if there is a 1 in the FM PO column. Here is are the formulas I have tried this far with no luck:
=SUMIF({Medxcel Fire Invoices Range 5}, OR(CONTAINS(1, {Medxcel Fire Invoices Range 4}), CONTAINS(1, {Medxcel Fire Invoices Range 1}))) - this formula is coming back as 0 and I know it is not 0.
=SUMIFS({Medxcel Fire Invoices Range 5}, {Medxcel Fire Invoices Range 4}, 1, {Medxcel Fire Invoices Range 1}, 1) - this formula is coming back as 0 and I know it is not 0.
Thank you!
Answers
-
Which range are you trying to sum?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Medxcel Fire Invoices Range 5
-
OK. The second SUMIFS should be working.
Which range is the checkbox and which range should contain the 1? How is the 1 populated in the source sheet?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
The Medxcel Fire Invoices Range 5 range is a checkbox and it works by itself without the other range. {Medxcel Fire Invoices Range 1} is the number 1.
I just tried {Medxcel Fire Invoices Range 1} without the 1st range and it comes back as 0, so I am not sure if there is something wrong with that part of the formula.
-
Are you trying to sum or count?
You said earlier that Range 5 is the range you want to sum, but in your latest comment you say it is a checkbox.
What is Range 4?
How is the 1 populated in Range 1?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
I figured out what the issue was. I have a contains formula in the {Medxcel Fire Invoices Range 1}, 1 column. When I deleted the formula on the sheet, this formula worked. I still need the formula in the column or it will create a manual process. Is it not possible to have a formula in a criteria column for SUMIFS?
-
It is possible. What was the formula you removed?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!