Excluding Criteria in a COUNTIFS formula in Excel

[excelbytes]

I have this simple formula:

=COUNTIFS('Emp List'!C2:C16,B2,'Emp List'!B2:B16,C2,'Emp List'!E2:E16,D2,'Emp List'!F2:F16,E2)

However, if any of the criteria in B2, C2, D2 or E2 equals "Overall", I want to ignore that criteria range and criteria. For example, if cell C2 = "Overall", then the formula would in essence calculate as follows:

=COUNTIFS('Emp List'!C2:C16,B2,'Emp List'!E2:E16,D2,'Emp List'!F2:F16,E2)

That could happen with any combination of criteria.

Is there a simple way to accomplish this?

 

[Fluff]

Are the values you are counting text or numbers?

 

[excelbytes]

Text

 

[Fluff]

Ok, you can do that like

=COUNTIFS('Emp List'!C2:C16,IF(B2="","*",B2),'Emp List'!B2:B16,IF(C2="","*",C2),'Emp List'!E2:E16,D2,'Emp List'!F2:F16,E2)

Just repeat what I've done for B2 & C2 for the others.

 

[excelbytes]

Ok, you can do that like

=COUNTIFS('Emp List'!C2:C16,IF(B2="","*",B2),'Emp List'!B2:B16,IF(C2="","*",C2),'Emp List'!E2:E16,D2,'Emp List'!F2:F16,E2)

Just repeat what I've done for B2 & C2 for the others.

That did it, thanks! I didn't think about using a wild card!

 

[Fluff]

You're welcome & thanks for the feedback.