So I have the formula
=COUNTIFS(G3:G39,5,L3:L39,5,Q3:Q39,5,V3:V39,5,AA3:AA39,5,AF3:AF39,5,AK3:AK39,5,AP3:AP39,5,AU3:AU39,5,AZ3:AZ39,5,BE3:BE39,5,BJ3:BJ39,5)It doesn't (seem to) make any difference whether the criteria is expressed as 5 or "5" or "=5".
4 of the columns contain an occurrence of the tested value, but the formula returns 0
If I use the formula
=COUNTIFS(L3:L39,5,Q3:Q39,5)which evaluates 2 of the columns actually containing the value 5 it returns 1. And, I'm sure this is a clue but I'm darned if I can see it, if I delete either of the 5 values the result is 0. So it's clearly testing both columns.
When editing the formula Excel correctly highlights all 12 columns.
I've just tried changing the formula to summing the results of 12 separate CountIf functions
=COUNTIF(G3:G39,5)+COUNTIF(L3:L39,5)+COUNTIF(Q3:Q39,5)+COUNTIF(V3:V39,5)+COUNTIF(AA3:AA39,5)+COUNTIF(AF3:AF39,5)+COUNTIF(AK3:AK39,5)+COUNTIF(AP3:AP39,5)+COUNTIF(AU3:AU39,5)+COUNTIF(AZ3:AZ39,5)+COUNTIF(BE3:BE39,5)+COUNTIF(BJ3:BJ39,5)and it correctly gives the result 4.
So have I misunderstood the CountIfS function?
We have more and more laws, and less and less enforcement
Edited by cheshire_man (Sat 20-Apr-13 22:54:08)