Google Sheets COUNTIF for Empty Cells

To count empty cells in Google Sheets, use =COUNTIF(A2:A100,""), replacing A2:A100 with your range. The empty-string criterion counts cells Sheets treats as blank, including cells whose formulas return an empty string; spaces and other entered values are not blank.

What COUNTIF Does

COUNTIF checks a range against one condition and returns how many cells meet it. Its basic structure is =COUNTIF(range, criterion): the range is the group of cells to inspect, and the criterion tells Sheets what to count. For empty cells, the criterion is two quotation marks with nothing between them: "".

For example, if a task list has due dates in B2:B50, =COUNTIF(B2:B50,"") reports how many tasks have no due date entered. It returns a number, not a list of the blank cells, and it does not change the sheet. You can put the formula in any cell outside the range being counted, such as B1, as long as it does not create a circular reference.

The range can be a single column, a row, or a rectangular block. A bounded range such as B2:B100 is often easier to understand and may be more efficient than counting an entire column. If new records will be added below the current data, choose a range large enough to include them or adjust the range as the sheet grows.

Enter the Formula and Read the Result

In a Google Sheets web browser, select the cell where you want the count, type the formula, and press Enter. For instance, with customer responses in C2:C25, =COUNTIF(C2:C25,"") counts the unanswered response cells. If five cells in that range are blank, the result is 5.

Identify the cells you want to check and note their range, such as D2:D200.
Type =COUNTIF(D2:D200,"") in a separate result cell.
Press Enter and check that the range starts and ends where intended.

Google Sheets normally accepts commas between function arguments, as shown. Depending on the spreadsheet’s locale, formulas may use semicolons instead. If Sheets reports a formula parse error despite the range and quotation marks being correct, check the locale under File > Settings and use the separator expected by that sheet. The function name and empty-string criterion do not change.

What Google Sheets Considers Empty

A cell that has never had a value entered is the clearest case of an empty cell. A cell containing a formula that displays an empty string is more subtle. For example, a formula might return "" when a related field is missing. Although the cell contains a formula, its displayed result is blank, and blank-counting functions can treat that result as empty. This differs from functions such as COUNTA, which may count a cell because it contains a formula.

Whitespace is different. A cell containing one or more spaces has content, even if it looks blank on screen. The same is true of a tab or other invisible character brought in through pasted or imported data. A cell containing the number 0 is also not empty. If a result seems higher or lower than expected, click a supposedly blank cell and inspect the formula bar to see whether it contains a space, a zero, or a formula.

Formatting alone does not make a cell nonempty. A blank cell with a fill color, border, number format, or conditional formatting can still be counted as blank. COUNTIF checks cell values and criteria; it does not count how many cells merely look empty or have particular formatting.

COUNTIF, COUNTBLANK, and ISBLANK

For the usual question “How many cells in this range are blank?”, COUNTIF with "" is a direct answer. Google Sheets also has COUNTBLANK, which is specifically intended to count empty cells. With responses in C2:C25, =COUNTBLANK(C2:C25) is an equivalent, readable option for a simple blank count. COUNTBLANK is convenient when the formula’s purpose is unmistakably to count blanks.

ISBLANK answers a different kind of question: whether a particular cell is truly empty. For example, =ISBLANK(C2) returns TRUE if C2 is empty and FALSE otherwise. A formula entered in C2 makes the cell nonempty to ISBLANK even if that formula displays nothing. That distinction matters when auditing formulas or deciding whether a record has ever been populated.

If you need a count of genuinely untouched cells rather than cells that display blank, you can count ISBLANK results. With the cells to check in C2:C25, use =SUMPRODUCT(--ISBLANK(C2:C25)). This counts cells that are actually empty, not cells occupied by formulas returning an empty string. Choose the function based on what “empty” means for your task, rather than assuming all blank-looking cells are identical.

Useful COUNTIF Examples

These examples show how the same criterion can answer several common spreadsheet questions. In each case, replace the sample range or values with the locations and labels in your sheet.

Find missing email addresses: With addresses in D2:D500, =COUNTIF(D2:D500,"") counts records with a blank address.
Count unassigned tickets: With ticket owners in B2:B101, =COUNTIF(B2:B101,"") counts tickets that have no owner entered.
Count blank cells in a row: With monthly figures in B4:M4, =COUNTIF(B4:M4,"") counts months without a value.
Count blank cells among records with a particular status: With statuses in A2:A100 and completion dates in B2:B100, =COUNTIFS(A2:A100,"Open",B2:B100,"") counts rows whose status is Open and whose date cell is blank.

