Count Non-Blank Cells With a Condition in Excel

To count cells that are not blank in Excel, use COUNTIF with the criteria "<>", which means "not equal to empty". To count non-blank cells only in rows that meet another condition, such as one team or region, use COUNTIFS. This guide is for anyone tracking forms, applicants or orders who needs to know "how many have been filled in?", and it covers the cells that look blank but aren't.

How to count cells that are not blank with COUNTIF#

=COUNTIF(range,"<>") counts every cell in the range that has something in it. Here is a list of job applicants, with some details still missing:

To count the applicants with an email address:

=COUNTIF(C2:C8,"<>")

The result is 5. In COUNTIF, <> means "not equal to". With nothing after it, the test is "not equal to empty", in other words "not blank". The quotes are required. The COUNTIF guide covers the other operators, such as ">=8".

COUNTA gives the same answer here: =COUNTA(C2:C8) also returns 5. The difference is that COUNTA can't take a condition, and the COUNTIF version can grow into COUNTIFS.

How to count non-blank cells with a condition (COUNTIFS)#

To count filled cells only in certain rows, add the "not blank" test as one pair in COUNTIFS. COUNTIFS takes pairs of range and criteria, and counts a row only if every pair matches.

How many Sales applicants have a test score entered?

=COUNTIFS(B2:B8,"Sales",D2:D8,"<>")

The result is 3: Ana (78), Dev (64) and Farid ("absent"). Chloe is in Sales but her score is blank, so she isn't counted.

You can add more pairs. =COUNTIFS(B2:B8,"Sales",C2:C8,"<>",D2:D8,"<>") counts Sales applicants with both an email and a score; the result is 2 (Ana and Dev). The COUNTIF function guide has the full syntax for criteria.

How to count blank cells with a condition#

Use "" (two double quotes with nothing between them) to mean "blank". How many Sales applicants still need a test score?

=COUNTIFS(B2:B8,"Sales",D2:D8,"")

The result is 1 (Chloe). Without a condition, COUNTBLANK does the same: =COUNTBLANK(D2:D8) returns 2. A useful check is that "blank" plus "not blank" equals the total: 1 Sales blank + 3 Sales filled = 4 Sales applicants, which matches =COUNTIF(B2:B8,"Sales").

Our comparison of COUNT vs COUNTA vs COUNTBLANK goes deeper on the three counting functions.

How to count only numbers or only text#

"Not blank" counts everything, including text such as "absent". Sometimes you only want real numbers, or only text.

The asterisk * is a wildcard that matches any text, so "*" counts text cells and skips numbers. The ">=0" criterion only matches numbers, which is a simple way to say "has a numeric score". Use ">-1E+307" instead if negative numbers are possible. The Excel count functions guide summarises all of these.

Why do cells that look empty still get counted?#

A cell can look blank but hold something Excel counts. The usual causes are:

1. A space. Someone typed a space, or the data was imported with one. COUNTA and "<>" both count it. Click the cell and check the formula bar.
2. A formula that returns empty text, such as =IF(B2="","",B2*2). The cell shows nothing, but COUNTA counts it because it holds a formula. COUNTBLANK, on the other hand, counts it as blank.
3. An apostrophe on its own ('), which makes the cell hold empty text.

To count only cells with visible content, check the length of each cell:

=SUMPRODUCT(--(LEN(D2:D8)>0))

LEN gives the number of characters, so a formula returning "" has length 0 and isn't counted. To ignore cells that hold only spaces too, use =SUMPRODUCT(--(TRIM(D2:D8)<>"")). To fix the data itself, select the column, use Find & Replace (Ctrl+H on Windows, ⌃H on Mac) to replace a single space with nothing, but check first that real values don't contain spaces you need.

How to build a "filled in" summary for each team#

In reports, you usually want the count for every team, not just one. Put the team names in a column, say F2:F3 (Sales, Finance), and point COUNTIFS at those cells instead of typing the name:

=COUNTIFS($B$2:$B$8,F2,$D$2:$D$8,"<>")

Lock the ranges with dollar signs, leave F2 relative, and fill down. Sales shows 3 and Finance shows 2. To show progress as a share, divide by the team size: =COUNTIFS($B$2:$B$8,F2,$D$2:$D$8,"<>")/COUNTIF($B$2:$B$8,F2) returns 0.75 for Sales; format it as a percentage to read 75%. This is the kind of "how complete is our data?" check that admin and HR tests like to ask for.

Count non-blank cells with OR conditions, or unique values#

COUNTIFS joins its conditions with AND. For OR, such as "Sales or Finance, with a score", add two COUNTIFS together:

=COUNTIFS(B2:B8,"Sales",D2:D8,"<>")+COUNTIFS(B2:B8,"Finance",D2:D8,"<>")

This returns 5. The same pattern works for sums; see SUMIFS with OR logic. If you need the number of different values rather than filled cells, such as distinct customers, see how to count unique values in Excel.

Practise it#

Try the live exercise on this page to count the Sales applicants who have a score. Then practise COUNTIF in a live spreadsheet, including the <> operator, and work through the COUNT, COUNTA and COUNTBLANK lesson to see exactly which cells each function counts.