How to Count Cells in Excel

Counting cells in Excel sounds like one of those one-click jobs — until you actually try it. You highlight a column expecting 500, and Excel quietly returns 312. Or you ask for a count of "sales above $1,000" and the formula refuses to budge from zero. The trouble is, Excel does not have one count function. It has six common ones, plus a status bar trick, plus a few clever tricks with SUMPRODUCT when nothing else fits.

Each method answers a slightly different question. COUNT only tallies numbers. COUNTA does not care what the cell holds, as long as it is not empty. COUNTBLANK does the opposite. COUNTIF lets you filter on a single condition. COUNTIFS chains several conditions. SUBTOTAL and AGGREGATE respect filters and hidden rows. Pick the wrong one, and the result is off by hundreds without any warning.

This guide walks every method you need — short examples, the syntax, the gotchas, and a few old-school tricks that still beat the new dynamic-array functions in certain cases. You will leave knowing exactly which formula to reach for, whether you are counting blanks, unique values, dates, partial text matches, or filtered rows. We will also cover the errors that catch everyone out, like #NAME? from missing quotes and the silent mismatched-range bug that returns wrong answers without ever flashing red.

If you want the fast version: COUNT for numbers, COUNTA for any non-empty cell, COUNTIF for one rule, COUNTIFS for many. Click the status bar selection for a no-formula count. Everything else in this article is the long version — and the long version is where the wrong answers come from. Let us start with the simplest one and work up.

Before any of that, a quick note on why this matters. Counting feels trivial, but downstream calculations lean on it. If your record count is off by one, your average drifts by a fraction of a percent and nobody notices. If it is off by ten percent, your dashboard suddenly disagrees with the source data and you spend an afternoon hunting ghosts.

Getting the count right at the source — with the function that matches your actual question — saves real time. So does writing the formula in a way that survives next month, when somebody pastes new data into the sheet and the ranges shift.

1. COUNT — numbers only

The COUNT function in Excel is the strict one. It counts cells that hold a numeric value: integers, decimals, currency, percentages, dates, and times all qualify, because dates and times are stored internally as numbers. Anything else — text, blanks, errors, TRUE/FALSE — is skipped without comment.

The syntax is =COUNT(value1, [value2], …). You can pass single cells, ranges, named ranges, or up to 255 arguments separated by commas. =COUNT(A1:A500) returns how many of those 500 cells contain numbers. If 312 are numeric and the rest are headers, notes, or blanks, the answer is 312. No error, no warning — Excel just hands you the number.

This is the formula to use when you need a sanity check before summing or averaging a column. If the count looks low, you have got stray text mixed into your data, and any aggregation downstream is going to be wrong. Catch it here.

One quirk worth knowing: COUNT also accepts arrays. =COUNT({1, 2, "three", 4}) returns 3, because it ignores the string. That makes COUNT useful inside larger array formulas where some elements may be text and some numeric. You can also pass formula results directly: =COUNT(IF(A1:A100>100, A1:A100)) as a Ctrl+Shift+Enter formula counts only values above 100. In Excel 365 the IF spills naturally and you do not need the array entry — the same formula just works.

2. COUNTA — any non-empty cell

The COUNTA function in Excel answers a different question: how many cells are not empty? Numbers, text, dates, errors, even formulas that return an empty string (="") all count. The only thing COUNTA skips is a truly empty cell — one you have never touched, or one that has been cleared with the Delete key.

Use COUNTA when your data is mixed and you just want to know how many rows have anything in them. =COUNTA(B2:B1000) tells you how many of those 999 cells have a value, regardless of whether it is a name, a date, or a number. This is the right tool for counting filled rows in a contact list, a survey response sheet, or any roster with mixed content.

3. COUNTBLANK — the empties

COUNTBLANK is COUNTA's mirror. It counts only empty cells in a range. =COUNTBLANK(A1:A500) returns how many of those 500 cells have nothing in them. A subtle catch: cells holding ="" (a formula returning an empty string) are treated as blank by COUNTBLANK but as non-empty by COUNTA. Two functions, two answers, same cell. Pick the one that matches your definition of "blank."

