{=COUNT(--ISNUMBER(G95:G98))}

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

G95 = 1
G96 = e
G97 = 2
G98 = 3

The count of numbers should be 3. However, I get 4. G96 is formatted as
General. =ISNUMBER(G96) resolves to FALSE.

So???
 
OK, I figured this out. COUNT counts ALL the 1s and 0s that the -- returns.
Not just the 1s. Duh.

Apologies.

Dave
 
You've got 3 cells with TRUE (which you've converted to 1), and one cell
with FALSE (which you've converted to zero).
Zero and one are both numbers so are counted by the COUNT() function.

If you did =COUNT(G95:G98) you presumably get 3?
 
Yes, that's right, 3 ones and one zero add up to 3.
Your other formula counted all 4, as one and zero are both numbers, so valid
for the COUNT() function.
 
Hi
RE: {=COUNT(--ISNUMBER(G95:G98))}, an array formula, counts the range
whether or not the data is a number, just like COUNTA

Peter
 
Yes, thanks. That should have been obvious to me.

Been a long day!
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


David Biddulph said:
You've got 3 cells with TRUE (which you've converted to 1), and one cell
with FALSE (which you've converted to zero).
Zero and one are both numbers so are counted by the COUNT() function.

If you did =COUNT(G95:G98) you presumably get 3?
Click to expand...
 
No, the issue is that the -- coerces the ISNUMBER function to return 1s for
TRUE and 0s for FALSE and so COUNT counts ALL the 1s and 0s. That COUNT may
or may not function like COUNTA in this instance is not the issue. COUNT is
only counting numbers here.

Dave
 
I would probably always use Sumproduct, but this array formula will work:

=COUNT(1/ISNUMBER(A1:A5))

Biff
 
Back
Top