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.

Any thoughts?

Edit:

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?

*Tony*

*We have more and more laws, and less and less enforcement*

*Edited by cheshire_man (Sat 20-Apr-13 22:54:08)*