Why does this matter in practice? Imagine you have a survey response sheet with 500 rows, and row 47 holds =IF(B47="", "", B47) — a formula that looks blank when B47 is empty. COUNTA returns 500, because every row has a formula. COUNTBLANK returns however many rows actually show nothing on screen. If you build your dashboard on COUNTA, you over-report response rates. If you build it on COUNTBLANK to find missing answers, the formula does its job correctly. Two functions, two truths — both are right, they just answer different questions.

4. COUNTIF — one condition

COUNTIF is where Excel starts to feel useful. The syntax is =COUNTIF(range, criteria). You give it a range and a rule, and it counts every cell that matches the rule. The criteria can be a number, a text string, a cell reference, or a comparison operator wrapped in quotes.

A few examples make this concrete:

• =COUNTIF(A1:A100, "Yes") counts every cell that holds the exact word Yes.
• =COUNTIF(B1:B500, ">1000") counts every cell with a value greater than 1,000.
• =COUNTIF(C1:C100, "<>"&"Closed") counts every cell that is not the word Closed.
• =COUNTIF(D1:D200, A1) counts how many cells in D match whatever is in A1.

The countif function in Excel is also wildcard-friendly. An asterisk matches any number of characters and a question mark matches exactly one. =COUNTIF(A:A, "*report*") counts every cell containing the word report anywhere inside the text. =COUNTIF(A:A, "J??n") counts four-letter strings starting with J and ending in n (Jean, John, etc.).

Watch the quotes. ">100" works; >100 without quotes triggers #NAME?. And when you compare against a cell, the operator stays in quotes but the cell reference does not: ">"&A1 — the ampersand glues the two together.

One more wrinkle: COUNTIF is not case-sensitive. =COUNTIF(A1:A100, "yes") and =COUNTIF(A1:A100, "YES") return the same answer. If you genuinely need a case-sensitive count, COUNTIF cannot help — fall back to SUMPRODUCT with EXACT: =SUMPRODUCT(--EXACT(A1:A100, "Yes")). EXACT performs a case-sensitive comparison for each cell, and the double-unary converts the TRUE/FALSE array to 1s and 0s for summing.

5. COUNTIFS — multiple conditions

When one filter is not enough, the countifs function in Excel chains them together. Syntax: =COUNTIFS(range1, criteria1, range2, criteria2, …). Each range/criteria pair must be paired up correctly, and every range must be the same shape and size — this is the single biggest source of silent bugs with this function.

Say column A holds region names and column B holds sales values. To count sales above $1,000 in the North region: =COUNTIFS(A2:A1000, "North", B2:B1000, ">1000"). Excel scans row by row, checks both conditions on the same row, and counts the row only if both pass. It is an AND across all your criteria — there is no OR built into COUNTIFS.

To do an OR — count sales in North or South — add two COUNTIFS together: =COUNTIFS(A:A,"North",B:B,">1000") + COUNTIFS(A:A,"South",B:B,">1000"). Clumsy but reliable. The newer alternative uses SUMPRODUCT, which we cover next.

The most common COUNTIFS mistake: ranges of different sizes. A2:A1000 paired with B2:B999 gives #VALUE!. Pick a stop row and stick with it across all ranges, or use full-column references like A:A and B:B.

6. SUMPRODUCT — the power-user fallback

SUMPRODUCT looks like a multiplication tool, but it is the old-school workhorse for counting with conditions COUNTIF cannot handle. The trick: feed it a boolean array, and TRUE/FALSE coerce to 1/0 when you multiply or use a double-unary (--) operator.

Count cells in A1:A100 where the value is greater than the value in B1:B100, row by row:

=SUMPRODUCT(--(A1:A100>B1:B100))

COUNTIF cannot do that — it can only compare to one fixed value or one cell. SUMPRODUCT happily compares two arrays element by element.

Another classic: count unique values. =SUMPRODUCT(1/COUNTIF(A1:A100, A1:A100)) returns the number of distinct entries. The trick is that COUNTIF returns how many times each value appears, and dividing 1 by that gives a fraction that sums to 1 per distinct value. In Excel 365 you can simplify to =COUNTA(UNIQUE(A1:A100)) — same answer, much faster.

SUMPRODUCT also handles OR conditions cleanly: =SUMPRODUCT((A1:A100="North") + (A1:A100="South")) counts cells matching either value. Add together two arrays of TRUE/FALSE, and matches in either array count once.

