Google Sheets COUNTIF for Non-Empty Cells

To count non-empty cells in Google Sheets, use =COUNTIF(A2:A100,"<>"), replacing A2:A100 with your range. The criterion <> means “not equal to an empty value,” so the formula counts populated cells while excluding ordinary blank cells.

The basic COUNTIF formula

COUNTIF checks a range against one condition and returns how many cells meet it. Its structure is =COUNTIF(range, criterion). For a non-empty count, the range is the group of cells you want to inspect, and the criterion is "<>". The quotation marks make the comparison operator a text criterion that Sheets can interpret.

For example, with survey responses in A2:A100, enter this in a cell outside that range:

With responses in A2:A100, the formula is =COUNTIF(A2:A100,"<>"). If 37 cells in that range contain entries and the rest are blank, the result is 37.

You can use a shorter or longer range to suit the sheet. A fixed range such as A2:A500 is often a good choice when you know roughly how much data to expect. A whole-column reference such as A:A is convenient when new rows will be added, though limiting a range can make a complex spreadsheet easier to manage.

What COUNTIF considers non-empty

The formula counts cells with values such as text, numbers, dates, and zero. A cell containing the number 0 is not blank, even if a number format or custom display makes its contents look unusual. A cell containing a space is also not technically empty. By contrast, a cell that has never had a value entered is an ordinary blank and is not included by the <> criterion.

Keep “not empty” separate from “looks filled” and “contains meaningful information.” COUNTIF checks cell contents, not whether a person considers an entry useful. A cell with a stray space, a placeholder such as “N/A,” or an accidental value will count. Likewise, the formula does not judge whether a date is valid for your task or whether a response meets your business rules.

Formulas that return an empty string, written as "", are a special case worth checking in your own sheet. They can behave differently from cells that are genuinely empty, and counting formulas is not always the same as counting visible results. If your range contains formulas that deliberately display nothing, compare the COUNTIF result with the cells you can see before relying on it for a report.

Example with a small range

Suppose A2:A7 contains these entries: North, a blank cell, the number 0, the text 0, a date, and Complete. There are six cells in the range, but one is blank. With those values in A2:A7, enter =COUNTIF(A2:A7,"<>"). The result is 5: North, both forms of zero, the date, and Complete each count as one non-empty cell.

This example also shows why a blank-looking zero can be confusing. If a number format hides zero, or if the column is narrow, a cell may appear empty even though its value is 0. Click the cell or inspect the formula bar to distinguish a zero from a truly empty cell. COUNTIF uses the stored value rather than the visual impression made by the cell’s formatting.

To avoid counting a header, start the range below it. If A1 says “Response,” use A2:A rather than A:A. Otherwise the header itself is a non-empty cell and contributes one to the total. The same principle applies to footnotes, totals, and labels included in a column.

Count non-empty cells that meet another condition

Sometimes the goal is not to count every populated cell, but only populated cells associated with a particular status. COUNTIFS applies multiple conditions to corresponding ranges. For example, if names are in A2:A100 and statuses are in B2:B100, you can count rows where the name is present and the status is Open.

With names in A2:A100 and statuses in B2:B100, use =COUNTIFS(A2:A100,"<>",B2:B100,"Open"). This counts rows in which A is non-empty and B contains Open. The two ranges should cover the same rows so each name is matched with the status on its own row.

COUNTIF can also count entries matching a specific value, but it only accepts one condition. Use COUNTIFS when you need conditions such as a non-empty name plus a particular team, status, or date category. Remember that text criteria such as Open are generally not case-sensitive in COUNTIF-style matching, so Open and OPEN are normally treated alike.

Spaces and cells that only look blank

A common source of unexpected counts is a cell containing one or more spaces. COUNTIF sees a space as content, so =COUNTIF(A2:A100,"<>") can include a cell that looks blank on the screen. Leading or trailing spaces in an otherwise valid entry also remain part of the stored text, even though they may be hard to notice.

If you want to count cells whose contents remain after trimming ordinary spaces, use a formula that tests the trimmed text. With values in A2:A100, enter =SUMPRODUCT(--(LEN(TRIM(A2:A100&""))>0)). This returns the number of cells whose trimmed contents have a length greater than zero. For example, a cell containing three ordinary spaces is ignored, while a cell containing 0 still counts because its text representation has a character.

