Arguments
criteria_range1- The first range to test.criteria1- The condition applied to the first range.
COUNTIFS
Counts cells that meet multiple conditions across several ranges.
Syntax
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)=COUNTIFS(A2:A50, "West", B2:B50, ">100")Returns: Rows where A is West AND B > 100
Every pair must match for the row to be counted.
How COUNTIFS evaluates
COUNTIFS counts the positions where every criteria pair passes at once. Each pair is a range and a criterion, and a cell position is counted only if it satisfies all of them - the conditions are joined with AND and there is no OR variant. With a single pair it does exactly what COUNTIF does, which is why COUNTIFS is a reasonable default even when only one condition exists today and a second is likely tomorrow.
Unlike SUMIFS there is no leading range to remember: the arguments are pairs from the start, so the count is of matching positions rather than of anything in a separate column. All the ranges must have the same number of rows and columns, because the test is applied position by position; a mismatch is refused with #VALUE! rather than aligned on the overlap.
Its usual job is building a cross-tab by hand - one COUNTIFS per cell of a grid, with the row header and column header as the two criteria. That is also where its cost shows: each formula re-reads every range in full, so a grid of a few hundred COUNTIFS over long columns is noticeably slower than the pivot table that produces the same numbers.
- Conditions are ANDed per position
- Row 12 is counted only when range1[12] passes criterion1 and range2[12] passes criterion2. Two criteria against the same range can therefore never both hold, so =COUNTIFS(A:A, "West", A:A, "East") is always 0.
- Every range must be the same shape
- A2:A100 with B2:B99 gives #VALUE!. The ranges do not have to be adjacent or even on the same sheet, but they must have identical dimensions so that position n means the same record in each.
- Criteria use the shared criteria parser
- Leading operators, case-insensitive text comparison, * and ? wildcards with ~ as the escape, and loose number/text equality all behave exactly as in COUNTIF. A criterion referring to a cell must be concatenated: ">"&$G$1.
- Blanks need explicit handling
- A blank cell fails every inequality, so ">0" silently excludes empty rows. Count them with "" for empty text, "<>" for anything non-empty, or COUNTBLANK when truly empty cells are the target.
- Counts positions, not distinct values
- Every matching cell adds 1, so a customer appearing eleven times contributes eleven. There is no de-duplicating variant; for a count of distinct matches, filter first with UNIQUE in Excel 2021 or 365, or use a helper column that flags only the first occurrence.
- Errors propagate from any range
- An error value in any criteria range returns that error for the whole formula, even if the row containing it would have failed the criteria anyway.
Errors COUNTIFS returns
| Error | What it means and how to fix it |
|---|---|
| #VALUE! | The ranges are not all the same size, or the argument count is odd so a criterion is missing its range. It also appears when any range is in a closed workbook, which COUNTIFS cannot read. |
| #NAME? | A criterion was left unquoted, or a named range in one of the arguments no longer exists in the workbook. |
| #REF! | One of the ranges points at deleted rows or columns. With many arguments it is worth checking each range in the formula bar rather than guessing. |
| An error from the data showing through | #N/A, #DIV/0! and the rest are not produced by COUNTIFS; they come from a cell inside a criteria range. Clear the source cells rather than wrapping the count in IFERROR. |
| 0 with no error | No position satisfied every condition. Remove pairs one at a time to find which criterion excludes everything - date criteria typed as text inside quotes, and blanks failing an inequality, are the two usual causes. |
More examples
=COUNTIFS($A$2:$A$2000, $F2, $B$2:$B$2000, G$1)Returns: One cell of a cross-tab
Row labels in column F and column headers in row 1, with mixed anchoring so a single formula fills the whole grid when dragged.
=COUNTIFS($C$2:$C$500, ">="&$H$1, $C$2:$C$500, "<"&$H$2)Returns: How many values fall inside a half-open band
The same range appears twice to bound a number or date from both sides. Using >= and < rather than >= and <= means adjacent bands cannot double-count a boundary value.
=COUNTIFS($A$2:$A$800, "*"&$J$1&"*", $D$2:$D$800, "<>", $E$2:$E$800, "<>Void")Returns: Rows matching a keyword, with a value present and not voided
Three pairs mixing a wildcard, a not-blank test and a negation - the shape most real filters end up in.
Compatibility
| Application | Support |
|---|---|
| Excel 2007-2016 | Available from Excel 2007. Files saved back to the Excel 2003 format lose it, leaving #NAME?. |
| Excel 2019 | Identical, with up to 127 range/criteria pairs per call. |
| Excel 2021 / Microsoft 365 | Unchanged. An array of criteria now spills one count per criterion instead of needing an array entry. |
| Google Sheets | Same syntax and same AND semantics; regex criteria are available when enabled for the file. |
| LibreOffice Calc | Supported; wildcard versus regular-expression handling follows the setting under Tools, Options, Calc, Calculate. |
Does COUNTIFS Work in Google Sheets?
Yes. COUNTIFS uses the same syntax in Google Sheets and LibreOffice Calc, so you can paste these formulas straight across.
In the ExcelTool editor
The ExcelTool editor implements COUNTIFS with any number of range/criteria pairs and returns #VALUE! when the argument count is odd, which means a pair is incomplete.
Every criteria range must have the same number of rows and columns or the result is #VALUE!; the editor does not align ranges on their overlap. Positions are tested in order and a position is counted only when every criterion passes, so the conditions are always ANDed.
Criteria are parsed by the same code as COUNTIF and SUMIF, so leading operators, case-insensitive text comparison, loose number-versus-numeric-text equality, and * and ? wildcards with ~ as the escape character all behave the same way here.
You can try any of these formulas in the free in-browser spreadsheet editor - it opens .xlsx files, recalculates as you type, and needs no signup.
COUNTIFS questions
COUNTIFS only ANDs, so the standard answer is to add calls: =COUNTIFS(A:A, "West", B:B, ">100") + COUNTIFS(A:A, "East", B:B, ">100"). If the alternatives are on the same column you can pass an array and wrap it in SUM: =SUM(COUNTIFS(A2:A50, {"West","East"}, B2:B50, ">100")). Check that the alternatives are mutually exclusive, or rows matching both get counted twice.
The ranges are different sizes. COUNTIFS compares position by position, so it insists that every criteria range has the same number of rows and columns - A2:A100 alongside B2:B101 is refused outright. COUNTIF never notices because it only ever sees one range. Rewrite the ranges to identical bounds, or use whole-column references, which are always the same height.
Yes: =COUNTIFS(A2:A500, "<>", B2:B500, "") counts rows where A has content and B is empty. The two criteria are opposites of each other - "<>" means not empty, "" means empty text or blank. Be aware that a formula returning "" counts as empty text here, so a column of formulas will not behave like a column of genuinely untouched cells.
For a handful of cells, COUNTIFS is fine and has the advantage of updating with the data and being readable in the formula bar. For a large grid it is the wrong tool: each formula reads every range in full, so a 20 by 30 grid over 50,000 rows performs 600 full passes on every recalculation. A pivot table builds the same counts in one pass, at the cost of needing a refresh.