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

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



"Steve Jackson" wrote in message
o.uk...
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

Hi,

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

Regards,

Ashish Mathur

"Steve Jackson" wrote:

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

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


"Ashish Mathur" wrote in message
...
Hi,

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

Regards,

Ashish Mathur

"Steve Jackson" wrote:

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

Try...

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

Hope this helps!

In article ,
"Steve Jackson" wrote:

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

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.


"Peo Sjoblom" wrote in message
...
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


"Ashish Mathur" wrote in message
...
Hi,

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

Regards,

Ashish Mathur

"Steve Jackson" wrote:

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