TRIM removes extra ordinary spaces around text and reduces repeated ordinary spaces between words. It does not remove every possible invisible character. Data copied from websites or other systems may include non-breaking spaces or other characters that look blank; those may need to be cleaned separately. Also, if a cell contains an error value, an expression involving that cell can propagate an error rather than produce a count, so inspect or clean error cells when necessary.

COUNTIF compared with COUNTA

COUNTA is another common way to count populated cells. It counts cells that contain a value, including text, numbers, and formulas. For a simple range with no formulas returning empty strings and no visually blank spaces, COUNTA and COUNTIF with "<>" often give the same result. They are not interchangeable in every spreadsheet, particularly when formulas or whitespace are involved.

With values or formulas in A2:A100, =COUNTA(A2:A100) counts cells that Sheets regards as containing content. A formula that displays an empty string may still be counted by COUNTA because the cell contains a formula. If your aim is to count visibly filled results rather than cells containing formulas, verify the behavior on your actual data instead of assuming COUNTA answers that question.

Use COUNTIF when you want a condition-based count and want to express “not empty” as a criterion. Use COUNTA when you want a quick count of cells containing something, including formulas. Use a trimming approach when spaces should not qualify. No single formula can decide whether an entry is meaningful; if you need to exclude placeholders or invalid values, add an explicit criterion for those cases.

Common problems and troubleshooting

The result is one too high: Check whether the range includes a header, a note, or a total. Also look for a space or other invisible character in a cell that appears blank.
The result is lower than expected: Confirm that the range includes all relevant rows and that the formula has not been overwritten. Check whether the cells you expect to count contain formula results that appear blank.
The formula shows an error: Make sure the range and criterion are separated by a comma in the formula. Some spreadsheet locales use semicolons as argument separators, so the separator shown by your sheet may differ.
The count changes after adding data: If the formula refers to a fixed range, entries below its last row are outside the count. Expand the range or use a suitable open-ended column reference.
The count includes filtered-out rows: COUNTIF evaluates the specified range; it is not a visible-rows-only count. Filtering changes what is displayed, not which cells are in that range.

To check a surprising result, temporarily select the range and inspect the formula bar for suspicious cells. You can also test a smaller sample range containing a known number of entries. These checks help separate a range-reference mistake from a formatting issue or an unexpected cell value.

Using the formula in Google Sheets

In Google Sheets in a web browser, select an empty cell where you want the total, type the formula, and press Enter. You can also begin typing =COUNTIF( and select the range with the mouse before entering the criterion. If you place the result inside the range being counted, the formula may include its own cell; keep the result outside the range or adjust the reference to avoid that circular counting problem.

Google Sheets recalculates the result when values in the referenced range change. If you plan to copy the formula elsewhere, decide whether the range should move with the copy. Ordinary references such as A2:A100 adjust when copied; dollar signs can lock a reference, as in $A$2:$A$100. For a single total that you will not copy, the unlocked reference is usually straightforward.

The basic COUNTIF function is also available in Excel, but the instructions here are for Google Sheets in a browser. Menu paths are not needed for this calculation: it is entered directly in a cell. If you open the sheet in Excel, check the result there when the range contains formulas returning empty strings or other edge cases, because spreadsheet applications can differ in how particular data situations are handled.

Frequently Asked Questions

What is the formula to count non-blank cells in Google Sheets?
Use =COUNTIF(A2:A100,"<>"), replacing A2:A100 with your range. The <> criterion means not equal to blank.

Does COUNTIF count zero as a non-empty cell?
Yes. A numeric zero is a value, so it counts. If zero appears blank because of number formatting, the formula still evaluates the stored value.

Does COUNTIF count cells containing only spaces?
Yes. Spaces are content, not true blanks. If spaces should be ignored, use a trimming-based count or clean the source data first.

How do I count non-empty cells in a whole column?
Use a reference such as =COUNTIF(A:A,"<>"). If row 1 is a header, the header is included in the total; start at A2 if you want to exclude it.

Why is my count different from COUNTA?
COUNTA may count cells containing formulas even when those formulas display an empty string. Spaces and other hard-to-see content can also affect the result. Inspect the cells and choose the formula that matches whether you mean stored content or visible, meaningful entries.

Will COUNTIF count only visible rows after filtering?
No. A filter hides rows from view but does not remove them from the COUNTIF range. The formula counts matching cells in the referenced range, including rows currently hidden by a 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.