COUNTIFS Generator
Count the rows that meet two or more conditions at the same time.
COUNTIFS
Count the rows that meet two or more conditions at the same time.
Column checked against the first condition. There is no separate count range.
Condition for the first range. Quote text and comparisons; leave cell references unquoted.
Column checked against the second condition. Must be the same size as the first range.
Condition for the second range, for example ">100", "<>" or "*paid*".
=COUNTIFS(A2:A100, "West", B2:B100, ">100")Worked Example
An order list with region in column A and units in column B; you want to know how many West orders exceeded 100 units.
=COUNTIFS(A2:A100, "West", B2:B100, ">100")Returns: Returns the number of rows where column A is West AND column B is greater than 100 - so 4 if exactly four rows meet both tests.
Checks Before You Paste
- •COUNTIFS keeps COUNTIF's ordering, range first then criteria, so arguments alternate: range1, criteria1, range2, criteria2. There is no separate count range - COUNTIFS returns the number of rows that satisfy every pair, unlike SUMIFS which needs a sum range up front.
- •All criteria ranges must be the same size. A2:A100 paired with B2:B101 returns #VALUE! in Excel, so avoid mixing a bounded range with a whole-column reference like B:B.
- •Criteria shortcuts worth knowing: "" counts blank cells (including formulas that return an empty string), "<>" counts cells that are not blank, and "*" counts cells containing text, which excludes numbers, dates and blanks.
How COUNTIFS works
Availability: Excel 2007 and later on Windows and Mac, Excel for the web, Google Sheets and LibreOffice Calc. In Excel 2003 the equivalent is SUMPRODUCT over multiplied comparisons.
COUNTIFS returns how many rows satisfy every condition at once. It keeps COUNTIF's argument order rather than SUMIFS' - range first, then criterion, then the next range and criterion, alternating up to 127 pairs. There is no count range to supply, because the answer is the number of positions where all the tests pass; what gets counted is rows, not cells.
That row-wise comparison is what separates it from COUNTIF. COUNTIF over a two-column range counts each cell independently, so a record matching in both columns counts twice. COUNTIFS lines its ranges up position by position: the first cell of range one is compared with the first cell of range two, and only a position where every test passes adds one to the total. This is also why the ranges must be the same size - A2:A100 paired with B2:B101 returns #VALUE! rather than aligning what it can.
All criteria are AND-ed, with no way to switch any of them to OR. Two conditions on the same range are how a between test is written: =COUNTIFS(B2:B500,">=100",B2:B500,"<=500"). For an OR you add two COUNTIFS together, or use an array constant with SUM, and if the two conditions can both be true of the same row you have to subtract the overlap to avoid double-counting.
Three criteria shortcuts are worth knowing because nothing else says them as briefly. "" counts blank cells, including cells holding a zero-length string returned by a formula. "<>" counts everything that is not blank. "*" counts cells containing text, which deliberately excludes numbers, dates, booleans and blanks - so pairing "*" against a numeric column is a fast way to find the rows that were imported as text and are quietly missing from your totals. Text matching throughout is case-insensitive, and to compare against a cell the operator has to be concatenated: ">"&$F$1.
Syntax
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)| Argument | Required | What it does |
|---|---|---|
criteria_range1 | Required | The first range to test. Its shape sets the shape every other criteria range must have; a mismatch returns #VALUE!. |
criteria1 | Required | The first condition: a value, a reference, or quoted text carrying an operator and the wildcards * ? ~. "" counts blanks and "<>" counts non-blanks. |
criteria_range2, criteria2 | Optional | Further pairs, up to 127. Compared position by position against the first range and AND-ed together. Repeating one range with two comparisons gives a between test. |
More worked examples
A ticket log with a raised date in A2:A5000, an owner in C2:C5000 and a priority in D2:D5000. You want the number of high-priority tickets Sam opened in 2026.
=COUNTIFS(C2:C5000, "Sam", D2:D5000, "High", A2:A5000, ">="&DATE(2026,1,1), A2:A5000, "<"&DATE(2027,1,1))Returns: The count of rows where all four tests hold - for example 37.
Using "<" against 1 January of the next year rather than "<=" against 31 December avoids losing rows whose date carries a time component.
Exam scores in B2:B300, counting how many sit in the 60 to 69 band inclusive.
=COUNTIFS(B2:B300, ">=60", B2:B300, "<=69")Returns: The number of scores from 60 to 69.
The same range twice is the normal way to bracket a value. FREQUENCY does the whole distribution in one formula if you need every band at once.
A survey sheet where column E should hold a numeric rating, checking how many rows were left blank and how many were filled in as text.
=COUNTIFS(E2:E400, "")&" blank, "&COUNTIFS(E2:E400, "*")&" text"Returns: "12 blank, 5 text" on a sheet with twelve empty ratings and five typed as words.
"*" matches text only, so this will not report a rating that is a real number. It is the quickest audit of a column that should be numeric but is not adding up.
Common mistakes
- Mixing whole-column and bounded ranges
- =COUNTIFS(A:A, "West", B2:B500, ">100") returns #VALUE!, because A:A is over a million rows and B2:B500 is 499. Every criteria range has to be identical in size and shape - use whole columns for all of them or bound all of them.
- Expecting two criteria on one column to be OR
- =COUNTIFS(B2:B500,"West",B2:B500,"North") is always 0, because a cell cannot hold both words. Add two COUNTIFs, or use =SUM(COUNTIFS(B2:B500,{"West","North"})), which runs the function once per item.
- Double-counting when OR conditions overlap
- Adding COUNTIFS(...,"West") and COUNTIFS(...,">1000") counts a West order over 1,000 twice. Subtract the intersection: the two counts minus COUNTIFS with both conditions together.
- "<>Closed" quietly including the blanks
- An empty cell is not the word Closed, so it satisfies the criterion and joins the count. Add a second pair on the same range, "<>", when unfilled rows should not be counted as open.
- Counting cells when you meant to count rows
- COUNTIFS with a single multi-column criteria range counts every matching cell in the block, which is rarely the question being asked. Give it one range per field, each one column wide, and it counts records.
Frequently Asked Questions
COUNTIF takes exactly one range and one criterion; there is no multi-range form of it. If you give COUNTIF a wide range it counts cells across the whole block independently. COUNTIFS aligns its ranges row by row and counts positions where all the conditions hold - which is what "how many records match" actually means.
COUNTIFS cannot do it - it counts rows, and duplicates are separate rows. In Excel 2021 or Microsoft 365 use =COUNTA(UNIQUE(FILTER(A2:A500, B2:B500="West"))). In older versions, add a helper column holding =IF(AND(B2="West", COUNTIFS($A$2:A2, A2, $B$2:B2, "West")=1), 1, 0) - the expanding range makes it 1 only on a value's first appearance - and sum it.
Its ranges can point at another sheet, but they must all be the same size, and the ranges themselves cannot be a 3D reference across several sheets - =COUNTIFS(Jan:Mar!A2:A100,"West") is not valid. Count each sheet separately and add the results.
Usually because the dates carry a time. 31/12/2026 09:15 is greater than 31/12/2026, so "<="&DATE(2026,12,31) excludes it. Test with "<"&DATE(2027,1,1) instead, which covers the whole final day whatever time is attached.
No. It reads every row in the range, visible or not, so its answer will not follow an AutoFilter. SUBTOTAL(103, range) counts visible non-blank cells but takes no criteria, so combining filtering with conditions usually means SUMPRODUCT or a PivotTable.