Google Sheets Count If Not Blank: Formulas and Fixes

To count cells that are not blank in Google Sheets, use =COUNTA(A2:A100), replacing the range with your own. If cells contain formulas that display nothing, or entries made of spaces, the best formula may differ; the key is deciding what “not blank” should mean for your data.

The basic formula: COUNTA

COUNTA counts cells containing any value in a range: text, numbers, dates, Boolean values such as TRUE, errors, and formulas. It is usually the right starting point for counting filled-in cells in a list, survey column, or inventory table.

With the items you want to count in A2:A100, enter =COUNTA(A2:A100) in a separate cell. If 18 cells in that range contain something, the result is 18. The function accepts a cell range, and you can also provide multiple ranges separated by commas when you want to count entries across different areas.

Avoid including the column heading unless you want it included in the result. For example, if A1 contains “Name,” counting A1:A100 counts the heading as one filled cell. Starting at A2 avoids that common off-by-one error.

What Google Sheets considers nonblank

“Not blank” can mean different things depending on how a sheet was made. A cell can look empty while still containing a formula, a space, or another value. COUNTA counts values and formulas even if the displayed result looks empty, so its total may be larger than the number of visibly filled cells.

These are typical cases to keep in mind:

A truly empty cell is not counted by COUNTA.
A cell containing a word, number, date, or Boolean value is counted.
A cell containing a space is counted, even though the space is difficult to see.
A formula returning an empty text string, such as a formula whose result is "", is counted by COUNTA.
A formula returning zero is counted; zero is a value, not a blank.

This distinction matters when another formula populates a column. For example, a formula copied down 100 rows may show text in only 12 rows and an empty-looking result in the rest. COUNTA can count all 100 formula cells, not just the 12 visibly populated results.

Count cells that are not empty-looking

If your range includes formulas that return an empty string and you want to omit those results, use COUNTIF with a “not equal to blank” criterion. With the data in A2:A100, enter =COUNTIF(A2:A100,"<>"). This counts cells that meet the nonblank criterion and is often a better fit for formula-generated lists than COUNTA.

As with any counting method, compare the result with a few known rows when the distinction is important. Google Sheets evaluates criteria according to the cell’s value and contents, and unusual data—especially formulas and whitespace—can make “looks blank” different from “is blank.” COUNTIF is also useful when the range contains a mix of normal entries and formulas but you want to exclude empty-looking results.

For a straightforward list of manually entered names or IDs, COUNTA is simpler and easier to understand. For a calculated output column, test COUNTIF on a small sample that includes a truly empty cell, a formula returning an empty string, and a visible value before applying it to a large sheet.

Ignore cells containing only spaces

Neither a normal space nor a series of spaces is a truly empty cell. COUNTA counts it, and a simple nonblank check may also treat it as content. If pasted data has whitespace-only cells, trim the values as part of the test.

With candidate entries in A2:A100, enter =SUMPRODUCT(--(LEN(TRIM(A2:A100))>0)). This counts cells whose content has at least one character left after TRIM removes leading and trailing ordinary spaces. It also ignores a formula result that is an empty string. If the range contains “ Oak ”, that entry counts; a cell containing only ordinary spaces does not.

TRIM is not a universal cleanup tool. It does not remove every special whitespace character that can arrive from a website or another application, such as a nonbreaking space. If suspicious entries remain, inspect or clean the source data rather than assuming every invisible character is a regular space. You can also test individual cells by editing them or using the formula bar to see whether they contain a value.

Choose a range that fits the job

A fixed range such as A2:A100 is easy to read and keeps calculations limited to the rows you expect. Its drawback is that new data entered below row 100 will not be included. If the list grows regularly, choose a larger range or an open-ended column range, while excluding the header.

For example, if column A has a heading in A1 and entries may continue down the sheet, use =COUNTA(A2:A). This counts from row 2 downward. An open-ended range is convenient, but it asks Sheets to consider many cells, and a sheet with many calculations may be easier to manage with deliberate, bounded ranges. Make the choice based on how the sheet is maintained rather than automatically selecting an entire column.

If you use multiple separate blocks, COUNTA can take more than one range argument. With entries in A2:A20 and C2:C20, enter =COUNTA(A2:A20,C2:C20). The result combines the nonempty cells in both blocks. Confirm that the areas do not overlap, or an overlapping cell could be counted more than once.

