View original (French)

NB.IF and ISNUMBER

xuorel Posted messages 253 Registration date   Status Member Last intervention   -  
eriiic Posted messages 24581 Registration date   Status Contributor 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 answers

  1.
    eriiic Posted messages 24581 Registration date   Status Contributor 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
    1. Anonymous user
       
      A note! I did not know about this use of sumprod().
      0
      1. ALS35 Posted messages 1034 Registration date   Status Member Last intervention   147 > Anonymous user
         
        Hello everyone,

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

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

      What does "--" mean in: 

      =SUMPRODUCT(--ISNUMBER(A2:E6))
      0
      1. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281 > xuorel Posted messages 253 Registration date   Status Member 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
  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