This listicle covers ten practical ways to use Excel’s COUNTIF function to count cells that contain any value at all, plus variations for specific text, numbers, blanks, and more. It’s for spreadsheet users who want to move beyond basic counting and handle real-world data that’s messy, mixed, or partially empty.
1. Count Non-Empty Cells with “*”
The classic method is =COUNTIF(range,"*"). The asterisk wildcard matches any sequence of characters, so this counts every cell in the range that has at least one character in it. Note that it ignores truly blank cells, but it also ignores cells containing only an empty string from a formula (like =""), which can be a limitation.
2. Count Cells with Any Number
To count only cells that contain a numeric value, use =COUNTIF(range,">0") or =COUNTIF(range,"<>0") depending on whether you want to include zero. But if you want any number including zero, use =COUNTIF(range,">=0"). This works because COUNTIF treats numeric criteria as comparisons, so it will count all numbers that meet the condition, and ignore text and blanks.
3. Count Cells with Any Text (Including Empty Strings)
If you need to count cells that contain text, including those with formulas that return an empty string, use =COUNTIF(range,"?*"). This matches any cell with at least one character, but unlike the simple asterisk, it also counts cells with a zero-length string because the question mark matches any single character, and the asterisk matches zero or more after it. This is a subtle but important difference for data imported from other systems.
4. Count Cells That Are Not Blank (Using “<>“)
For a count of cells that are not blank, including those with formulas returning empty strings, use =COUNTIF(range,"<>"). The “not equal to” operator with no value after it counts all cells that are not truly empty. This is often what people mean when they say “anything in a cell,” and it’s more inclusive than the asterisk method.
5. Count Cells That Are Blank (Using “=”)
The inverse is =COUNTIF(range,"="), which counts blank cells. This is useful for quality checks, like finding missing entries in a column. It counts truly empty cells but not cells with an empty string from a formula, so be aware of that distinction when auditing your data.
6. Count Cells That Contain Specific Text Anywhere
To count cells that contain a particular word or character anywhere, wrap it in asterisks: =COUNTIF(range,"*text*"). For example, =COUNTIF(A:A,"*error*") counts every cell that has “error” anywhere in it. This is case-insensitive, so it will match “Error”, “ERROR”, and “error”.
7. Count Cells That Start With or End With Specific Text
Use =COUNTIF(range,"text*") to count cells that start with “text”, or =COUNTIF(range,"*text") for cells that end with “text”. This is handy for filtering by prefixes like “ID-” or suffixes like “.pdf”. Remember that these are wildcard matches, so they work on text and numbers stored as text.
8. Count Cells That Are Not Equal to a Specific Value
To count cells that contain anything other than a certain value, use =COUNTIF(range,"<>value"). For instance, =COUNTIF(B:B,"<>pending") counts all cells that are not “pending”, including blanks. If you want to exclude blanks as well, combine it with the non-blank method: =COUNTIF(range,"<>value")-COUNTIF(range,"=").
9. Count Cells That Contain Numbers Greater Than or Less Than a Threshold
Beyond just any number, you can count numbers above or below a cutoff: =COUNTIF(range,">10") or =COUNTIF(range,"<0"). This is useful for sales figures, test scores, or inventory levels. The criteria is a text string, so you can also reference a cell: =COUNTIF(range,">"&C1).
10. Count Cells with Errors (Using SUMPRODUCT)
COUNTIF cannot directly count cells with errors like #N/A or #VALUE!. Instead, use =SUMPRODUCT(--ISERROR(range)). This counts every cell that contains any error type. It's a workaround, but it's the standard way to get a count of error cells in a range.
How to Choose
The right method depends on what you mean by "anything." If you want to count cells that are not blank, including those with formulas returning empty strings, use "<>". If you want to count cells with visible characters only, use "*". If you need to count numbers only, use a comparison like ">=0". For text with specific patterns, use wildcards. For errors, you must use SUMPRODUCT. Always test your formula on a small sample to confirm it matches your expectation, especially when your data contains formulas that return empty strings or imported values with leading spaces.
Frequently Asked Questions
Q: Why does COUNTIF(range,"*") not count cells with formulas that return ""?
A: Because the asterisk wildcard matches cells that contain at least one actual character. A formula that returns an empty string produces a zero-length text, which is not counted by the asterisk. Use "<>" to include those.
Q: Can I count cells that contain any number, including zero?
A: Yes, use =COUNTIF(range,">=0"). This counts all numeric values that are zero or greater, and ignores text and blanks.
Q: How do I count cells that are not blank and not equal to a specific value?
A: Use =COUNTIF(range,"<>value") - COUNTIF(range,"="). The first part counts all cells not equal to the value, including blanks; the second subtracts the blank count.
Q: Does COUNTIF work with wildcards for numbers?
A: Wildcards like * and ? work only with text. For numbers, you must use comparison operators like <, >, =, and <>. For example, =COUNTIF(range,">5").
Q: Is COUNTIF case-sensitive?
A: No, COUNTIF is not case-sensitive. It treats "APPLE", "Apple", and "apple" as the same text. If you need case-sensitive counting, you'd need a different approach, like SUMPRODUCT with EXACT.
Q: What if I want to count cells that contain any of several specific values?
A: You can add multiple COUNTIF results: =COUNTIF(range,"apple") + COUNTIF(range,"banana"). Or use SUMPRODUCT with an array constant for a more compact formula.
Final Thoughts
Mastering these COUNTIF variations will save you time and reduce errors when working with messy data. Start with the basic non-blank count and then adapt as your needs grow. The key is understanding the difference between blank, empty string, and non-visible characters, and knowing that wildcards only work on text. With these ten techniques, you'll be equipped to handle most counting scenarios in Excel.