Google Sheets COUNTIFS: Count Nonblank Cells Correctly

To count nonblank cells in Google Sheets, use =COUNTIFS(A2:A100,"<>"), replacing A2:A100 with your range. The criterion “<>” means “not equal to blank”; you can add more range-and-criterion pairs when the count also needs to meet other conditions.

The basic COUNTIFS formula

COUNTIFS checks one or more ranges against matching criteria and counts the rows where all the criteria are met. Although COUNTIF is enough for a single condition, COUNTIFS works with one condition too and makes it easy to extend the formula later. The comparison criterion must be written as text in quotation marks: “<>”.

For an example, assume A2:A8 contains the values Maya, an empty cell, 14, a formula that displays an empty string, Luis, a cell containing a space, and Priya. With those entries in A2:A8, =COUNTIFS(A2:A8,"<>") returns 6: the empty cell is excluded, while the formula cell and the cell containing a space are counted as nonblank. This is often what you want when “nonblank” means a cell contains something, rather than when it merely looks like it contains something.

Use a bounded range such as A2:A100 when practical. It avoids counting a heading in row 1 and makes the formula’s intended data area clear. If new records will be added, expand the endpoint as needed or use a suitable open-ended range, such as A2:A.

COUNTIFS versus COUNTIF

Both functions can count nonblank cells. COUNTIF takes one range and one criterion; COUNTIFS accepts pairs of ranges and criteria, making it useful when a record must satisfy several tests at once. For a simple nonblank count, there is no need to prefer one on mathematical grounds. If your formula already uses COUNTIFS for other conditions, adding the nonblank test there keeps the logic together.

Assume A2:A20 contains names and you only want to count rows where the name is present. Either function can do that: with names in A2:A20, =COUNTIF(A2:A20,"<>") counts the nonblank entries. If the range contains 12 entries and 3 truly empty cells, the result is 9, including any cell with a formula returning an empty string.

COUNTIFS is particularly helpful in reports: for example, count orders with an order ID and a particular status. Each additional criterion narrows the result; a row must meet every criterion, not just one of them. All criteria ranges should cover corresponding rows and have the same dimensions. Mixing a two-row range with a full-column range can produce an error or unintended results.

What Google Sheets treats as nonblank

A cell can look empty without actually being empty. COUNTIFS with “<>” counts ordinary text, numbers, dates, and other entered values. It can also count a formula cell whose displayed result is an empty string, such as a formula using IF to show nothing until another cell is filled. That distinction explains why a nonblank count can be larger than the number of visibly filled cells.

Whitespace is another frequent source of surprises. A cell containing one or more spaces is not empty, even though it may look blank on the sheet. The same general issue applies to text with leading or trailing spaces: it is still content. If imported data contains spaces or formulas that return empty text, first decide what your report means by “blank.” It might mean genuinely empty, visually empty, or containing no meaningful characters; those definitions can produce different totals.

For a count that excludes formula results of “” as well as truly empty cells, use LEN to check whether the displayed value has any characters. Assume the values to inspect are in A2:A100; =SUMPRODUCT(--(LEN(A2:A100)>0)) counts cells whose evaluated value has a length greater than zero. If the sample range contains four names, one number, one formula returning “”, and one truly empty cell, the result is 5. A cell containing a space still has a character and is counted, so this formula does not automatically treat whitespace as empty.

Adding more conditions

To count nonblank cells only when a related field meets a condition, add another range-and-criterion pair to COUNTIFS. For example, assume A2:A100 contains task names and B2:B100 contains task statuses. To count rows with a task name and the status Open, use =COUNTIFS(A2:A100,"<>",B2:B100,"Open"). If four rows have status Open but one of those rows has no task name, the result is 3.

Criteria are evaluated for the same row across all the ranges. COUNTIFS does not count a nonblank name from one row and an Open status from another; both tests must be true on the same row. This makes the function suitable for tables where each row represents one record.

