SUMIFS Problem

Hello Excel Forum.
Would really appreciate some help.

My SUMIFS has 4 criteria. 3 of which are in drop-down lists.

The spreadsheet works perfectly when Status2 is set to any value (which is what I desire)

But as soon as I set the next criteria, Status1 to any value in the drop-down, then my spreadsheet goes to all zeros. But the values will pop up if I set the next criteria,Loss Threshold.

I would like the spreadsheet to show the values when I have Status1 and Status2 criteria set.

Summary:
Just Status 1 set: perfect
Status1/Status2/Loss Threshold set: perfect
Status1 and Status2 set: no data shows - this is the problem

(Note: I can get the right result if I use sumproduct instead of SUMIFS, but I want to use SUMIFS for this spreadsheet)

Many thanks Forum - Hope this is an interesting case to look at and learn from
denise

Try revising formula a bit to:

Please Login or Register  to view this content.

in C11, copied across the matrix.

The formula now coerces blank B6 to 0 by adding +0 and it coerces blanks into "all" by adding * wildcard to the B2 and B4 criteria.

Hello NBVC.
Always so reliable. Really really appreciate the quick reply. I would have never thought of your solution.

It works!

The IF part of the formula becomes redundant - so removing this and using your formula now all the combinations that I desire in the dropdown work and produces data instead of zeros.

Here is the formula:

PHP Code: 

=SUMIFS('Raw Data'!D$2:D$235,'Raw Data'!$T$2:$T$235,">="&$B$6+0,'Raw Data'!$C$2:$C$235,$B$2&"*",'Raw Data'!$B$2:$B$235,$B$4&"*",'Raw Data'!$A$2:$A$235,$B11) 


Revised spreadsheet re-uploaded for those who want to see it in action.

Thank you NBVC - What a guy! and Roy UK too!
denise