Google Sheets COUNTIF: Count Non-Empty Cells in a Range

If your entries are in A2:A100, count cells that are not empty in Google Sheets with =COUNTIF(A2:A100,"<>"). The criterion "<>" means “not equal to blank,” so the result is the number of cells in that range containing a value or text.

How the COUNTIF formula works

COUNTIF checks a range against one condition and returns how many cells meet it. Its structure is =COUNTIF(range, criterion): the first argument identifies the cells to inspect, and the second states what to count. For a non-empty count, the criterion is the two-character comparison operator <> inside quotation marks.

For instance, if A2:A6 contains the names Maya, an empty cell, Omar, an empty cell, and Priya, respectively, the formula returns 3. It counts the three populated cells and leaves the two empty cells out. The criterion is text supplied to COUNTIF, which is why the quotation marks are required. In Google Sheets, enter the formula in a cell outside the range being counted so it does not include itself or create a circular reference.

You can replace A2:A100 with the range that fits your sheet. The row numbers need not begin at 2; they simply need to cover the data you want counted. If you use a whole-column reference, such as A:A, remember that a header in the column is non-empty and will be included in the result.

What Google Sheets treats as non-empty

The formula counts cells with text, numbers, dates, or other stored values. A numeric zero is not blank, and neither is a date: both count as populated cells. Formatting alone does not make an empty cell count. A background color, border, or number format applied to a blank cell does not add a value for COUNTIF to find.

There are a few important cases where a cell may look empty but still affect your count:

A formula returning an empty string: A cell containing a formula such as one that returns "" can look blank. COUNTIF with the not-empty criterion generally treats that empty-string result as blank, while COUNTA counts cells containing formulas even when their displayed result is empty. This distinction matters when formulas fill a column in advance.
Spaces or invisible characters: A cell containing one or more spaces is not truly empty, so it can be counted even though it appears blank. Nonbreaking spaces and other invisible characters can cause similar surprises.
Hidden display values: A cell might contain a value that is concealed by formatting, conditional formatting, or a narrow column. COUNTIF evaluates the cell’s contents, not just what is readily visible on screen.

If you are unsure why a cell is included, select it and look at the formula bar. That can reveal spaces, a formula, or a value that the cell’s formatting makes hard to see.

Choose a range that matches your data

Use a bounded range such as A2:A100 when you know where the records begin and end. This keeps a header out of the count and makes the intended data area easy to understand. If new records are added often, choose an endpoint with room to grow, or use a whole-column reference and account for headers and any other content in that column.

Suppose column A has a heading in A1 and records below it. If you count A:A, the heading itself contributes one to the total. Starting at A2 avoids that. The same issue comes up with notes, totals, or stray values underneath a table: COUNTIF counts any qualifying populated cell in the selected range, whether or not it belongs to the main data set.

For a sheet with multiple data columns, count only the field relevant to your question. Counting a whole rectangular block such as A2:C100 checks each cell in that block, rather than counting rows with at least one entry. If you want the number of records with a name in column A, use column A alone. A count of populated cells is not automatically a count of populated rows.

Count non-empty cells with another condition

COUNTIF is designed for one condition. To require a non-empty cell and another condition in a separate column, use COUNTIFS, which applies multiple criteria to corresponding ranges. For example, if names are in A2:A100 and regions are in B2:B100, this counts records with a name whose region is East:

=COUNTIFS(A2:A100,"<>",B2:B100,"East")

The ranges should cover the same rows so each name is checked alongside its matching region. You can change East to another region, or refer to a cell containing the region name. The non-empty criterion still checks the name column; it does not mean that every column in the row must be filled.

If the task involves alternatives rather than simultaneous conditions—for example, counting cells in one column that are either East or West—COUNTIFS alone is not an “OR” test. You can add separate COUNTIF results for the two criteria when each cell should belong to only one category. Be careful with overlapping conditions, because adding counts can count the same cell more than once.

Count blanks instead, or compare with COUNTA