You can also combine a nonblank test with numeric or date conditions. Assume A2:A100 contains customer names and C2:C100 contains amounts. To count named records with an amount greater than 100, use =COUNTIFS(A2:A100,"<>",C2:C100,">100"). If three rows meet both tests, the result is 3. For dates, use a cell reference to avoid typing a date in a locale-dependent format: with names in A2:A100, dates in C2:C100, and the cutoff date in E1, =COUNTIFS(A2:A100,"<>",C2:C100,">="&E1) counts named records dated on or after E1.

Criteria details and practical variations

The operators in a COUNTIFS criterion are text, so quotation marks matter. A criterion such as “>100” combines an operator and a value inside quotes. When the value comes from a cell, join the operator text to the reference with the ampersand, as in “>=”&E1. The criterion “<>” is a compact way to ask for values other than blank.

Use “<>” when you want to count cells that contain something, including numbers and dates.
Use a specific criterion such as “Open” when only an exact text value should qualify.
Use an operator criterion such as “>0” when a numeric threshold is part of the rule.
Use a cell reference joined to an operator when the cutoff may change.

Be careful not to substitute a text-only wildcard count without considering the data. For example, COUNTIF with the criterion “?*” is commonly used to find text strings with at least one character, but it does not serve as a general count of all nonblank values when numbers and dates must be included. Choose the criterion to match the types of data in the range.

Common problems and how to diagnose them

If the result seems too high, check for formulas returning empty strings and cells containing spaces. These may appear blank but still satisfy the nonblank test. Inspect a few cells from the range, including cells populated by formulas and imported data, rather than relying only on what is visible. If the result seems too low, verify that the formula covers all data rows and that any additional criteria match the actual spelling, spacing, or value type in the sheet.

For a COUNTIFS formula with several tests, check that each range starts and ends on the same rows. For example, A2:A100 and B2:B100 align; A2:A100 and B3:B101 do not describe the same records. Also confirm that the function has a range followed by a criterion for every condition. COUNTIFS uses pairs, so a missing criterion or an extra range can make the formula invalid.

Locale settings can affect formula separators. Most examples here use commas between arguments. If your spreadsheet’s locale expects semicolons, Sheets may require semicolons in place of commas. Google Sheets is a browser-based spreadsheet, so menu paths and formula behavior described here refer to Sheets, not Microsoft Excel. Excel also has COUNTIFS, but do not assume every detail of a formula or locale setting transfers unchanged between programs.

Choosing the right approach

Use COUNTIFS with “<>” when you need a straightforward count of cells that contain something, or when the count must also satisfy other row-based criteria. Use the LEN and SUMPRODUCT approach when formula-generated empty strings must be excluded from the count. If spaces should count as blank too, you need to account for whitespace separately; counting the length of the raw cell value will include a space.

Before sharing a report, define the intended meaning of “not blank” and test a small range containing representative cases: a true empty cell, a normal text value, a number, a formula returning “”, and a space. This quick check prevents a technically correct formula from answering a different question than the one your report asks. For a one-time check, it can also be useful to filter the data and compare the visible records with the formula’s result.

Frequently Asked Questions

What is the formula to count nonblank cells in Google Sheets?
Use =COUNTIFS(A2:A100,"<>") and replace A2:A100 with your range. It counts cells that are not empty according to the criterion, including cells containing values or formulas.

Can COUNTIFS count nonblank cells and match a second condition?
Yes. Add another range and criterion pair. For example, with names in A2:A100 and statuses in B2:B100, =COUNTIFS(A2:A100,"<>",B2:B100,"Open") counts rows where the name is nonblank and the status is Open.

Does COUNTIFS count a formula that displays a blank?
It can. A formula returning an empty string may look blank but is still present in the cell, so the “<>” test may include it. If you want to exclude those results, count values with a positive LEN using SUMPRODUCT instead.

How do I count nonblank cells while ignoring spaces?
The basic “<>” test treats a space as content. First clean or normalize the source data if whitespace should not count, or use a whitespace-aware calculation designed for your data. Check whether cells contain ordinary spaces or other whitespace characters before relying on a cleanup rule.

Why does my COUNTIFS formula return an unexpected result?
Look for formula-generated blanks, spaces, shifted or mismatched ranges, and extra conditions that do not match the data exactly. Also check whether your spreadsheet locale expects commas or semicolons between formula arguments.

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.