COUNTIF for Nonblank Cells in Google Sheets: Tips

To count cells that are not blank in Google Sheets, use =COUNTIF(A2:A100,"<>"). Replace A2:A100 with your range; the criterion "<>" means “not equal to blank.”

How the nonblank COUNTIF formula works

COUNTIF checks a range against one condition and returns the number of cells that meet it. Its general pattern is =COUNTIF(range,criterion). For a not-blank count, the criterion is the text string "<>": the two angle brackets mean “not equal to,” and the empty value after them represents blank.

With inventory entries in A2:A100, for example, the formula =COUNTIF(A2:A100,"<>") counts entries in that section of column A that Sheets treats as nonblank. It does not change the cells or remove empty rows; it simply returns a number that updates as the range changes.

The formula works with text and numeric values, including a zero entered as a number. Dates are stored as numeric values in spreadsheets, so a date in the range is counted too. A cell containing a space is not truly empty and may also be counted. These distinctions matter if your data has formulas, pasted whitespace, or imported values.

A worked example

Suppose B2:B8 contains the following: “Paid” in B2, a genuinely empty B3, the number 0 in B4, “Pending” in B5, a date in B6, one space in B7, and a genuinely empty B8. With those values in B2:B8, enter =COUNTIF(B2:B8,"<>") in another cell. The result is 5: “Paid,” 0, “Pending,” the date, and the space are all counted; the two empty cells are not.

This example shows why “not blank” is not always the same as “contains a useful entry.” A space looks empty on the sheet, but it is a character. Similarly, a zero may look like no activity to a person, but it is a real value and should be included if the question is whether the cell contains something.

For a quick check while setting up a sheet, put a few representative values in a small range, including a blank, a zero, and any formulas your real data uses. The returned count can help confirm that the criterion matches your intended definition.

Choose a range that fits the job

A bounded range such as A2:A100 is usually easier to understand and can avoid counting a header. If the column will grow and you want the formula to include future entries, you can use an entire column. With data anywhere in column A, the formula is =COUNTIF(A:A,"<>"). If A1 is a heading, it is counted because the heading is also nonblank; start at A2 when you want to exclude it.

For a list with a header in row 1 and records from row 2 onward, use the row range that covers the records. Keep the range consistent with the data you mean to measure. For example, if records end at row 500, A2:A500 makes the intended scope clear, while A:A also includes the heading and any notes elsewhere in the column.

Google Sheets supports open-ended column references such as A2:A, which cover row 2 through the bottom of the column. With entries in column A and a heading in A1, =COUNTIF(A2:A,"<>") counts the data area while allowing new rows below it. In very large or calculation-heavy sheets, a sensible bounded range can be easier to maintain and may reduce unnecessary work.

COUNTIF compared with COUNTA and COUNTBLANK

COUNTIF is useful when you want to express the condition directly or combine it with other criteria. For a simple count of cells that contain something, COUNTA is another common choice. With the same entries in A2:A100, =COUNTA(A2:A100) counts cells containing values of any type, including text and numbers. COUNTA does not let you add a criterion in the same way COUNTIF does.

COUNTBLANK answers the reverse question: how many cells in a range are blank. With the section to check in A2:A100, =COUNTBLANK(A2:A100) returns the number Sheets treats as blank. If the range has 99 cells, there are no error or special cases, and your definition of blank is consistent, the nonblank count plus the blank count should total 99.

Do not assume that every method treats formula-generated empty strings or unusual imported data identically. COUNTA, COUNTIF, and COUNTBLANK answer related but not identical questions, and a cell that contains a formula is not physically empty. If your count must exclude formulas whose displayed result is an empty string, test the formula against representative cells or use the length-based method below.

When cells contain formulas that return an empty string

A cell with a formula is different from a truly empty cell, even if the formula displays nothing. For example, a formula might return an empty string, written as "", when a row has no data. That cell can be confusing: it looks blank, but it contains a formula. Blank-counting behavior can depend on the function and the evaluated result, so do not rely on appearance alone when this distinction affects a report.

If you specifically want to count cells whose evaluated contents have at least one character, a length test can make that intent explicit. With possible entries in A2:A100, use =SUMPRODUCT(--(LEN(A2:A100)>0)). LEN returns the length of each value, the comparison tests whether that length is greater than zero, and SUMPRODUCT totals the true tests. A formula returning an empty string has length zero and is not included.

