COUNTA for a cell with a formula

[Steve Jackson]

I have tried setting a COUNTA formula on an array. The cells in the array
all have a similar formula in each one and will contain text if a specific
condition is met in another set of cells. The problem I am getting is that
when I put in the formula =COUNTA(A1,B1,C1), I get the result of all of them
not being empty. Is this because all those cells contain formulas, even if
text is there or not. If so, is there a similar function to COUNTA that will
count cells and not take into account that a formula is already in the cell?

Any help will be appreciated.

Steve

 

[Peo Sjoblom]

What is it that you want to count, text or numbers?

if text but not cells with formulas that return blank ""

=SUMPRODUCT(--(ISTEXT(A1:A10)),--(LEN(A1:A10)>0))

if numbers

=COUNT(A1:A10)

if both numbers and text but not blanks from formulas

=SUMPRODUCT(--(LEN(A1:A10)>0))


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Guest]

Hi,

Try counta(a1,b1,c1)-countblank(a1,b1,c1)

Regards,

Ashish Mathur

 

[Peo Sjoblom]

countblank takes only a range but since A1,B1,C1 can be used as A1:C1 then
maybe

=COUNTA(A1:C1)-COUNTBLANK(A1:C1)

but that won't work if there is a truly blank cell


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Domenic]

Try...

=COUNTIF(A1:C1,"?*")

Hope this helps!

 

[Steve Jackson]

Thanks for all your help. The data I have is a selection of cells and not a
direct array so I had to go for:

=COUNTA(A1,E1,G1)-COUNTBLANK(A1)-COUNTBLANK(E1)-COUNTBLANK(G1)

Its not exactly pretty but it works because all cells A1,E1,G1 start off
blank (with just formulas in them) and as data is inputted in other cells,
cells A1, E1, G1 are automatically populated with specific data via their
formulas.