software

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 quot;quot;

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

if numbers

=COUNT(A1:A10)

if both numbers and text but not blanks from formulas

=SUMPRODUCT(--(LEN(A1:A10)gt;0))--

Regards,

Peo Sjoblom

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

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

Regards,

Ashish Mathur

quot;Steve Jacksonquot; wrote:

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

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

nwexcelsolutions.comquot;Ashish Mathurquot; gt; wrote in message
...
gt; Hi,
gt;
gt; Try counta(a1,b1,c1)-countblank(a1,b1,c1)
gt;
gt; Regards,
gt;
gt; Ashish Mathur
gt;
gt; quot;Steve Jacksonquot; wrote:
gt;
gt;gt; I have tried setting a COUNTA formula on an array. The cells in the array
gt;gt; all have a similar formula in each one and will contain text if a
gt;gt; specific
gt;gt; condition is met in another set of cells. The problem I am getting is
gt;gt; that
gt;gt; when I put in the formula =COUNTA(A1,B1,C1), I get the result of all of
gt;gt; them
gt;gt; not being empty. Is this because all those cells contain formulas, even
gt;gt; if
gt;gt; text is there or not. If so, is there a similar function to COUNTA that
gt;gt; will
gt;gt; count cells and not take into account that a formula is already in the
gt;gt; cell?
gt;gt;
gt;gt; Any help will be appreciated.
gt;gt;
gt;gt; Steve
gt;gt;
gt;gt;
gt;gt;
Try...

=COUNTIF(A1:C1,quot;?*quot;)

Hope this helps!

In article gt;,
quot;Steve Jacksonquot; gt; wrote:

gt; I have tried setting a COUNTA formula on an array. The cells in the array
gt; all have a similar formula in each one and will contain text if a specific
gt; condition is met in another set of cells. The problem I am getting is that
gt; when I put in the formula =COUNTA(A1,B1,C1), I get the result of all of them
gt; not being empty. Is this because all those cells contain formulas, even if
gt; text is there or not. If so, is there a similar function to COUNTA that will
gt; count cells and not take into account that a formula is already in the cell?
gt;
gt; Any help will be appreciated.
gt;
gt; 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.quot;Peo Sjoblomquot; gt; wrote in message
...
gt; countblank takes only a range but since A1,B1,C1 can be used as A1:C1 then
gt; maybe
gt;
gt; =COUNTA(A1:C1)-COUNTBLANK(A1:C1)
gt;
gt; but that won't work if there is a truly blank cell
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; nwexcelsolutions.com
gt;
gt;
gt; quot;Ashish Mathurquot; gt; wrote in message
gt; ...
gt;gt; Hi,
gt;gt;
gt;gt; Try counta(a1,b1,c1)-countblank(a1,b1,c1)
gt;gt;
gt;gt; Regards,
gt;gt;
gt;gt; Ashish Mathur
gt;gt;
gt;gt; quot;Steve Jacksonquot; wrote:
gt;gt;
gt;gt;gt; I have tried setting a COUNTA formula on an array. The cells in the
gt;gt;gt; array
gt;gt;gt; all have a similar formula in each one and will contain text if a
gt;gt;gt; specific
gt;gt;gt; condition is met in another set of cells. The problem I am getting is
gt;gt;gt; that
gt;gt;gt; when I put in the formula =COUNTA(A1,B1,C1), I get the result of all of
gt;gt;gt; them
gt;gt;gt; not being empty. Is this because all those cells contain formulas, even
gt;gt;gt; if
gt;gt;gt; text is there or not. If so, is there a similar function to COUNTA that
gt;gt;gt; will
gt;gt;gt; count cells and not take into account that a formula is already in the
gt;gt;gt; cell?
gt;gt;gt;
gt;gt;gt; Any help will be appreciated.
gt;gt;gt;
gt;gt;gt; Steve
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;
gt;