Your Situation | Use This | Why |
|---|---|---|
I just need a simple count of filled cells | COUNTA | Fastest — just specify the range, no criteria needed |
I want to count truly non-blank cells only | COUNTIF | Use |
I need to count non-blank with one extra condition | COUNTIFS | Supports multiple criteria in a single formula |
Some cells look blank but have a space inside | COUNTIFS (two criteria) | Add a second condition to catch hidden space characters |
I need OR logic across multiple conditions | SUMPRODUCT | The only method that handles OR logic cleanly |
I don't want to write a formula at all | Status Bar | Select the range — the count appears instantly at the bottom |
I am cleaning a large dataset and want to remove blanks | Power Query | Use Remove Empty Rows in the Transform tab — no formula needed |
Let's say you are a teacher in a school and are working with data where you have student scores. When collecting test scores, some entries might be blank due to students being absent on the day of the test or because of errors in data entry.
Counting the number of cells not blank in the "Score" column helps the teacher identify the students who were present in the school on that day.
There are two primary ways to count the non-blank (non-empty) or the ones with value. These two methods are two pre-defined functions that you can use.
The first function is COUNTIF/COUNTIFS, and the other is COUNTA. Using any of these depends on your choice. In this tutorial, we will look at both the ways to do this.
COUNTIF — Count Non-Blank Cells with the Not-Equal Operator
COUNTIF gives you more control than COUNTA. Instead of counting everything that is not empty, you are telling Excel exactly what condition to look for — in this case, cells that are not equal to blank.
Using the same student scores data, here is the formula:
Below are the steps you need to follow:
Click on the cell where you want the result — in this example, that is cell C2.
Type =COUNTIF( to start the formula.
Select the range B2:B11 as the first argument — this is where your scores are.
Add a comma, then type "<>" as the criteria.
Close the parenthesis and press Enter.
The moment you hit Enter, Excel returns 7. The <> operator means "not equal to." When you pair it with nothing after it, Excel reads it as "not equal to blank" and counts every cell that has a value in it.
You can also write the formula this way, which means exactly the same thing:
- range The group of cells you want to evaluate. In our example, this is B2:B11 — the score column.
- criteria The condition to match. Use "<>" to count any cell that is not equal to blank — meaning any cell that has a value in it.
According to Microsoft's official documentation, COUNTIF supports wildcard characters in the criteria argument — use * to match any sequence of characters and ? to match any single character.
COUNTA — The Quickest Way to Count Non-Blank Cells
COUNTA is the most straightforward function for this job. It counts every cell in a range that is not empty — numbers, text, logical values, errors, everything included.
Let's say you have student scores in column B and some cells are blank because students were absent on the day of the test. Here is the formula you need:
Below are the steps you need to follow:
Click on the cell where you want the result — in this example, that is cell C2.
Type =COUNTA( to start the formula.
Select the range B2:B11 — this is the column where your scores are.
Close the parenthesis and press Enter.
The moment you hit Enter, Excel returns 7 — because three students were absent and their score cells are blank.
- value1 The range you want to count. This is required. In our example, this is B2:B11 — the score column.
- value2 Optional. Add more ranges if you want to count non-blank cells across multiple columns at once. You can add up to 255 ranges.
COUNTIFS — Count Non-Blank Cells with Multiple Conditions
COUNTIFS is the version you want when you have more than one condition to check at the same time.
Let's say you now have a Gender column added to the same data. You want to count how many female students have a score entered — meaning the Gender column says "Female" AND the Score column is not blank.
Here is the formula:
There are 5 female students in total — but Maria has no score entered. So the formula returns 4, because only Priya, Sara, Lena, and Nina pass both conditions.
Below are the steps you need to follow:
Click on the cell where you want the result — in this example, that is cell D2.
Type =COUNTIFS( to start the formula.
Select B2:B11 as the first range — this is the Gender column.
Add a comma, then type "Female" as the first criteria.
Add another comma, then select C2:C11 as the second range — this is the Score column.
Add a comma, then type "<>" as the second criteria.
Close the parenthesis and press Enter.
Here is how the formula works:
- First, it goes through column B and checks each cell for the value "Female".
- Then, for those exact same rows, it checks column C to see if the Score cell is not blank.
- Only rows that pass both conditions get counted.
- range1 The first range to evaluate. In our example, this is B2:B11 — the Gender column.
- criteria1 The condition for range1. Here we used "Female" to only look at female students.
- range2 The second range to evaluate. In our example, this is C2:C11 — the Score column.
- criteria2 The condition for range2. Here we used "<>" to only count rows where the score is not blank.
COUNTIFS — Handle Cells That Only Look Blank
This is a problem I want to point out because it catches a lot of people off guard.
Sometimes a cell looks completely empty, but it actually contains a space character. This happens when someone accidentally pressed the spacebar, or when data was imported from another system.
The problem is that both COUNTA and COUNTIF with <> will count that space-filled cell as non-blank — because technically, it is not empty. So you end up with a wrong count.
Look at this example. We have 7 cells with actual scores, but one cell has a hidden space in it:
Cell B4 looks blank — but the formula bar shows a space inside it. Both COUNTA and COUNTIF count it as non-blank and return 8 instead of the correct 7.
The fix is to use COUNTIFS with a second condition that explicitly excludes cells with a single space. Here is the formula:
Now the formula correctly returns 7 — the space cell in B4 is excluded.
Below are the steps you need to follow:
Click on the cell where you want the result — in this example, that is cell C2.
Type =COUNTIFS( to start the formula.
Select B2:B11 as the first range.
Add a comma, then type "<>" as the first criteria — this excludes truly blank cells.
Add another comma, then select B2:B11 again as the second range.
Add a comma, then type "<> " as the second criteria — note the single space after <>. This excludes cells with only a space in them.
Close the parenthesis and press Enter.
Here is how the two criteria work together:
- First criteria "<>" — excludes cells that are truly empty.
- Second criteria "<> " — excludes cells that only contain a space character.
Only cells that pass both checks get counted. That's it.
- range1 The range to check for blank cells. In our example, this is B2:B11.
- criteria1 Use "<>" to exclude truly blank cells.
- range2 The same range again — B2:B11. You are applying a second condition to the same column.
- criteria2 Use "<> " with a space after <> to exclude cells that only contain a space character.
SUMPRODUCT — Count Non-Blank Cells with OR Logic
SUMPRODUCT is the method to use when COUNTIFS is not enough — specifically when you need OR logic instead of AND logic.
For a simple non-blank count, here is the SUMPRODUCT formula:
Below are the steps you need to follow:
Click on the cell where you want the result — in this example, that is cell C2.
Type =SUMPRODUCT( to start the formula.
Type --( — this is the double negative that converts TRUE and FALSE into 1s and 0s.
Select the range B2:B11 and type <>"" after it — this checks each cell to see if it is not blank.
Close both parentheses and press Enter.
The moment you hit Enter, Excel returns 7 — the same result as COUNTIF and COUNTA.
Now here is where SUMPRODUCT becomes more powerful than the others. If you want to count non-blank scores for Female students using OR logic, you can write it like this:
How This Formula Works
The -- at the start is called a double negative. Here is what happens step by step inside the formula:
- B2:B11<>"" — checks each cell and returns TRUE if not blank, FALSE if blank.
- -- — converts TRUE into 1 and FALSE into 0.
- SUMPRODUCT — adds all the 1s together. That total is your count.
- range The cells you want to evaluate. In our example, this is B2:B11 — the score column.
- <>"" The not-blank condition. Returns TRUE if the cell has a value, FALSE if it is empty.
- -- The double negative. Converts TRUE to 1 and FALSE to 0 so SUMPRODUCT can add them up.
Status Bar — Count Non-Blank Cells Without Any Formula
If you just need a quick count and don't want to write a formula at all, the status bar at the bottom of Excel does this instantly.
All you need to do is select the range — Excel shows you the count straight away.
Select the range of cells you want to check — in this example, B2:B11.
Look at the status bar at the very bottom of your Excel window.
You will see a Count value appear — that is your non-blank cell count.
The moment you select the range, Excel shows Count: 7 in the status bar — no formula needed at all.
Find & Replace — Another Way to Count Without a Formula
You can also use Find & Replace to count non-blank cells. Here is how:
Select your range first — B2:B11.
Press Ctrl + F to open the Find dialog.
In the Find what field, type an asterisk * — this matches any content.
Click Find All.
At the bottom of the results window, Excel tells you exactly how many cells it found — that is your non-blank count.
Excel 365 — Count Non-Blank Cells with FILTER and ROWS
If you are on Excel 365, Excel 2021, or Google Sheets, there is one more way to count non-blank cells using the FILTER function combined with ROWS.
Here is the formula:
Below are the steps you need to follow:
Click on the cell where you want the result — in this example, that is cell C2.
Type =ROWS(FILTER( to start the formula.
Select the range B2:B11 as the first argument inside FILTER — this is the range you want to filter.
Add a comma, then type B2:B11<>"" as the condition — this tells FILTER to keep only non-blank cells.
Close both parentheses and press Enter.
The moment you hit Enter, Excel returns 7 — the count of all non-blank cells in the range.
How This Formula Works
The two functions work together in a simple sequence:
- FILTER(B2:B11, B2:B11<>"") — goes through the range and removes every blank cell, returning only the cells that have a value.
- ROWS() — counts how many rows are left after FILTER removes the blanks. That number is your non-blank count.
- range The cells you want to evaluate. In our example, this is B2:B11 — the score column.
- range<>"" The condition passed to FILTER. This keeps only cells that are not blank and removes everything else.
- ROWS() Counts the number of rows returned by FILTER — giving you the total non-blank count.
Frequently Asked Questions
Wrapping Up
So these are the five ways to count non-blank cells in Excel — COUNTA, COUNTIF, COUNTIFS, SUMPRODUCT, and the no-formula status bar trick. Each one has its place depending on how complex your data is.
Here is a quick way to think about which one to pick:
- Use COUNTA when you just need a fast, simple count.
- Use COUNTIF when you want more control over what gets counted.
- Use COUNTIFS the moment you need a second condition — or when your data has hidden spaces.
- Use SUMPRODUCT when you need OR logic.
- Use the status bar when you just need a quick answer and don't want a formula at all.
My personal go-to is COUNTIF for quick jobs and COUNTIFS the moment I need a second condition. SUMPRODUCT is the one I reach for when things get complicated with OR logic.
Which one worked best for you? Let me know in the comments below — and please don't forget to share this post with your friends. I am sure they will find it useful too.
