Help... Counting Text in a range of cells

[Mykro]

I'm trying to count Text in a range of cells..
=IF(ISNUMBER(B6),COUNTA(City!G6,City!J6,City!M6,City!P6,City!S6,City!V6,City!Y6),"")

But it Seems that COUNTA includes formulas too.. The cells also has a drop down list too.. When the user chooses a city from one of those cells I would like the formula just to total the occurences..

Thanks..

 

[Domenic]

Try...

=IF(ISNUMBER(B6),SUMPRODUCT(--(MOD(COLUMN(City!G6:Y6)-COLUMN(City!G6),3)=0),--(City!G6:Y6<>"")),"")

 

[Mykro]

Try...

=IF(ISNUMBER(B6),SUMPRODUCT(--(MOD(COLUMN(City!G6:Y6)-COLUMN(City!G6),3)=0),--(City!G6:Y6<>"")),"")

It Didn't work... I wonder if it is because in my formula I sent it dose not include the other cells which include numbers.. H6,I6 - K6,L6 - N6,O6 - Q6,R6 - T6,V6 - W6,X6 or does it matter?

 

[Domenic]

It Didn't work...

Can you be specific? Does it return the wrong answer? Or does it return an error? If the latter, which error value? If the target cells can contain numbers, as well as text and formula blanks (""), then try the following instead...

=IF(ISNUMBER(B6),SUMPRODUCT(--(MOD(COLUMN(City!G6:Y6)-COLUMN(City!G6),3)=0),--(City!G6:Y6<>""),--ISTEXT(City!G6:Y6)),"")

I wonder if it is because in my formula I sent it dose not include the other cells which include numbers.. H6,I6 - K6,L6 - N6,O6 - Q6,R6 - T6,V6 - W6,X6 or does it matter?

The MOD function in the first argument ensures that only target cells that meet the criteria are considered in the count.