COUNTIF Google Sheets: Count Blank Cells and Empty Strings

In Google Sheets, use =COUNTIF(A2:A100,"") to count blank cells in A2:A100. The formula counts empty cells and cells whose formulas return an empty string; use COUNTBLANK for a simpler alternative, or ISBLANK when you need to identify cells that are truly empty.

What COUNTIF Counts as Blank

COUNTIF checks a range against one condition. Its usual structure is =COUNTIF(range, criterion): the first argument is the cells to check, and the second is the condition. An empty string, written as "", is the condition used to count blanks.

For example, with entries in A2:A20 and some cells left empty, =COUNTIF(A2:A20,"") returns the number of cells Sheets treats as blank. The formula can count a cell that contains a formula such as one returning "", even though that cell is not physically empty. That distinction matters when a sheet uses formulas to hide results until data is entered.

The range determines what gets counted. A formula that checks A2:A20 will not count blank cells elsewhere in the column. Using a bounded range is usually clearer than checking an entire column, especially if the sheet has a header or many unused rows.

Enter the Formula in Google Sheets

To count blanks in a particular range:

Select the cell where you want the result.
Type =COUNTIF(A2:A100,""), replacing A2:A100 with your actual range.
Press Enter. The result updates when cells in the range change.

In that example, the assumed layout is the data to check in A2:A100 and the result in a cell outside that range. If the result cell is inside the checked range, it can become part of the count, or create a circular reference if the formula is included in its own range. Put the total in a separate cell or adjust the range to exclude it.

If your data is in a different column, change both range references. With names in B2:B50 and a count in B52, for instance, the formula is =COUNTIF(B2:B50,""). Google Sheets may use semicolons rather than commas in formulas depending on spreadsheet locale settings. If Sheets rejects a comma-separated formula, check the spreadsheet’s locale under File > Settings.

COUNTIF or COUNTBLANK?

For a straightforward blank count, COUNTBLANK is often easier to read. It takes one range argument and is designed specifically to count blank cells. With data in A2:A100 and the result outside that range, use =COUNTBLANK(A2:A100). It counts empty cells and cells containing formulas that return an empty string.

For the same range, =COUNTIF(A2:A100,"") gives a condition-based version of the count. The two are convenient alternatives for ordinary blank counting, but COUNTIF becomes more useful when you need a condition alongside another criterion, or when you are extending a formula that already uses COUNTIF. Neither function tells you whether a cell was never filled in or was cleared by a formula.

Choose based on what the formula needs to communicate:

Use COUNTBLANK when you simply want the blank count.
Use COUNTIF with "" when you want a COUNTIF condition or plan to adapt the formula to other criteria.
Use ISBLANK-based logic when the difference between a physically empty cell and a formula-generated empty string is important.

Truly Empty Cells, Formula Blanks, and Spaces

“Looks blank” can mean several different things in a spreadsheet. A cell may actually be empty, contain a formula that displays nothing, or contain a space character. These cases can look identical on screen but are not the same underlying content.

To count only cells that are genuinely empty, use ISBLANK inside SUMPRODUCT. With the cells to inspect in A2:A100 and the result outside that range, enter =SUMPRODUCT(--ISBLANK(A2:A100)). ISBLANK returns TRUE for an empty cell and FALSE for a cell containing a formula, including a formula whose displayed result is "". SUMPRODUCT turns those TRUE/FALSE results into a count.

A cell containing a space is not empty, so a blank-count formula will not treat it as a genuinely blank cell. To count cells that are empty or contain only ordinary spaces, with data in A2:A100, use =SUMPRODUCT(--(LEN(TRIM(A2:A100))=0)). TRIM removes ordinary leading and trailing spaces and LEN checks whether anything remains. This test also counts formula results that are empty. It may not remove every invisible character, such as a nonbreaking space copied from a web page; those cells may need cleaning separately.

These distinctions help explain why a count can differ from a visual scan. If a cell appears empty but is not counted, click it and inspect the formula bar. A space, an apostrophe, or a formula may be present even when the cell looks blank.