COUNTIFS is useful for the last example because it applies multiple conditions to corresponding rows. The criteria ranges should cover the same rows and have matching dimensions. A blank date in a row marked Closed will not contribute to the example’s result; both conditions must be satisfied.

Counting Blank Versus Nonblank Cells

To count cells that are not blank, use the not-equal-to-empty criterion: =COUNTIF(A2:A100,"<>"). In the formula itself, the criterion is the characters less-than, greater-than, and an empty quoted string. In this HTML display, the less-than and greater-than signs are shown as entities so they do not interfere with the markup. In a Google Sheets cell, type the ordinary characters.

A helpful check is to compare the blank and nonblank counts with the number of cells in the range. If you are using A2:A100, there are 99 cells. The two counts should generally add to 99 when the criteria classify every cell as either blank or nonblank. If they do not, inspect the range boundaries and any cells containing formulas or unusual imported values. Formula results and invisible characters can make a visually blank cell behave differently from what you expect.

Do not use "0" as a substitute for an empty criterion. A numeric zero is a real value, not a blank. Likewise, a dash, the word “N/A,” or a space is content. To count a specific marker instead, provide that value as the criterion, such as =COUNTIF(A2:A100,"N/A").

Common Problems and How to Fix Them

The count is unexpectedly low: Check for spaces or invisible characters in cells that look blank. Click a sample cell or test it with =LEN(A2), replacing A2 with the cell to inspect. A result above 0 indicates that the cell’s evaluated text contains characters.
The count includes cells that look blank: Look for formulas returning "". Decide whether those formula results should count as blank for your purpose. If you want only never-filled cells, use an ISBLANK-based count instead.
The formula returns a parse error: Confirm that the range and quotation marks are paired correctly. Also check whether your spreadsheet’s locale expects semicolons rather than commas between arguments.
The number does not update as expected: Make sure the formula’s range includes the changed cells. A formula that checks A2:A100 will not include a new entry in A101.
The result seems to count records hidden by a filter: COUNTIF evaluates the cells in its range, including rows hidden by a filter. Applying a filter does not automatically limit this formula to visible rows.

For a simple whitespace cleanup, TRIM can remove ordinary leading and trailing spaces from text. Be cautious about changing source data: spaces can sometimes be meaningful, and imported nonbreaking spaces may not behave like ordinary spaces. If you are cleaning a shared or important sheet, use a helper column or make a copy before replacing data.

Filters, Data Quality, and Practical Choices

A blank count is often a small data-quality check. For example, a count of missing order IDs can reveal incomplete records before analysis, while a count of blank survey answers can help distinguish unanswered questions from responses such as “Prefer not to say.” Keep those meanings separate: an intentionally selected response is not the same as an unfilled cell.

COUNTIF includes hidden rows in its range, whether rows are hidden manually or excluded by a filter. That is useful for an overall total, but it may surprise someone who expects the count to change when filtering. If the goal is specifically to count blanks among visible rows only, a plain COUNTIF is not sufficient; the calculation needs to account for row visibility as well as blankness. State clearly which total a dashboard is showing so readers do not mistake an all-record count for a filtered subtotal.

For a stable worksheet, put the result in a clearly labeled summary area, such as “Missing dates,” and make the checked range obvious. If users add rows regularly, consider using a range with room to grow or designing the sheet so the formula’s range is updated along with the data. Avoid placing a count formula inside the range it counts if that could make the formula count itself or create confusing results.

Frequently Asked Questions

How do I count blank cells in Google Sheets?

Use =COUNTIF(A2:A100,""), changing A2:A100 to the range you want to check. You can also use =COUNTBLANK(A2:A100) for a function specifically named for counting blank cells.

Does COUNTIF count a formula that returns an empty string?

A formula that displays "" can be treated as blank by blank-counting functions, even though the cell contains a formula. If you need to distinguish that case from a truly empty cell, test with ISBLANK or use an ISBLANK-based count.

How do I count cells that are not empty?

Use =COUNTIF(A2:A100,"<>"), typing the less-than and greater-than characters normally in Sheets. Check formula-generated empty strings separately if the distinction is important to your result.

Why is a cell that looks empty not counted as blank?

It may contain a space, an invisible character, a zero, or a formula. Click the cell and inspect its contents. Formatting such as a background color or border does not by itself make an empty cell nonempty.

Does COUNTIF ignore filtered-out rows?

No. COUNTIF includes all cells in the range, including rows hidden by a filter. Use a calculation designed for visible rows if you need the count to follow the current filter.

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.