CountA

[martialtiger]

How can I prevent CountA from counting cells with forumlas? I though
CountA counts only cells in a range that contain a value.

Here's the scenario:

I have a formula: > =IF('Alex P.'!D10="","",AVERAGE('Alex P.:Vinutha G.'!D10))

And this forumla runs down column C. Now in another cell I want t
count only the cells down column C that display an actual figure. Hop
this makes sense.

Thanks for the help!

:cool

 

[Frank Kabel]

Hi
you ran into a typical Excel problem. The formula result "" is not
intrepreted as empty cell by COUNTA. One workaround would be to use the
following array formula (enter with CTRL+SHIFT+ENTER)
=SUM(IF(C1:C100<>"",1,0))
or the non array version:
=SUMPRODUCT(--(C1:C100<>""))

HTH
Frank

 

[Peo Sjoblom]

Another way

=COUNTA(B1:B20)-COUNTBLANK(B1:B20)

 

[Leo Heuser]

One way:

=COUNTA(C1:C10)-COUNTIF(C1:C10,"")

 

[dcronje]

The standard COUNT formula would not count the cells with ""
in i

 

[Peo Sjoblom]

Good point.

 

[martialtiger]

Thank you everyone for your responses! It's working

 

[hilihili]

your solution of COUNTBLANK is very nice, however, I need the COUNTA i
a dynamic (SUBTOTAL) range were I can only use COUNT and COUNTA (wit
the "" that results from a formula). Any suggestions?

thanks