This method still counts a space because a space has a length of one. It also counts ordinary text and numeric values represented with a nonzero length. If cells may contain errors, test the formula on those rows too, because an error in the range can affect a calculation. Choose the method based on the meaning you need: “cell is not empty,” “displayed result has characters,” or “entry is valid data” are different requirements.

Spaces, imported data, and other common surprises

Extra spaces often arrive when data is copied from a website, exported from another system, or entered manually. A cell containing one or more spaces is not empty, even though it may appear blank. COUNTIF with "<>" can therefore produce a higher count than the number of visible names or labels. If spaces should not count, clean the source values or define a separate test that ignores whitespace.

Before changing a formula, inspect a few cells that seem empty. Select one and look at the formula bar: a visible space or a formula may explain the difference. You can also temporarily make the column wider or apply a contrasting fill to locate cells that contain unexpected content. For a small data set, clearing a cell with Edit > Delete values removes its contents; deleting a whole row is not necessary just to clear a value.

Errors are another reason a count may not match expectations. A cell displaying an error is not the same as a blank cell. Decide whether errors should count as occupied cells, be excluded, or be fixed before counting. For a reliable report, document the rule and clean inconsistent inputs rather than trying to hide a data-quality problem inside a complicated count.

Count nonblank cells only when another condition is met

To count entries in one column only when a related column has a particular status, use COUNTIFS, which applies multiple criteria. Suppose names or IDs are in A2:A100 and status labels are in C2:C100. To count rows where column A is nonblank and column C says Open, enter =COUNTIFS(A2:A100,"<>",C2:C100,"Open"). The two ranges should cover matching rows so each record is evaluated against its own status.

This is helpful for questions such as “How many listed tasks are open?” rather than “How many cells in the status column say Open?” The nonblank condition prevents a row with an Open status but no corresponding entry in column A from being included. Adjust the ranges and the exact status text to fit your sheet; text criteria should match the data as it is recorded.

In Google Sheets, the basic COUNTIF and COUNTIFS formulas use commas between arguments in many spreadsheet locales. If your sheet’s locale expects a different argument separator, the formula entry may require that locale’s separator. The function names and criteria remain the same. The formulas shown here are also familiar to Excel users, though this guidance and the menu references are for Google Sheets in a web browser.

Troubleshooting a count that looks wrong

If the result is unexpectedly high or low, check these likely causes before replacing the function:

Header included: An entire-column range counts a nonblank heading. Start at the first data row if the heading should be excluded.
Spaces counted: A cell that looks empty may contain spaces or other invisible characters.
Formula in an apparent blank: A formula may return an empty string while leaving the cell nonempty as a formula cell.
Range mismatch: The range may omit later rows or include notes and totals that are not records.
Zero is present: Numeric zero is a value, so it is included in a nonblank count.

For a quick diagnostic, compare the result with a small hand-counted section and inspect the cells where the totals differ. Also confirm that the formula refers to the correct sheet and column if you copied it from another tab. If the rule is “count completed records,” it may be better to count a required ID or use a completion-status criterion than to treat every nonblank cell as a record.

Frequently Asked Questions

What is the formula to count cells that are not blank 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 nonblank cell?
Yes. A numeric zero is a value, not an empty cell, so it is included by the not-blank criterion.

Does COUNTIF count a cell containing only a space?
It can, because a space is content even when the cell looks empty. Remove unwanted spaces or use a rule designed to ignore whitespace if those cells should not count.

How do I count nonblank cells in an entire Google Sheets column?
Use a whole-column range, such as =COUNTIF(A:A,"<>"). Remember that this includes a nonblank header; use A2:A if row 1 is a heading you want to leave out.

How can I count nonblank cells only when another column says Open?
Use COUNTIFS with a nonblank condition for the entry range and an Open condition for the status range. For example, with entries in A2:A100 and statuses in C2:C100, use =COUNTIFS(A2:A100,"<>",C2:C100,"Open").

Should I use COUNTIF or COUNTA?
Use COUNTIF when you want an explicit criterion or need to add conditions. COUNTA is a straightforward option for counting cells that contain something, but check formula-generated empty strings and other edge cases if the exact definition of blank matters.

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.