COUNTIF vs COUNTIFS vs COUNTA — counting correctly in Excel
Excel has SIX different COUNT functions and half of them are confusable with each other. COUNTIF counts cells matching one condition. COUNTIFS counts cells matching multiple conditions. COUNTA counts non-blank cells regardless of content. Three functions, three completely different jobs — and using the wrong one gives you the wrong answer without any error message. This is the definitive guide to counting in Excel with a decision framework that respects the entire COUNT family.
⚡ The short answer
Use COUNTIF when counting cells that match exactly one condition. Use COUNTIFS when counting cells that match two or more conditions with AND logic. Use COUNTA when counting all non-empty cells regardless of content — great for counting how many rows have any data. These functions do fundamentally different jobs — you don't pick one over the others, you pick the right one for the question you're asking.
The confusion this article solves
The COUNT family is one of Excel's most-confused function groups because the names sound similar. Beginners often reach for COUNTIF when they need COUNTA (or vice versa) and get answers that look plausible but are completely wrong.
Here's the fundamental question each function answers:
COUNTIF asks: "How many cells match this ONE condition?"
COUNTIFS asks: "How many cells match ALL these conditions?"
COUNTA asks: "How many cells have any content at all?"
These are three genuinely different questions. Unlike the aggregation pair SUMIF vs SUMIFS where you can substitute one for the other in single-criterion cases, COUNTA can never replace COUNTIF and vice versa — they measure different things.
The three approaches at a glance
COUNTIF
- Single condition only
- 2 arguments — range + criterion
- Supports wildcards (* and ?)
- Supports operators (>, <, <>)
- Case-insensitive by default
- Works in every Excel version
COUNTIFS
- Multiple conditions with AND logic
- Up to 127 range/criterion pairs
- Same wildcard/operator support
- All conditions must be TRUE
- Ranges must be same size
- Works in every Excel version
COUNTA
- Counts all non-empty cells
- Just 1 argument — the range
- No conditions or criteria
- Counts text, numbers, dates, errors
- Includes formulas returning ""
- Works in every Excel version
Three different questions, three different formulas
Say you have this order data in A1:C11:
| Region | Product | Amount |
|---|---|---|
| APAC | Widget A | 1,200 |
| EU | Widget A | 950 |
| APAC | Widget B | 2,100 |
| US | Widget A | 3,400 |
| EU | 1,750 | |
| APAC | Widget A | 890 |
| US | Widget B | 2,500 |
| EU | Widget A | 1,100 |
| US | Widget C | 3,200 |
| APAC | Widget C | 1,650 |
Note row 5 has a blank Product cell. Watch how each function handles the same underlying data differently.
Question 1: "How many orders came from APAC?" (one condition)
Returns 4 — the four APAC rows.
Question 2: "How many APAC orders were for Widget A?" (two conditions)
Returns 2 — APAC + Widget A rows (rows 1 and 6).
Question 3: "How many rows have a product name entered?" (any content)
Returns 9 — all rows except row 5 (which has a blank Product cell).
Three questions. Three different functions. Three different answers. Each function is right for its question — and dangerous for any other question. Trying to use COUNTA to count APAC orders would return 10 (all cells in the region column have values). Trying to use COUNTIF to count all orders would require a workaround like COUNTIF(range, "*") which is fragile.
Feature-by-feature scorecard
| Feature | COUNTIF | COUNTIFS | COUNTA |
|---|---|---|---|
| Purpose | Count with 1 condition | Count with N conditions | Count non-empty cells |
| Arguments | 2 (range, criterion) | 2+ (range, criterion pairs) | 1+ (range or ranges) |
| AND logic (multiple criteria) | No | Yes, native | No |
| OR logic | Add COUNTIFs | Add COUNTIFs | Not applicable |
| Wildcards (* and ?) | Yes | Yes | No (counts everything) |
| Comparison operators (>,<,<>) | Yes | Yes | No |
| Counts blank cells | No | No | No (that's COUNTBLANK's job) |
| Counts text cells | If criteria matches | If criteria matches | Always (any content) |
| Counts numeric cells | If criteria matches | If criteria matches | Always |
| Counts error cells | Only #N/A exactly | Only #N/A exactly | Yes, all errors |
| Counts formula returning "" | No | No | Yes (that's a common gotcha) |
| Excel version support | Excel 2007+ | Excel 2007+ | Excel 5 (1993)+ |
| Google Sheets support | Yes | Yes | Yes |
| Speed on large datasets | Fast | Fast (marginally slower with more criteria) | Very fast |
The complete COUNT family — all 6 functions
COUNTIF, COUNTIFS, and COUNTA are the three most-searched, but Excel has six count functions total. Understanding when each applies is what separates competent users from beginners:
| Function | What it counts | Common use |
|---|---|---|
COUNT |
Cells with NUMBERS only | How many rows have valid numeric data |
COUNTA |
Cells with ANY content | How many rows have any data entered |
COUNTBLANK |
Truly empty cells only | How many cells still need input |
COUNTIF |
Cells matching 1 criterion | How many orders are from APAC |
COUNTIFS |
Cells matching multiple criteria (AND) | How many APAC orders over $1000 |
DCOUNT / DCOUNTA |
Database-style count with criteria range | Legacy — use COUNTIFS instead |
COUNT only counts NUMBERS. COUNTA counts ANYTHING non-empty. If you have a column of names and use COUNT, you get 0 — because names aren't numbers. Use COUNTA to count text values. This single distinction confuses more Excel beginners than any other counting question.
Where COUNTA becomes tricky
COUNTA counts EVERY non-empty cell — and "non-empty" has a subtle definition that catches many users.
The formula-returning-empty-string gotcha
If a cell contains a formula like =IF(A1>10, "High", ""), and the condition returns "", the cell LOOKS empty but COUNTA counts it as non-empty.
This might return 100 even though only 30 cells appear to have visible data — because the formulas returning "" still count as non-empty.
Fix: use COUNTIF with a specific criterion to exclude blank strings:
The ?* wildcard means "one character followed by any characters" — so it counts only cells with at least one visible character.
COUNTA vs COUNTBLANK don't always add up
Intuition says COUNTA + COUNTBLANK should equal the total cell count. It usually doesn't, because formulas returning "" count as non-blank by COUNTA and as blank by COUNTBLANK — the same cell is counted in both. Excel's counting definitions have overlapping edges.
Where COUNTIFS decisively wins
1. Multiple conditions
The obvious case. Only COUNTIFS handles this natively:
Three conditions in one function. Trying to do this with COUNTIF requires the awkward SUMPRODUCT workaround.
2. Date range counting
Count orders in Q3 2026:
Two conditions on the same column create a range filter. COUNTIF can only do one bound.
3. Cross-column combinations
Count rows where Region is APAC AND Product is not Widget A:
The <> not-equal operator combined with AND logic. Essential for exclusion counting.
The professional pattern — use COUNTIFS exclusively
Following the same logic as SUMIF vs SUMIFS, many Excel professionals standardize on COUNTIFS for all counting-with-criteria tasks, even when only one criterion is needed.
Reasons:
Consistency. Every counting formula in the workbook follows the same pattern.
Future-proof. Adding a second criterion later is trivial with COUNTIFS.
No argument-order confusion. COUNTIFS is always range-criterion-range-criterion. COUNTIF has the same order but different structure.
Compare counting APAC orders both ways:
COUNTIFS is exactly 1 character longer. That's the cost of never needing to remember which function to use.
Counting unique values — the modern way
None of these functions count unique values directly. In older Excel, the classic pattern is:
Cryptic and array-formula-based. In Excel 365 or 2021, the modern replacement:
Extract unique values first with UNIQUE, then count them with COUNTA. Cleaner, faster, easier to understand.
For unique values matching a condition:
Combines FILTER, UNIQUE, and COUNTA into one modern one-liner. See FILTER vs AutoFilter for more on FILTER patterns.
The decision framework
Which counting function should you use?
- Count non-empty cells in a range → COUNTA
- Count only numeric cells → COUNT
- Count truly empty cells → COUNTBLANK
- Count cells matching one text/value → COUNTIF
- Count cells above/below a threshold → COUNTIF (with comparison operator)
- Count with multiple conditions (AND) → COUNTIFS
- Count date ranges (between two dates) → COUNTIFS (two conditions on same column)
- Count with OR logic → Add multiple COUNTIFs together
- Count unique values → COUNTA(UNIQUE(range)) in modern Excel
- Any workbook you'll maintain long-term → COUNTIFS exclusively for criteria counting
Common mistakes to avoid
1. Using COUNT when you need COUNTA
The most common counting bug. COUNT on a column of names returns 0 because names aren't numbers. Use COUNTA to count text values.
2. COUNTIFS with mismatched range sizes
All ranges in COUNTIFS must be the same size. Using A1:A100 for one criterion and B1:B50 for another returns #VALUE!. Always use ranges of identical dimensions.
3. Trying to count OR logic with COUNTIFS
COUNTIFS is AND-only. To count APAC OR EU orders, you need:
Not one COUNTIFS. If the conditions could overlap, subtract the overlap to avoid double-counting.
4. Forgetting COUNTA counts formulas returning ""
Cells with =IF(condition, "value", "") look empty but COUNTA counts them. Use =COUNTIF(range, "?*") to count only cells with visible content.
5. Using SUMPRODUCT tricks when UNIQUE exists
The old SUMPRODUCT(1/COUNTIF(...)) pattern for counting unique values is obsolete in modern Excel. Use COUNTA(UNIQUE(range)) instead — dramatically clearer.
Wildcards and operators — the shared syntax
COUNTIF and COUNTIFS share the same criteria syntax as SUMIF/SUMIFS and AVERAGEIFS. Once you know the pattern, it works across the entire IFS family:
| Criteria pattern | Example | What it counts |
|---|---|---|
| Exact text | "APAC" | Cells equal to "APAC" (case-insensitive) |
| Number | 1000 | Cells equal to 1000 |
| Greater than | ">1000" | Cells greater than 1000 |
| Not equal | "<>APAC" | Cells not equal to "APAC" |
| Starts with | "Wid*" | Cells starting with "Wid" |
| Contains | "*Widget*" | Cells containing "Widget" |
| Non-empty | "?*" | Cells with at least one character |
| Cell reference | D1 | Matches value in D1 |
| Cell + operator | ">"&D1 | Greater than value in D1 |
The IFS family's criteria syntax is one of Excel's most powerful and least-taught features. Master it once, apply it everywhere.
🎉 You just finished the comparison series
This is post 10 of 10 in our Excel comparison series — the definitive resource for choosing the right function or approach for any spreadsheet decision. Each post cross-references the others to build a complete decision-making framework.
The complete series:
- XLOOKUP vs VLOOKUP — the modern lookup upgrade
- IFS vs Nested IF — cleaner logic branching
- Excel vs Google Sheets — platform decision
- SUMIF vs SUMIFS — aggregation basics
- IFERROR vs IFNA — error wrapper choice
- INDEX/MATCH vs XLOOKUP — power user's dilemma
- Pivot Tables vs Formulas — approach decision
- FILTER vs AutoFilter — modern vs classic filtering
- CONCATENATE vs TEXTJOIN vs & — text joining
- COUNTIF vs COUNTIFS vs COUNTA — this article
Related resources
Master aggregation formulas
Full coverage of the entire IFS family — SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, MINIFS — in the function library.
Browse Statistical Functions → Browse Math Functions →Frequently asked questions
What is the difference between COUNTIF and COUNTIFS?
COUNTIF counts cells that meet ONE criterion — for example, count how many orders came from APAC. COUNTIFS counts cells that meet MULTIPLE criteria simultaneously — for example, count orders from APAC AND for Widget A AND over $1000. The S at the end signals plural conditions. COUNTIFS uses AND logic where all criteria must match for a row to be counted.
What does COUNTA count?
COUNTA counts all non-empty cells in a range, regardless of what they contain — numbers, text, dates, errors, or even blank strings from formulas. Unlike COUNT which only counts numeric cells, COUNTA includes everything except truly empty cells. Use COUNTA to check how many rows have any data, regardless of type.
Should I use COUNTIF or COUNTIFS?
Use COUNTIF when you have exactly one criterion. Use COUNTIFS when you have two or more criteria. Many Excel professionals use COUNTIFS exclusively even for single conditions because it maintains consistent argument order across the workbook and future-proofs the formula in case additional criteria are needed later. See SUMIF vs SUMIFS for the parallel logic on the summing side.
What is the difference between COUNT and COUNTA?
COUNT only counts cells containing numbers, ignoring text, blanks, and errors. COUNTA counts all non-empty cells regardless of content type — numbers, text, dates, formulas, everything except truly blank cells. If you want to count how many customers have entered any data, use COUNTA. If you want to count how many rows have valid numeric values, use COUNT.
Can COUNTIFS use OR logic?
No, COUNTIFS uses AND logic exclusively — every criterion must match for a row to be counted. For OR logic, add multiple COUNTIFS formulas together, then subtract any COUNTIFS that counts rows matching both conditions to avoid double-counting. Alternatively, use SUMPRODUCT for more complex mixed logic conditions.
How do I count unique values in Excel?
None of the COUNT family functions count unique values directly. Use =SUMPRODUCT(1/COUNTIF(range, range)) as a classic array formula, or use =COUNTA(UNIQUE(range)) in Excel 365/2021 which is much cleaner. The UNIQUE function extracts unique values first, then COUNTA counts them. This modern approach is far easier to read and maintain.
Do the COUNT functions work in Google Sheets?
Yes, COUNT, COUNTIF, COUNTIFS, COUNTA, and COUNTBLANK all work identically in Google Sheets with the same syntax as Excel. All have been in Google Sheets since day one. All examples and patterns in this article transfer directly between Excel and Google Sheets without changes. See Excel vs Google Sheets for more platform comparisons.
Why does COUNTA sometimes return more than expected?
COUNTA counts cells containing formulas that return empty strings ("") as non-empty. If your range has formulas like =IF(condition, "value", ""), cells that appear empty are still counted. To count only cells with visible content, use =COUNTIF(range, "?*") which uses a wildcard requiring at least one character.
What happens if COUNTIFS ranges have different sizes?
COUNTIFS returns the #VALUE! error if the ranges have different dimensions. All range arguments must have the same number of rows and columns. Using A1:A100 and B1:B50 in the same COUNTIFS will fail. Always use ranges of matching sizes — typically the same rows across different columns.
Bottom line: COUNTIF for one condition, COUNTIFS for many conditions, COUNTA for non-blank cells. Three different jobs — pick based on the question you're asking, not on habit. Master the criteria syntax once and it works across the entire IFS family.