Countif – Quick Tip

Posted on December 16th, 2013 in Huis , Posts by Hui , Quick Tip - 20 comments

Here’s a few neat ways to count Text, Blanks and Non-Blank cells using the Countif() function.

Count All Non-Blanks



Count All Text



Count All Blanks




You can download a sample of the above Countif_Quicktips


Learn More about the Countif() function

Sumifs()  a close relation to Countifs()

Written by Hui...
Tags: , , , ,
Home: Main Page
? Doubt: Ask an Excel Question

20 Responses to “Countif – Quick Tip”

  1. Sumit Bansal says:

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

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

  2. Rahim says:

    Awesome... New Learning 🙂

  3. Deepak Kamath says:

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

  4. Xiq says:

    Nice summary! Thanks!

  5. Jason M says:

    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,"*").


    • Sumit Bansal says:


      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

  6. Pavi says:

    Hi Hui,

    How to count number in the list.


    • Nate says:


      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:

      If you wanted to return only values that met conditions using SUMPRODUCT, you'd use something like:

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

    • pmsocho says:

      You can also use a formula like this:
      It counts only numbers 🙂

  7. Colin says:

    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.

  8. pmsocho says:

    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 ("").

  9. Manish says:

    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.

  10. Eddy says:

    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?

  11. SomeintPhia says:

    a nice learning, thank you very much.

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

    Kind regards,

  12. Rajesh says:

    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!!

  13. benishiryo says:

    to exclude the "" for the count but count other texts, you could use:

    doesn't count numbers though.

  14. Shaiq says:

    Very useful and interesting. Thanks.

Leave a Reply