7. SUBTOTAL and AGGREGATE — filtered counts

Here is a scenario every analyst hits: you filter a table to show only one region, then write =COUNT(B2:B1000) below it. The result includes the hidden rows. COUNT, COUNTA, COUNTIF — none of them respect filters. They count everything in the range whether the row is visible or not.

SUBTOTAL fixes this. =SUBTOTAL(2, B2:B1000) counts numbers only in visible rows. The first argument tells SUBTOTAL which operation to perform: 2 for COUNT, 3 for COUNTA, 9 for SUM. Add 100 to the function number (102, 103, 109) to also ignore manually hidden rows, not just filtered ones.

AGGREGATE is SUBTOTAL on steroids — it ignores errors as well as hidden rows. =AGGREGATE(2, 5, B2:B1000) returns a count that skips errors and hidden rows in one go. The 5 in the second argument is the option for "ignore hidden rows and errors." Handy when your source data is messy.

8. The status bar — no formula needed

Select any range and look at the bottom-right corner of the Excel window. Right-click the status bar and you can toggle Count (counts all non-empty cells, like COUNTA), Numerical Count (numbers only, like COUNT), Sum, Average, Min, and Max. Quick checks live here. No formula, no cell consumed — just select and read.

9. Counting unique values

Three options, oldest to newest:

• =SUMPRODUCT(1/COUNTIF(A1:A100, A1:A100)) — works in every Excel version, slow on big ranges, breaks if any cell is blank (divide by zero).
• =SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100, A1:A100&"")) — handles blanks safely.
• =COUNTA(UNIQUE(A1:A100)) — Excel 365 / 2024 only, fastest and cleanest.

10. Text vs numeric — the silent killer

One trap that catches everyone: numbers stored as text. They look like numbers, they show up in cells like numbers, but Excel treats them as strings. COUNT skips them. COUNTIF with ">500" skips them too. The fix is to convert the column with Text to Columns, or wrap the formula in VALUE(), or use a helper column.

Quick test: select the range and look at the status bar. If Numerical Count is lower than Count, you have got text-formatted numbers hiding in there. Excel count cells with text formulas use COUNTIF with a wildcard: =COUNTIF(A:A, "*") counts every cell containing any text. Use that to verify.

11. Counting dates and patterns

Dates are numbers under the hood, so COUNT counts them. To count cells matching a specific date: =COUNTIF(A:A, DATE(2026,1,15)). To count cells in a date range, use COUNTIFS with two conditions on the same column (one for the start, one for the end).

Wildcards are limited to text. =COUNTIF(A:A, "*2026*") only works if column A is formatted as text. If A holds real dates, you need to convert with TEXT() first, or use SUMPRODUCT: =SUMPRODUCT(--(YEAR(A1:A1000)=2026)) counts every cell whose year part is 2026.

12. Common errors and how to fix them

Three errors come up again and again. #NAME? almost always means a missing or stray quote — Excel cannot parse the criteria. #VALUE! from COUNTIFS means your ranges are different sizes. And the silent killer: a formula that returns 0 when you expected 20. That one usually points to a hidden trailing space in your criteria text or a number stored as text mismatch. Use TRIM() and VALUE() liberally when troubleshooting.

13. Which one should you actually use?

Match the function to the question, not the other way around. How many numbers are in this column? COUNT. How many rows have any data? COUNTA. How many cells are empty? COUNTBLANK. How many match this one rule? COUNTIF. How many match these several rules together? COUNTIFS. How many unique values? SUMPRODUCT or UNIQUE in Excel 365. How many in the filtered view? SUBTOTAL or AGGREGATE. Each one has its niche, and once you have used them a few times the choice becomes automatic.

One last thing worth remembering: the easiest count is the one you do not write a formula for. If you only need the number once and you are not building a dashboard, just select the range and read the status bar. It saves a cell, it saves a calc cycle, and it is one click. Formulas are for results that need to live in the workbook — for everything else, the status bar wins.

Want to put this to work? Open a sheet, drop in 100 mixed cells — some numbers, some text, some blanks, some dates — and run all six functions side by side. The differences become obvious in about thirty seconds, and you will remember them for good. Practice is the part that sticks; reading about Excel is the part that fades by Friday.