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.
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.
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.
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.
For most everyday counting tasks: COUNT for numeric data, COUNTA for any cell that has data, and COUNTBLANK for empties. If you also need a condition — like "only sales above $100" — jump to COUNTIF or COUNTIFS next.
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.
Counts cells that hold an exact word or phrase. Not case-sensitive, so Yes and YES return the same count.
Greater than, less than, or not equal — every operator must be wrapped in double quotes.
Asterisk matches any characters. Great for partial matches in long descriptions or notes columns.
Build the criteria from another cell using the ampersand so the threshold updates dynamically.
Counts cells that have anything in them — equivalent to the non-empty count from COUNTA.
Counts cells whose text is a specific number of characters. Uses question-mark wildcards.
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.
Count orders placed in January 2026:
=COUNTIFS(A2:A10000, ">=2026-01-01", A2:A10000, "<=2026-01-31")
Wrap both date bounds in quotes and use the ISO format. Excel coerces these to real date numbers before comparing. You can also reference cells holding the bounds — that lets you make the range dynamic without rewriting the formula.
Count rows where the region is North AND the status is Open:
=COUNTIFS(B2:B500, "North", D2:D500, "Open")
Both conditions are checked on the same row. If row 7 has North in B7 but Closed in D7, it does not count. This is the bread-and-butter use of COUNTIFS.
Count cells in column A that contain "invoice" AND have a value above $500 in column B:
=COUNTIFS(A2:A500, "*invoice*", B2:B500, ">500")
Wildcards work in text criteria. They do not work on numeric ranges — for that you need SUMPRODUCT or a helper column.
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.
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.
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.
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.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.
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.
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.
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.
COUNT only counts cells holding numbers — text and blanks are ignored. COUNTA counts every non-empty cell regardless of data type. If your column is purely numeric, the two give the same answer. If it is mixed, COUNTA will be higher because it also includes the text cells.
Use COUNTIF: =COUNTIF(A1:A100, "Yes"). The match is not case-sensitive, but it must be exact. To match a partial string, use wildcards: =COUNTIF(A1:A100, "*report*") counts every cell containing the word "report" anywhere in the text.
Wrap the comparison in quotes: =COUNTIF(B1:B100, ">1000"). To compare against another cell, glue with an ampersand: =COUNTIF(B1:B100, ">"&C1). Forgetting the quotes around the operator is the most common cause of #NAME? errors with COUNTIF.
In Excel 365 use =COUNTA(UNIQUE(A1:A100)). In older versions use =SUMPRODUCT(1/COUNTIF(A1:A100, A1:A100)). If any cell in the range is blank, the SUMPRODUCT version divides by zero — guard against it with =SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100, A1:A100&"")).
The most common cause is mismatched range sizes. Every range in COUNTIFS must cover the same number of rows. =COUNTIFS(A1:A100, "x", B1:B99, "y") errors because A has 100 cells and B has 99. Make every range identical, or use full-column references (A:A, B:B) so the sizes always match.
COUNT, COUNTA, and COUNTIF include hidden rows. Use =SUBTOTAL(2, B2:B1000) for visible numeric cells, or =SUBTOTAL(3, B2:B1000) for visible non-empty cells. The function numbers 102 and 103 also ignore manually hidden rows in addition to filtered ones.
Use COUNTIF with the DATE function: =COUNTIF(A:A, DATE(2026,1,15)). For a date range, use COUNTIFS twice on the same column: =COUNTIFS(A:A, ">=2026-01-01", A:A, "<=2026-01-31") counts all January 2026 entries.
Yes. Select the range and check the bottom-right status bar in Excel. Right-click the status bar to enable Count (any non-empty cell) and Numerical Count (numbers only). This is perfect for one-off sanity checks where you do not need the result to live in a cell.