Countif – Quick Tip

Posted on December 16th, 2013 in Huis , Posts by Hui , Quick Tip - 19 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()

Do you want to be awesome in Excel?

Free Excel tips book - joining bonus - newsletter

Here is a smart way to become awesome in Excel. Just signup for my Excel newsletter. Every week you will receive an Excel tip, tutorial, template or example delivered to your inbox. What more, as a joining bonus, I am giving away a 25 page eBook containing 95 Excel tips & tricks. Please sign-up below:

Your email address is safe with us. Our policies

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

19 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.

Leave a Reply