# 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 Blanks

You can download a sample of the above Countif_Quicktips

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

Sumifs()  a close relation to Countifs()

 What is the coolest Excel trick you have learned this year? Sporadic Totals in Excel [video]
 Written by Hui... Tags: countif(), Learn Excel, Microsoft Excel Formulas, quick tip, wildcards in excel Home: Chandoo.org 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

• Hui... says:

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

• Sumit Bansal says:

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

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?

• Sumit Bansal says:

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)

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.

14. Shaiq says:

Very useful and interesting. Thanks.

 What is the coolest Excel trick you have learned this year? Sporadic Totals in Excel [video]