Google Sheets COUNTIF Not Blank: Formula and Examples

To count nonblank cells in Google Sheets, use =COUNTIF(A2:A100,"<>"), replacing A2:A100 with your range. The criterion "<>" means “not equal to blank,” so the formula counts cells with content, including numbers and dates.

What COUNTIF counts as not blank

COUNTIF checks each cell in the range against a condition and returns the number of matches. In this case, the condition is the not-equal operator, <>, followed by an empty criterion. A cell containing a name, a number, a date, or the value zero is not blank and is included. A genuinely empty cell is left out.

For example, suppose A2:A8 contains the text “Ana,” an empty cell, the number 0, the word “Note,” the date 1/15/2026, and two empty cells. With those values in A2:A8, =COUNTIF(A2:A8,"<>") returns 4: Ana, zero, Note, and the date. Zero is a value, not a blank, even though it can look visually similar to an empty cell if number formatting hides it.

COUNTIF does not judge whether a cell looks filled in. Background color, borders, font color, and other formatting do not make a cell count as nonblank. The cell needs a value or content that the criterion recognizes.

How to enter the formula

In Google Sheets in a web browser, click the cell where you want the result, type the formula, and press Enter. For a list in A2:A100, enter =COUNTIF(A2:A100,"<>"). The formula updates when values in that range change. If you later add rows below row 100, expand the range or use a larger range from the start.

