How to use countif with more than 3 ranges?

[CANNONT4]

Hi there,

I am trying to count how many times "2b" appears in 9 seperate ranges, like this for example:

=COUNTIF(H4:H19,J4:J19,L4:L19,N4:N19,N4:N19,P4:P19,R4:R19,T4:T19,V4:V19,X4:X19"2b")

It says I have too many arguments and would appear to allow me to count up to only 3 data ranges. Any ideas how to fix this?

Many thanks,

Tom.

 

[CANNONT4]

oh ya...wh

y your COUNTIFS didnt work is because COUNTIFS is for multiple crtiteria...not multiple cell

That's what I thought, but countif wont allow me to select more than 3 ranges, e.g. H4:H19, J4:J19, L4:L19

 

[Tivakaran]

That identifies 2 counts of 2b.

Yeah..because you only have 2 count of 2b which is in H4 & P4?... What is the correct supposely answer then?

 

[CANNONT4]

Yeah..because you only have 2 count of 2b which is in H4 & P4?... What is the correct supposely answer then?

That is correct but I don't want it counting all those columns, only H,J,L,N,P,R,T,V and X. The answer will be correct for 2b but not when I go to count other data such as 3a.

 

[Tivakaran]

That is correct but I don't want it counting all those columns, only H,J,L,N,P,R,T,V and X. The answer will be correct for 2b but not when I go to count other data such as 3a.

I understand that, thats why I checked the other column first & I see all data starts with 3 & 4..means they wotn interfere...unless you intend to put 2b also in those columns

 

[CANNONT4]

I understand that, thats why I checked the other column first & I see all data starts with 3 & 4..means they wotn interfere...unless you intend to put 2b also in those columns

I need two separate counts, one for all columns headed EoY and one for all columns headed EoPH, some data such as 3a appears in both EoY and EoPH so if I simply count the whole table I will have incorrect counts as 3a appears in both EoY and EoPH. Does that make sense? So I need to count those columns separately, hense why I cant simply do a countif from h4 to x19.

Your suggestion works fine when I am looking for 2b, but I need to go on to look for 2a, 3c, 3b, 3a and so on.

 

[Tivakaran]

I need two separate counts, one for all columns headed EoY and one for all columns headed EoPH, some data such as 3a appears in both EoY and EoPH so if I simply count the whole table I will have incorrect counts as 3a appears in both EoY and EoPH. Does that make sense? So I need to count those columns separately, hense why I cant simply do a countif from h4 to x19.

Your suggestion works fine when I am looking for 2b, but I need to go on to look for 2a, 3c, 3b, 3a and so on.

ok...so you need use COUNTIFS...not countif

=COUNTIFS(H4:H12,"2b",J4:J12,"2b",L4:L12,"2b",N4:N12,"2b")

like above but you need modify the cell...try it

 

[Tivakaran]

oh wait sorry... you need use COUNTIF... it will work like this but formula will be lenghty

=COUNTIF(H4:H19,"2b")+COUNTIF(J4:J19,"2b")+countif........and so on

 

[CANNONT4]

oh wait sorry... you need use COUNTIF... it will work like this but formula will be lenghty

=COUNTIF(H4:H19,"2b")+COUNTIF(J4:J19,"2b")+countif........and so on

That's the ticket! Cheers mate. Sorted!

 

[Fluff]

Rather than multiple countif formulae, you can use

=SUMPRODUCT((H4:X19="2b")*(MOD(COLUMN(H4:X19),2)=0))