Celebrate 'The VLOOKUP Book' birthday with us. Last day to get 50% discount on the e-book (31 October only).

Click here for details

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

Countif1

 

Count All Text

Countif2a

 

Count All Blanks

Countif3a

 

Download

You can download a sample of the above Countif_Quicktips

 

Learn More about the Countif() function

http://chandoo.org/excel-formulas/countif.shtml

Sumifs()  a close relation to Countifs()

Your email address is safe with us. Our policies

Written by Hui...
Tags: , , , ,
Home: Chandoo.org 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:

      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

  6. Pavi says:

    Hi Hui,

    How to count number in the list.

    Regards,
    Pavi

    • Nate says:

      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.

    • pmsocho says:

      You can also use a formula like this:
      =COUNT(B2:B9)
      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.
    =COUNT(B2:B9)

  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,
    SomeintPhia

  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:
    =COUNTIF(B2:B9,”>”””)

    doesn’t count numbers though.

Leave a Reply