Sumifs with multiple criteria

Amy Broeker

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!

Paul Newcome

Which range are you trying to sum?

Amy Broeker

Medxcel Fire Invoices Range 5

Paul Newcome

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?

Amy Broeker

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.

Paul Newcome

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?

Amy Broeker

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?

Paul Newcome

It is possible. What was the formula you removed?