Having COUNTA ignore cells with formulas

[Wulluby]

Hi,


If I have a column of say 10 rows all of which contain a formula, 5 of which have TEXT values returned can I total those which just return the text? If I use counta it is counting the formulas as well.


Thanks.

 

[Luke M]

How about:

=COUNTA(A:A)-COUNT(B:B)


Can't tell whether the cells contain a formula or not, but you can do this to get a count of only cells that contain text (as it subtracts the count of all cells that contain numbers)

 

[Wulluby]

Hi Luke,


That was still counting the formulas but modified it to

=COUNTA(C$2:C$162)-COUNTIF(C$2:C$162,"")


And that seems to have done the job for me.


Thankyou!

 

[NARAYANK991]

Hi ,


Just to add to whatever dialogue has taken place ;


In your last post , the COUNTIF(C$2:C$162,"") can be replaced by =COUNTBLANK(C$2:C$162) with the same result.


Narayan

 

[Wulluby]

Thanks Narayan.

 

[rogerimee]

Hi,


Just to add one more formula that I think it is more easier.


if your formula is in Column A, you can write this formula in B1


=COUNTIF(A:A,"?*")

 

[Wulluby]

Thanks Rogerimee