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
-
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?
-
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?
-
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?
-
Help Article Resources
Categories
Check out the Formula Handbook template!