SUMIFs with multiple criteria and revert to one or more criteria

[benntw]

Currently I am having SUMIFs kick my butt. I have a data dump from P6 and I want to be able to filter through 4 categories. That part is not the issue. If I want to filter for one category I want the other three to view all the data. Here are my parameters

Phase - PH
Work Order - WO
Company - CO
Equipment - EQ

I have a lookup if I select the words Phase, Work Order, Company, or Equipment the cell in column B shows a value of "ALL". If the words are not selected then the cell will populate with the value from the dropdown list. I tried using an if statement in the beginning for "ALL" and it works with just one in the formula. I tried to using multiple IF statements, but it failed to produce the correct data. The formula below is the one I am using as long as nothing says all. My end goal is to be able to look at all the hours for a company or all the hours for multiple companies under one work order, etc.

SUMIFS(Data!U:U,PH,$B2,WO,$B5,CO,$B8,EQ,$B11)

Hopefully some one can help out on this since it has kicked my butt. Thank you

 

[Eric W]

Maybe:

=SUMIFS(Data!U:U,PH,IF($B2="ALL","*",$B2),WO,IF($B5="ALL","*",$B5),CO,IF($B8="ALL","*",$B8),EQ,IF($B11="ALL","*",$B11))

 

[benntw]

Appreciate the formula. It resulted in the same as the one I tried writing. It just gives the value of the first row which is the header names(Dates).

 

[Eric W]

Could you show a sample of your sheet? It would make it easier to see what you're looking for. There is a tool called XL2BB here (see the link in my signature) which makes it easy to share your sheet.

 

[benntw]

I tried adding the tool. I have a company computer and it would not let me. What I want to do is pick any or all of the dropdowns. I want to be able either to view all the hours or select one of or all of the dropdowns to get to the specific criteria.