Here’s a few neat ways to count Text, Blanks and Non-Blank cells using the Countif() function.
You can download a sample of the above Countif_Quicktips
Share this tip with your colleagues
Simple, fun and useful emails, once per week.
Learn & be awesome.
Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME. Read my story • FREE Excel tips book
From simple to complex, there is a formula for every occasion. Check out the list now.
Calendars, invoices, trackers and much more. All free, fun and fantastic.
Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.
Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.
This week, let’s try something new. I have a spreadsheet challenge for you. Solve it, post your answers by leaving a comment. No cheating or
20 Responses
Interesting use of “><". Didn't know this!!
I use "?*" to count non-blank cells with text
@Sumit
Yes, This occurred to me today by a slip of the finger and Wow!
@Hui
I searched for it on web and in Office Help. Didn’t find it there as well. That makes this trick more Wow 🙂
Awesome… New Learning 🙂
countifs() is also a very interesting formula. Which helps in having multiple ranges
Nice summary! Thanks!
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,"*").
"
#
$
%
&
'
(
)
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
Hi Hui,
How to count number in the list.
Regards,
Pavi
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.
You can also use a formula like this:
=COUNT(B2:B9)
It counts only numbers 🙂
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)
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 (“”).
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.
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?
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)
a nice learning, thank you very much.
at “Count all text” I tested a space, with
a nice result .. 😉
Kind regards,
SomeintPhia
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!!
to exclude the “” for the count but count other texts, you could use:
=COUNTIF(B2:B9,”>”””)
doesn’t count numbers though.
Very useful and interesting. Thanks.