Excel COUNTIF: How to Count Non‑Blank Cells

What is COUNTIF and why count non‑blank cells?

COUNTIF is one of Excel’s most frequently used statistical functions. Its syntax is simple:

COUNTIF(range, criteria)

• range – the cells you want to evaluate.
• criteria – a condition expressed as a number, text, expression, or wildcard pattern.

The function scans every cell in range and returns the number of entries that satisfy criteria. Because it works on a single condition, COUNTIF is ideal for quick counts such as “how many sales are over $1,000?” or “how many rows contain the word Completed?”.

Why count non‑blank cells?

In many worksheets the real interest is the number of rows that actually contain data. Blank cells can appear for several reasons:

If you simply use COUNTA, which counts all non‑empty cells, you’ll also include cells that contain formulas returning an empty string (""). Those cells look blank but are not blank to COUNTA, leading to an over‑count.

COUNTIF solves this by allowing a criteria that explicitly excludes blanks:

=COUNTIF(A2:A100, "<>")

The "<>" operator means “not equal to” and, when used alone, translates to “not blank”. It treats cells that display "" as blank, making it the preferred tool for “non‑blank” counts in both Excel and Google Sheets (the same syntax works in Sheets). This precision is essential when you need accurate denominators for ratios, percentages, or conditional‑formatting rules that depend on the presence of real data.

Basic COUNTIF syntax for non‑blank cells

To count any cell that contains a value (numbers, text, dates, errors) while ignoring truly empty cells, use:

=COUNTIF(range, "<>")

• range – the set of cells you want to evaluate (e.g., A2:A100).
• "<>" – the comparison operator “not equal to” combined with a blank string, i.e., “not empty”.

How it works – sample data

Formula

=COUNTIF(A2:A6, "<>")

Result: 3 (cells A2, A4, and A5 contain values; A3 and A6 are empty).

Quick checklist

Excel vs. Google Sheets

Both applications accept the same syntax ("<>"). The only practical difference is that Google Sheets automatically expands whole‑column references (e.g., A:A), while older desktop versions of Excel may require a specific row range (e.g., A2:A1000) to avoid performance hits.

Common pitfalls

• Hidden characters: Cells that appear blank but contain a space or a formula returning "" are not considered blank, so they will be counted.
• Multiple, non‑adjacent ranges: Combine COUNTIF with SUM (e.g., =SUM(COUNTIF(A2:A6,"<>"),COUNTIF(C2:C6,"<>"))).

Using NOT and ISBLANK for more flexibility

When you need extra control—such as adding another condition or handling errors—pair NOT with ISBLANK inside SUMPRODUCT.

Sample data

Results

Both formulas ignore the three empty cells and count everything else, including numbers, text, formulas that return a value, and the zero (0) which is not considered blank.

When to prefer NOT(ISBLANK(...))

• Multiple criteria – you can nest extra tests, e.g., count non‑blank cells that also contain “Apple”:

excel =SUMPRODUCT(--NOT(ISBLANK(A2:A10)), --(A2:A10="Apple"))

• Dynamic ranges with possible errors – wrap the range in IFERROR to keep the formula from failing:

excel =SUMPRODUCT(--NOT(ISBLANK(IFERROR(A2:A10,""))))

Practical examples with sample data

The following scenarios use the same table, which works in Excel 2016 + or 365 and in Google Sheets.

1. Simple count of non‑blank cells in a single column

Goal: How many products are listed in column A?

=COUNTIF(A2:A7, "<>")

Result: 5 (Apple, Banana, Cherry, Fig, Grape)

2. Count rows where both columns have data

Goal: Count only the rows that have a product name and a sales figure.

=COUNTIFS(A2:A7, "<>", C2:C7, "<>")

Result: 3 (Apple‑120, Cherry‑85, Grape‑200)

3. Count non‑blank cells across multiple columns (total entries)

Goal: Find the total number of filled cells in columns A and C combined.

=COUNTIF(A2:A7, "<>") + COUNTIF(C2:C7, "<>")

Result: 8 (5 product names + 3 sales numbers)

4. Using COUNTA as an alternative

COUNTA counts every non‑empty cell, including those that contain formulas returning "". In this clean dataset it returns the same value as the first example:

=COUNTA(A2:A7)

Result: 5

Tips for handling errors and edge cases

Quick‑fix formulas you can copy‑paste

• Basic non‑blank count (Excel & Google Sheets)

excel =COUNTIF(A2:A100, "<>")

• Ignore cells that only contain spaces or empty strings

excel =SUMPRODUCT(--(LEN(TRIM(A2:A100))>0))

• Non‑blank count with an additional condition (e.g., value > 0)

excel =COUNTIFS(A2:A100, "<>", A2:A100, ">0")

FAQ

How does COUNTIF differ from COUNTA?

COUNTIF counts cells that meet a specific condition; you can tell it to ignore blanks ("<>"). COUNTA simply counts every cell that is not truly empty, including cells that contain formulas returning "", spaces, or error values. Use COUNTIF when you need to exclude visual blanks; use COUNTA when you want every non‑empty entry regardless of its content.

Can I count non‑blank cells with multiple conditions?

Yes. Use COUNTIFS, which accepts a pair of range/criteria arguments for each condition. For example, =COUNTIFS(A2:A100,"<>",B2:B100,">0") counts rows where column A is not blank and column B is greater than zero.

What if I want to ignore cells that contain only spaces?

Spaces are characters, so COUNTIF(range,"<>") will count them. To exclude such cells, trim the content and test the length:

=SUMPRODUCT(--(LEN(TRIM(A2:A100))>0))

The TRIM removes leading/trailing spaces, and LEN>0 ensures only cells with visible characters are counted.

All formulas shown are syntactically valid in the indicated versions of Excel and in Google Sheets.