Count nonblank cells that meet another condition

Sometimes the question is not simply how many cells are filled, but how many filled cells belong to a particular group. For instance, you might want to count records that have both an owner and a status of “Open.” COUNTIFS can apply multiple criteria to matching ranges.

With owner names in A2:A100 and statuses in B2:B100, enter =COUNTIFS(A2:A100,"<>",B2:B100,"Open"). This counts rows where the owner cell is nonblank and the status is Open. The criteria ranges need to cover the same set of rows so each owner is checked against the status on the same record.

Be clear about which field defines a complete record. If a row should count only when it has a name, checking the name column is appropriate. If it should count only when both a name and date are present, add a nonblank criterion for the date column too. Counting one required field is not the same as counting complete rows.

COUNTA, COUNTIF, and COUNTBLANK compared

These functions answer related but different questions. COUNTA counts cells with content, including formulas that return empty text. COUNTIF can count cells matching a criterion, such as the not-blank condition. COUNTBLANK counts blank cells and, in Google Sheets, includes cells whose formulas return an empty string. A blank count is therefore not always the simple opposite of a COUNTA count.

For example, suppose a range has 10 cells: six contain visible entries, two contain formulas displaying nothing, and two are truly empty. COUNTA can return 8 because the two formulas are still present. COUNTBLANK can return 4 because it includes the two truly empty cells and the two formula results that display as empty. The numbers describe different definitions, not a calculation error.

If you need to check how many cells are empty, use COUNTBLANK rather than subtracting COUNTA from the total range size when formulas may return empty strings. With the cells in A2:A100, enter =COUNTBLANK(A2:A100). Use this alongside a sample inspection if the sheet contains imported data or formulas whose displayed output can be blank.

Common counting problems and quick checks

When a total seems wrong, look at the range and the underlying cell contents before changing functions. Small inconsistencies in input or setup often explain the difference.

The heading is included: Change A1:A100 to A2:A100 if row 1 is a label.
Formula-filled rows inflate COUNTA: Use COUNTIF for a nonblank criterion, or a TRIM-based test if spaces also need to be ignored.
Zeros are counted: This is expected. If zero should mean “missing” in your particular dataset, define that as a separate rule rather than treating all nonblank formulas as wrong.
Spaces are counted: Clean the input or use the TRIM-based formula. Check whether the characters are ordinary spaces.
New entries are missed: Expand the fixed range or use an open-ended range that starts below the heading.
The total is higher than the number of records: Check for repeated entries, notes in cells, and formulas copied farther down than intended.

Filtering does not change the meaning of COUNTA: it normally counts matching content in the specified range whether or not rows are hidden by a filter. If your goal is to count only visible rows, that is a different task and requires a method designed to account for filtered or hidden rows.

Frequently Asked Questions

What is the formula to count nonblank cells in Google Sheets?
Use =COUNTA(A2:A100), replacing A2:A100 with your range. It counts cells with values or formulas, including formulas that display an empty string.

Does COUNTA count cells with formulas that return blank?
Yes. COUNTA counts the formula itself as content even when its displayed result looks empty. If you need to exclude empty-looking formula results, try COUNTIF with a nonblank criterion and verify it against your data.

How do I count cells that contain only spaces?
A basic COUNTA count includes them because a space is content. Use a TRIM-based test, such as =SUMPRODUCT(--(LEN(TRIM(A2:A100))>0)), to ignore cells made only of ordinary leading or trailing spaces.

What is the difference between COUNTA and COUNTBLANK?
COUNTA counts cells with content, while COUNTBLANK counts empty cells and formula results that display as empty. Because formulas can look blank while still existing, these totals are not always simple opposites.

Can I count nonblank cells in an entire column?
Yes. If row 1 is a heading and data starts in A2, use =COUNTA(A2:A). An open-ended range includes future entries but may be less efficient in a very large, calculation-heavy sheet than a suitable bounded range.

Which formula should you use?

For ordinary lists, start with COUNTA: it is direct, widely understood, and counts all cells with actual content. Choose COUNTIF when formula-produced empty strings should be treated as blank, and use the TRIM-based SUMPRODUCT approach when whitespace-only cells should also be ignored. If the count must reflect filtered rows, complete records, or special imported characters, define that requirement explicitly and test a few representative cells before relying on the total.

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.