20 responses

  1. Sumit Bansal
    December 16, 2013

    Interesting use of “><". Didn't know this!!

    I use "?*" to count non-blank cells with text

    Reply

    • Hui…
      December 16, 2013

      @Sumit
      Yes, This occurred to me today by a slip of the finger and Wow!

      Reply

      • Sumit Bansal
        December 16, 2013

        @Hui
        I searched for it on web and in Office Help. Didn’t find it there as well. That makes this trick more Wow 🙂

        Reply

  2. Rahim
    December 16, 2013

    Awesome… New Learning 🙂

    Reply

  3. Deepak Kamath
    December 16, 2013

    countifs() is also a very interesting formula. Which helps in having multiple ranges

    Reply

  4. Xiq
    December 16, 2013

    Nice summary! Thanks!

    Reply

  5. Jason M
    December 16, 2013

    Be very careful with =COUNTIF(B2:B9,”><"). If you insert the following text into B2:B9, the formula returns 0 which is incorrect. Better to use =COUNTIF(B2:B9,"*").

    "
    #
    $
    %
    &
    '
    (
    )

    Reply

    • Sumit Bansal
      December 16, 2013

      Jason,

      Good catch. One more thing that I tried.. If you insert =”” in one of the cells, “*” still counts it as non-blank (this is sometimes the case with imported data)

      I prefer “?*” as it ensures the cell has atleast one character

      Reply

  6. Pavi
    December 16, 2013

    Hi Hui,

    How to count number in the list.

    Regards,
    Pavi

    Reply

    • Nate
      December 16, 2013

      Pavi,

      I’ve always used something like: =COUNTIF(B2:B9,”>”&0)

      But generally, I want to find that meet certain criteria. If you just wanted all numbers, regardless of value, you could use:
      =SUMPRODUCT(–ISNUMBER(A2:A9))

      If you wanted to return only values that met conditions using SUMPRODUCT, you’d use something like:
      =SUMPRODUCT(–(A2:A9>110000),–ISNUMBER(A2:A9))

      Without the ISNUMBER() clause, SUMPRODUCT will also count your text cells.

      Reply

    • pmsocho
      December 16, 2013

      You can also use a formula like this:
      =COUNT(B2:B9)
      It counts only numbers 🙂

      Reply

  7. Colin
    December 16, 2013

    The “><" isn't quite doing what the post implies.

    It is counting cells where the character code is greater than that of the < symbol, with the character code for the < symbol being 60.

    So, if you just put an exclamation mark (!) in a cell, the formula wouldn't count it because the character code for ! is 33. This is what Jason highlighted a couple of posts ago.

    @Pavi, just use the COUNT() function.
    =COUNT(B2:B9)

    Reply

  8. pmsocho
    December 16, 2013

    Also be careful with this: COUNTIF(B2:B9,”=”) because it treats cells with formula =”” as non-empty cells.
    COUNTIF(B2:B9,””) is better because it counts empty cells and also cells with empty text string (“”).

    Reply

  9. Manish
    December 17, 2013

    We are trying to use COUNTIF function to find blank and non-blank cells, which is creative and fine.

    However, we can also use COUNTA() to count non-blank cells and COUNTBLANK() to count blank cells. These are very specific functions to do the same work.

    Reply

  10. Eddy
    December 17, 2013

    What are the best CountIf options for text, number and blank using the data in B2:B9 if the data in each cell, including the blank, was generated with a formula?

    Reply

    • Sumit Bansal
      December 18, 2013

      Try this:

      =CountA(B2:B9) + Countif(B2:B9,”?*”)

      This will count if the cell has anything (number, text, or characters) except blank (even if generated by formula)

      Reply

  11. SomeintPhia
    December 27, 2013

    a nice learning, thank you very much.

    at “Count all text” I tested a space, with
    a nice result .. 😉

    Kind regards,
    SomeintPhia

    Reply

  12. Rajesh
    December 30, 2013

    Also we can try out this thing as well

    1. =COUNTIF(C28:C31,”Band”)..will return 2 by exact match between the cell range
    2. =COUNTIF(C28:C31,”*and”)..will return 2 by exact match between the cell range containing “Band”,”Rand”
    3. =COUNTIF(C28:C31,”*and”)..will return 1 by exact match between the cell range containing “Band”,”Rnd”

    Good luck!!

    Reply

  13. benishiryo
    February 13, 2014

    to exclude the “” for the count but count other texts, you could use:
    =COUNTIF(B2:B9,”>”””)

    doesn’t count numbers though.

    Reply

  14. Shaiq
    May 18, 2017

    Very useful and interesting. Thanks.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top
mobile desktop