Practical Examples and Conditional Counts

Suppose A2:A9 contains four names, one formula returning an empty string, and three truly empty cells. With the count formula outside that range, =COUNTIF(A2:A9,"") returns 4: the three empty cells plus the formula result that displays as empty. By contrast, =SUMPRODUCT(--ISBLANK(A2:A9)) returns 3 because it excludes the formula cell. This is a useful way to diagnose a mismatch between a visual count and a formula result.

You can also count blank cells only when another column meets a condition. Suppose A2:A100 contains task owners and B2:B100 contains task statuses. To count rows where the owner is blank and the status is Open, enter =COUNTIFS(A2:A100,"",B2:B100,"Open") in a cell outside those data ranges. COUNTIFS applies both conditions to corresponding rows, so the ranges should have matching dimensions.

Another practical use is to check a required field before sharing a data-entry sheet. If names are expected in A2:A50, the blank count tells you how many entries still need attention. A positive result does not necessarily mean something is wrong: some rows may be intentionally unused. The formula counts all cells in the selected range, so make the range match the rows that are meant to contain data.

Common Problems and How to Fix Them

The result is larger than expected: Check whether the range includes unused rows, a header, or cells with formulas returning empty strings. Narrow the range to the intended records.
A cell looks blank but is not counted: Look for spaces or other invisible characters. TRIM can help with ordinary spaces; copied nonbreaking spaces may need a separate cleanup.
The formula displays an error: Confirm the range is valid and that your locale uses the separator you entered. Some locales require semicolons instead of commas.
The count changes after adding a formula: A formula that returns "" may be included in a blank count. Use ISBLANK-based counting if only physically empty cells should qualify.
The result seems to count itself: Move the result outside the range being checked, or exclude its cell from that range.

Also confirm that the range starts and ends where you expect. For example, A2:A100 checks 99 cells, not 100, because row 1 is excluded. A full-column reference such as A:A can include a header and a very large number of unused cells, so it may produce a technically correct but unhelpful result.

Using Blank Counts in a Working Sheet

A blank count can be more than a one-time check. It can act as a simple completeness indicator beside an input table. For example, place a label such as “Missing entries” next to the count, then review the source rows when the result is greater than zero. If some rows are optional, narrow the counted range or use a conditional count that reflects which records are actually required.

For collaborative sheets, keep the counting range understandable and avoid placing totals among the input cells. This makes the formula easier for another editor to inspect and reduces the chance that new rows fall outside the range. If the dataset grows frequently, consider using a range that leaves room for expected entries, while avoiding an unnecessarily large full-column count.

Blank counts are not a substitute for validating the data itself. A cell can contain the wrong value and still be nonblank; conversely, a blank can be deliberate. Use the count as a signal to review records, not as proof that every row is correct.

Frequently Asked Questions

Does COUNTIF count cells with formulas that return an empty string?

Yes. A blank criterion such as "" can count cells whose formulas display an empty string, as well as genuinely empty cells. Use ISBLANK-based counting if you want to exclude formula cells.

What is the simplest formula to count blank cells?

With the range to check in A2:A100 and the result outside it, use =COUNTBLANK(A2:A100). It is a direct alternative to COUNTIF for a basic blank count.

How do I count only cells that are completely empty?

With the cells to inspect in A2:A100, use =SUMPRODUCT(--ISBLANK(A2:A100)). It counts truly empty cells but does not count a formula that displays nothing.

Why does COUNTIF miss a cell that looks blank?

The cell may contain a space or another invisible character rather than being empty. Select it and inspect the formula bar; for ordinary spaces, a TRIM-based check can help identify cells that contain no other text.

Can COUNTIF count blanks only for rows that meet another condition?

Yes. Use COUNTIFS with one condition for the blank field and another for the related value. For example, with owners in A2:A100 and statuses in B2:B100, =COUNTIFS(A2:A100,"",B2:B100,"Open") counts rows with a blank owner and an Open status.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.