View original (French)

NB.IF and ISNUMBER
xuorel -  
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 Contributor
    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
    2. ALS35
      @Anonymous userHello everyone,

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

      Best regards
      0
    3. Anonymous user
      @ALS35Indeed! Why make it more complicated!
      0
    4. xuorel Author
      Thank you all for your feedback.

      What does "--" mean in: 

      =SUMPRODUCT(--ISNUMBER(A2:E6))
      0
    5. eriiic Contributor
      @xuorelYes, 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