View original (French)

NB.IF and ISNUMBER

xuorel Posted messages 251 Registration date   Status Membre Last intervention   -  
eriiic Posted messages 24581 Registration date   Status Contributeur Last intervention   -
Hello,

I have a 5x5 table
Each cell contains text or numbers.

I want to define the number of cells in the table that contain a numeric value.

How should I proceed?

I was thinking about nesting ISNUMBER within COUNTIF, but it doesn't work.

Thank you for your help.

Xuo

2 réponses

Réponse 1 / 2
eriiic Posted messages 24581 Registration date   Status Contributeur Last intervention   7 281
 
Hello everyone,

or: 
=SUMPRODUCT(--ISNUMBER(A2:E6))

eric

--
By constantly trying, we eventually succeed.
So the more it fails, the more chances we have that it works. (the Shadoks)
In addition to the thank you (yes, it does happen!!!), please remember to set it to resolved. Thank you.
1
Anonymous user
 
A note! I did not know about this use of sumprod().
0
ALS35 Posted messages 1034 Registration date   Status Membre Last intervention   147 > Anonymous user
 
Hello everyone,

And why not simply for the beach A1:E5 
=COUNT(A1:E5)

Best regards
0
Anonymous user > ALS35 Posted messages 1034 Registration date   Status Membre Last intervention  
 
Indeed! Why make it more complicated!
0
xuorel Posted messages 251 Registration date   Status Membre Last intervention  
 
Thank you all for your feedback.

What does "--" mean in: 

=SUMPRODUCT(--ISNUMBER(A2:E6))
0
eriiic Posted messages 24581 Registration date   Status Contributeur Last intervention   7 281 > xuorel Posted messages 251 Registration date   Status Membre Last intervention  
 
Yes, hello too,
The 1st - is used to convert the boolean TRUE/FALSE into numeric.
The 2nd to restore the sign: -1 => 1
You could just as well do +0 or *1
eric
0
Réponse 2 / 2
Anonymous user
 
Hello,
NB.SI works well! Using 2 formulas to account for positive and negative numbers: =NB.SI(RANGE;">=0") + NB.SI(RANGE;"<0") where RANGE should be replaced...
0