To count empty cells in a range, use COUNTBLANK. If the entries are in A2:A100, the formula is =COUNTBLANK(A2:A100). COUNTBLANK is useful for finding missing values, but its result is not always simply the number of visually empty-looking cells: it also treats formulas that return an empty string as blank. Spaces, by contrast, are content rather than empty cells.

COUNTA provides a different comparison. With entries in A2:A100, =COUNTA(A2:A100) counts cells containing values, including text, numbers, and formulas. Because a formula is present even if its displayed result is an empty string, COUNTA may return a larger total than COUNTIF with the not-empty criterion. Choose based on what you mean by “filled”: visible or meaningful results, or any cell that contains a formula or value.

If your data should have no formulas that display as blank, the difference between these approaches may not matter. If formulas are prefilled down a column, it often does. You can compare the two totals to investigate whether formula cells are contributing to COUNTA but not to the non-empty count you expect.

Ignore cells containing only spaces

COUNTIF’s not-empty criterion counts spaces because a space is a character, not an empty cell. If imported data or manual entry leaves cells containing only ordinary spaces, first decide whether those cells should count as missing. To exclude cells that are empty or contain only spaces, use a formula that trims surrounding spaces and checks the remaining text length.

With the values to inspect in A2:A100, this formula counts cells whose contents remain longer than zero characters after TRIM removes leading and trailing ordinary spaces:

=SUMPRODUCT(--(LEN(TRIM(A2:A100))>0))

For example, a cell containing three ordinary spaces is excluded, while a cell containing the word North is included. TRIM also reduces repeated spaces within text, but that does not change whether a non-empty word has a positive length. This approach is not a universal cleanup tool: unusual whitespace characters, such as nonbreaking spaces copied from a webpage, may need separate cleaning. If data quality is a recurring issue, cleaning the source values can make later counts easier to interpret.

Common problems and quick checks

If the result seems wrong, check the selected range and the contents of cells that look blank before changing the formula. Many apparent COUNTIF errors come from a header being included, a formula returning an empty string, or spaces left behind in imported data.

The count is one too high: Check whether the range includes its heading, a total, or a note outside the records.
Visibly blank cells are counted: Inspect for spaces or other characters. If the cells contain formulas, compare the result with COUNTA and consider whether formula-produced blank strings should count.
The count is lower than expected: Confirm that the range reaches the last data row and that the formula is counting the intended column.
The formula displays an error: Check that the opening and closing parentheses match and that the criterion has quotation marks. Google Sheets may also use a different argument separator in some locale settings; follow the separator style accepted by your sheet.
The result changes after editing: COUNTIF recalculates when relevant cell contents change. A change in formatting alone usually does not change whether a cell is considered empty.

Google Sheets in a web browser lets you enter the formula directly into a cell or the formula bar. Excel has a similar COUNTIF formula and criterion, but menu names and some behaviors can differ between spreadsheet products; the formulas here are for Google Sheets. If you are following an Excel guide, do not assume that every related function or interface instruction is identical.

Frequently Asked Questions

What is the formula to count cells that are not blank in Google Sheets?
Use COUNTIF with the criterion "<>". For data in A2:A100, enter =COUNTIF(A2:A100,"<>"). Replace the range with the cells you need to check.

Does COUNTIF count a cell containing zero?
Yes. Zero is a value, not a blank cell, so it meets the not-empty criterion. Dates and text values are counted as well.

Will COUNTIF count a formula that displays nothing?
A formula returning an empty string can look blank and is generally treated as blank by COUNTIF with "<>". COUNTA, however, counts the cell because it contains a formula. The two functions answer slightly different questions.

How do I count cells that look empty but contain spaces?
Spaces are characters, so the basic COUNTIF formula counts them. If you want to ignore cells containing only ordinary spaces, use a TRIM-and-LEN check such as =SUMPRODUCT(--(LEN(TRIM(A2:A100))>0)).

How do I count non-empty cells in a whole column?
Use a whole-column range, such as A:A, in COUNTIF. Remember that the count includes any populated header, note, or other cell in that column, so a bounded range may be better when you want records only.

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.