count problems

  • Thread starter Thread starter cathal
  • Start date Start date
C

cathal

all, I have a range of cells of different values [numbers], some cell
are 'blank' [containing the ISNA function, meaning I can't see it bu
there is stuff in the cell].

My question: I want to count the cells that do not contain numbers
thus if a cell contains something hidden via the ISNA command it wil
not be counted.

Suggestions etc. much appreciated. My guess is it's something simpl
that I've overlooked.

regards, cathal.
 
Hi Cathal,

This might work for you

=COUNTA(A1:A10)-COUNT(A1:A10)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi
try
=SUM(IF(AND(ISNUMBER(A1:A100),A1:A100=""),0,1))
entered as array formula (CTRL+SHIFT+ENTER). This will count all cells
that contain text values (numbers, blanks and formula results as =""
not counted)

Frank
 
This might work for you

=COUNTA(A1:A10)-COUNT(A1:A10)
Click to expand...
...

That'd count the #N/A cells as well as the text cells, since the #N/A cells
would be included in COUNTA but not COUNT. The following counts only cells
containing text.

=COUNTIF(A1:A10,"*")

Note: this includes cells evaluating to "". To exclude those as well, use

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

...
 
=SUM(IF(AND(ISNUMBER(A1:A100),A1:A100=""),0,1))
entered as array formula (CTRL+SHIFT+ENTER). This will count all cells
that contain text values (numbers, blanks and formula results as =""
not counted)
Click to expand...
...

Clearly you didn't test this.

First off, there's the classic error of assuming that AND returns an array when
given array arguments. Nope!

Even if this were rewritten as

=SUM(IF(ISNUMBER(A1:A100)*(A1:A100=""),0,1))

it'd include *ALL* cells in the range. Why so? Because ISNUMBER returns TRUE
only for cells containing numbers, not blank cells or cells evaluating to text,
booleans or errors. However, all numbers <> "", so for numbers, the second arg
to AND is always FALSE, and for nonnumbers the first arg to AND is always FALSE.
Net result: the AND expression above *ALWAYS* returns FALSE.

Did you mean OR rather than AND? Even so, your general approach chokes on error
values like #N/A, so it still wouldn't work.
 
Hi Harlan
thanks for pointing this out. You're right, didn't test this solution -
one of the rare times and it hits me!
Regards
Frank
 
Back
Top