Choose the column or range to check, such as A2:A100.
Select an empty result cell outside the range being counted.
Type =COUNTIF(, select or enter the range, then add a comma and the criterion "<>".
Close the parenthesis and press Enter. Check that the result matches a few cells you can inspect manually.

Google Sheets uses commas between function arguments in the examples here. Spreadsheet locale settings can affect separators and how dates are entered, so if Sheets rejects an otherwise valid formula, check File > Settings for the spreadsheet’s locale. COUNTIF is a Google Sheets function; Excel also has COUNTIF and the same basic criterion, though this article’s steps and examples are for Sheets.

Counting nonblank cells with extra conditions

Use COUNTIFS when a cell must be nonblank and another condition must also be true. COUNTIFS applies all of its criteria to corresponding rows, so each range should cover the same number of rows.

With task names in A2:A100 and statuses in B2:B100, this counts rows that have a task name and the status Open: =COUNTIFS(A2:A100,"<>",B2:B100,"Open"). A row with Open in column B but no task name in column A does not qualify. This is useful for tracking completed forms, assigned work, or records that need both an identifier and a particular status.

You can also use COUNTIF for other criteria, but be clear about what each condition means. For example, a criterion of ">0" counts positive numeric values rather than all nonblank cells; it excludes text, zero, and negative numbers. If you need a nonblank count restricted to one category, COUNTIFS is usually easier to read than trying to combine unrelated conditions in one criterion.

COUNTIF, COUNTA, and COUNTBLANK compared

These functions answer related but different questions. Choosing the one that matches the data avoids a result that looks plausible but includes the wrong cells.

COUNTIF with “<>”: Counts cells that meet the not-blank criterion. It is a direct choice when you want to count entries in a range while excluding blank results.
COUNTA: Counts cells containing values, including text, numbers, and formulas. A formula that displays an empty string can still be counted by COUNTA because the cell contains a formula.
COUNTBLANK: Counts blank cells, including cells whose formulas return an empty string. It answers the inverse-style question—how many are blank—rather than directly counting populated entries.

For instance, with values in A2:A20, =COUNTA(A2:A20) counts nonempty entries and formula cells, while =COUNTBLANK(A2:A20) counts blanks. Those totals may not add up to the number of rows if formulas returning empty strings are present, because the functions can treat formula cells differently. If a range contains formulas, compare the functions on a small sample before relying on a count for a report.

Formula blanks, spaces, and other tricky cells

A cell can appear blank without being truly empty. A formula such as =IF(B2>0,"Ready","") leaves a formula in the cell even when its displayed result is empty text. COUNTA can count that formula cell. COUNTBLANK treats a formula result of an empty string as blank, and COUNTIF with "<>" is commonly used to exclude empty-string results. If your workbook depends on the distinction, check the behavior against representative rows rather than judging only by appearance.

A cell containing one or more spaces is different: it contains text, so it is not genuinely empty and can be counted. This often happens when data is pasted from a website or exported from another system. If you want to ignore cells that contain only spaces, a plain COUNTIF is not enough. For text entries in A2:A100, use =SUMPRODUCT(--(LEN(TRIM(A2:A100))>0)). This counts cells whose contents remain longer than zero after TRIM removes leading and trailing spaces. It is intended for text-cleanup situations; review mixed data, such as numbers and dates, before using it as a general-purpose replacement.

There is also a difference between a cell that contains zero and one that is empty. A custom number format can hide zero, and a formula can display a dash or other placeholder, but the underlying value still exists. COUNTIF evaluates the cell’s content, not the visual display. To verify a suspicious cell, select it and inspect the formula bar.

Common problems and how to fix them

The formula returns zero unexpectedly. Confirm the range points to the cells you intended and that the criterion is typed exactly as "<>", including the quotation marks. A range that starts on the wrong row is a frequent cause of a surprising result.
Cells that look empty are included. Check for spaces, invisible pasted characters, or formulas that return text. Clear the cell contents or clean the data as appropriate; changing the fill color will not fix it.
The count changes when rows are added. A fixed range such as A2:A100 does not include new entries below row 100. Extend it, use a whole-column range such as A2:A, or put the data in a managed table-like layout and keep the formula’s range aligned with the data.
The result seems to count a header. Start at the first data row, not the column label. For a header in A1, A2:A is often a convenient range because it leaves the heading out.
The formula shows an error. Check the parentheses, quotation marks, and argument separator. Depending on locale settings, your sheet may require semicolons instead of commas between arguments.

Whole-column ranges are convenient, but they also ask Sheets to consider many cells. For a small or frequently recalculated workbook, a bounded range such as A2:A1000 can be easier to maintain and understand. Choose a range that gives room for growth without including unrelated content elsewhere in the column.

Choosing the right approach for a spreadsheet

For a simple list where each populated cell represents one record, COUNTIF with "<>" is clear and compact. For a range with formulas that may return blank text, compare it with COUNTA and COUNTBLANK using a few known examples. For data that may contain stray spaces, clean the source or use a text-specific cleanup formula rather than assuming every visually empty cell is empty.

It also helps to define what “not blank” means for the task. In an attendance sheet, a zero may mean an actual recorded score and should count. In a contact list, a space-only entry is probably not a valid name. In an intake form, a formula-generated empty string may mean “not answered yet,” even though the cell technically contains a formula. The right count follows the meaning of the data, not just the function name.

For a recurring report, document the range and the counting rule near the result. A note such as “Counts rows with a visible entry; ignores formula blanks” helps another person understand why the number may differ from COUNTA. If the spreadsheet is shared, a small test area containing one true blank, one zero, one space, and one formula blank can make the rule easy to verify after edits.

Frequently Asked Questions

What is the Google Sheets formula to count cells that are not blank?
Use =COUNTIF(A2:A100,"<>") and replace the range with yours. It counts cells matching the not-blank condition, including cells containing zero.

Does COUNTIF count zero as a nonblank cell?
Yes. Zero is a numeric value, so it is not blank. If you want to count only positive numbers, use a numeric condition such as ">0" instead.

How do I count nonblank cells in a row or another column?
Use the same formula with the range changed. For cells B2:F2, enter =COUNTIF(B2:F2,"<>"). For another column, replace the column letter in the range.

Does COUNTIF count cells with formulas that display nothing?
Formula cells can behave differently across counting functions: COUNTA counts a cell containing a formula, even if its displayed result is empty text, while COUNTBLANK recognizes formula results of an empty string as blank. Check COUNTIF on a sample if formula-generated blanks affect an important total.

How can I ignore cells that contain only spaces?
A space is content, so the basic nonblank criterion can count it. For a text range, =SUMPRODUCT(--(LEN(TRIM(A2:A100))>0)) counts entries that remain nonempty after trimming spaces. Clean the source data when possible, especially if it contains mixed types.

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.