Looking for a more elegant way to write the following countifs formula

[willow1985]

Hello,

was wondering if anyone could help me with the below formula. Is there a more elegant way of writing it rather than adding each additional section?

I want the formula to count E:E if it matches Z2, and if S:S matches B12, C12 and D12. Would this be some sort of Countifs/And formula?

=COUNTIFS('WO Report'!$E:$E,Z2,'WO Report'!$S:$S,$B$12)+COUNTIFS('WO Report'!$E:$E,Z2,'WO Report'!$S:$S,$C$12)+COUNTIFS('WO Report'!$E:$E,Z2,'WO Report'!$S:$S,$D$12)

Thank you for all of your help

Carla

 

[Special-K99]

Not much more elegant, think you got it about as good as its gonna get. This is pretty much the same

=SUMPRODUCT(('WO Report'!E:E=Z2)*(('WO Report'!S:S=B12)+('WO Report'!S:S=C12)+('WO Report'!S:S=D12)))

 

[willow1985]

=COUNTIFS('WO Report'!$E:$E,Z2,'WO Report'!$S:$S,$B$12,'WO Report'!$S:$S,$C$12,'WO Report'!$S:$S,$D$12)

This formula does not work but is there a reason why?

the reason I ask is that this formula works in another workbook I have and I thought the conditions were the same.

The other workbook where the formula works:

=COUNTIFS(Report!$M:$M,A7,Report!$A:$A,'Weekly Metrics'!$B$4,Report!$P:$P,'Weekly Metrics'!$D$4)

 

[Fluff]

The countifs function works on an AND basis, so you are looking for any cell in col S that matches B12 & C12 & D12.
So unless B12,C12 & D12 are all the same value, the formula will give you 0.

 

[pgc01]

=COUNTIFS('WO Report'!$E:$E,Z2,'WO Report'!$S:$S,$B$12,'WO Report'!$S:$S,$C$12,'WO Report'!$S:$S,$D$12)

This formula does not work but is there a reason why?

What you are saying is that you want

'WO Report'!$S:$S

to be equal to $B$12 AND to be equal to $C$12 AND to be equal to $D$12

and I think you want OR, not AND

Try instead:

=SUMPRODUCT(COUNTIFS('WO Report'!$E:$E,Z2,'WO Report'!$S:$S,$B$12:$D$12))

 

[willow1985]

That works. Thank you very much