countif excel any value

CountIF Excel for Any Value

By Joshua Young
AD

Ever found yourself wanting to know how many cells in an Excel sheet contain any value at all? The humble COUNTIF function might not immediately spring to mind, but with some clever construction, it's a powerful tool for this exact task. Let's dive into the world of "COUNTIF Excel any value".

Before we craft our formula, it's essential to understand that by default, COUNTIF only counts cells with numbers. To count cells with any value, including text, we'll need to use a trick with an error value. But first, let's set the stage with a simple COUNTIF use case.

Using COUNTIF With Numbers

COUNTIF is your friend when it comes to counting numerical values. If you want to know how many cells in a range equal a certain number, use this syntax:

AD
an excel spreadsheet with multiple columns and numbers in the bottom left hand corner
an excel spreadsheet with multiple columns and numbers in the bottom left hand corner
Basic Excel Formulas
Basic Excel Formulas
Count Number of Occurrences of Each Value in a Column in Excel - ExcelDemy
Count Number of Occurrences of Each Value in a Column in Excel - ExcelDemy

=COUNTIF(range, number)

For instance, if you have a range of cells (A1:A10) containing the numbers 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, and you want to know how many of them are equal to 5, you'd enter:

COUNTIF in excel 💯
COUNTIF in excel 💯
COUNTIFS Unique Values in Excel (4 Easy Ways) - ExcelDemy
COUNTIFS Unique Values in Excel (4 Easy Ways) - ExcelDemy
Count Greater than 0 in Excel (COUNTIF - COUNTIFS)
Count Greater than 0 in Excel (COUNTIF - COUNTIFS)

=COUNTIF(A1:A10, 5)

Counting Positive Numbers

What if you want to count only the positive numbers? Here's where the "greater than" (>0) operator comes in handy:

AD

=COUNTIF(A1:A10, ">0")

How to Count Unique Values Using Excel Pivot Table (3 Ways) - ExcelDemy
How to Count Unique Values Using Excel Pivot Table (3 Ways) - ExcelDemy
COUNTIF Between Two Cell Values in Excel (5 Examples) - ExcelDemy
COUNTIF Between Two Cell Values in Excel (5 Examples) - ExcelDemy
Easy Excel Formula to Count Unique Values
Easy Excel Formula to Count Unique Values
Excel Count Unique Values (A Complete Overview) - ExcelDemy
Excel Count Unique Values (A Complete Overview) - ExcelDemy
How to Create a Pie Chart by Count of Values in Excel (2 Methods) - ExcelDemy
How to Create a Pie Chart by Count of Values in Excel (2 Methods) - ExcelDemy
COUNTIF vs COUNTIFS in Excel: 4 Methods - ExcelDemy
COUNTIF vs COUNTIFS in Excel: 4 Methods - ExcelDemy

Counting Negative Numbers

Similarly, to count only the negative numbers, use the "<0" operator:

=COUNTIF(A1:A10, "<0")

AD

Counting Any Value (Numbers or Text)

Now, let's return to our primary goal: counting cells with any value, including text. Here's where that error value trick comes into play.

Count Blank Cells with Excel COUNTIF Function: 2 Examples - ExcelDemy
Count Blank Cells with Excel COUNTIF Function: 2 Examples - ExcelDemy

In Excel, errors like #N/A, #NULL!, or #REF! are technically "values". So, if we can make COUNTIF ignore these values, we'll have our solution. Let's use the infamous error value, #DIV/0!, for this:

Creating the Error Value

To create this error, divide any number by 0, or use the =1/0 formula. Place this in a cell and drag it across your range. Now, every cell that's empty or contains nothing of value will display #DIV/0!. Otherwise, it will display the actual contents of the cell.

AD

Counting with COUNTIF

Now, use COUNTIF to ignore these #DIV/0! errors. Here's the formula to achieve this:

=COUNTIF(1/(A1:A10, ""), "<>#DIV/0!")

This formula says, "Count cells in the range A1:A10 where the value is not equal to #DIV/0!" In other words, it counts any cells with any non-empty value.

Using Wildcards With COUNTIF

COUNTIF also supports wildcards for more advanced searching. Here's how you can use them:

* Wildcard: Any Text

The asterisk (*) wildcard can represent any text. For example, to count cells containing the word "exam" anywhere in the text, use:

=COUNTIF(A1:A10, "*exam*")

? Wildcard: Any Single Character

The question mark (?) wildcard can represent any single character. For example, to count cells where the first letter is 'A', use:

=COUNTIF(A1:A10, "A?")

With these techniques, you're well-equipped to tackle various COUNTIF Excel challenges, including counting any value at all. Happy Exceling!

AD