Excel COUNTIFS: count rows that meet multiple criteria

To count rows based on conditions in one or more other columns, use COUNTIFS. Each condition is a "range, criteria" pair: =COUNTIFS(B2:B13,"Leeds",C2:C13,"Laptop") counts orders placed in Leeds and for a laptop.
On the table below, that formula returns 2 (rows 4 and 8). Every example in this guide uses the same data.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | City | Product | Amount (£) | Date | Status |
| 2 | Emma | Leeds | Printer | 320 | 05/03/2026 | Paid |
| 3 | James | London | Laptop | 1150 | 06/03/2026 | Pending |
| 4 | Olivia | Leeds | Laptop | 980 | 09/03/2026 | Paid |
| 5 | Emma | London | Monitor | 210 | 12/03/2026 | Paid |
| 6 | James | Leeds | Printer | 290 | 16/03/2026 | Cancelled |
| 7 | Harry | Bristol | Laptop | 1320 | 19/03/2026 | Paid |
| 8 | Emma | Leeds | Laptop | 1050 | 23/03/2026 | Pending |
| 9 | Olivia | London | Printer | 340 | 02/04/2026 | Paid |
| 10 | Harry | Leeds | Monitor | 185 | 07/04/2026 | Paid |
| 11 | James | Bristol | Monitor | 230 | 14/04/2026 | Paid |
| 12 | Emma | Leeds | Printer | 310 | 21/04/2026 | Paid |
| 13 | Olivia | Bristol | Laptop | 1200 | 28/04/2026 | Cancelled |
COUNTIFS syntax
=COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2],…)
- Each criteria_range is the column the condition applies to; the criteria that follows it is tested against that column.
- A row is counted only when all conditions are true (AND logic).
- All ranges must have the same number of rows.
B2:B13together withC2:C12returns #VALUE!. - Criteria work exactly as in COUNTIF: text and operators in quotes,
*and?wildcards allowed. See our COUNTIF guide for the details.
With a single pair, COUNTIFS gives the same result as COUNTIF. Excel accepts up to 127 pairs.
Count based on another column
This is the everyday case: the column you filter on is not the one you are thinking about. How many paid orders are there in Leeds?
=COUNTIFS(B2:B13,"Leeds",F2:F13,"Paid")
Result: 4 (rows 2, 4, 10 and 12). Rows 6 (cancelled) and 8 (pending) are in Leeds but fail the second condition.
To exclude a value, use <>. Leeds orders that are not cancelled:
=COUNTIFS(B2:B13,"Leeds",F2:F13,"<>Cancelled")
Result: 5 (the 6 Leeds orders minus row 6).
If what you actually need is the value from another column (the amount of one specific order, say), you are no longer counting: VLOOKUP returns the matching value.
Numeric criteria: threshold and range
Laptops sold for more than £1,000:
=COUNTIFS(C2:C13,"Laptop",D2:D13,">1000")
Result: 4 (rows 3, 7, 8 and 13; the £980 laptop in row 4 is left out).
For a range of values, repeat the same column with a lower and an upper limit. Orders between £200 and £400:
=COUNTIFS(D2:D13,">=200",D2:D13,"<=400")
Result: 6 (320, 210, 290, 340, 230 and 310).
Count between two dates
Same idea as the amount range, using DATE so the regional date format cannot get in the way. Orders in March 2026:
=COUNTIFS(E2:E13,">="&DATE(2026,3,1),E2:E13,"<="&DATE(2026,3,31))
Result: 7 (rows 2 to 8). Add a third pair to keep only Emma's orders:
=COUNTIFS(E2:E13,">="&DATE(2026,3,1),E2:E13,"<="&DATE(2026,3,31),A2:A13,"Emma")
Result: 3 (rows 2, 5 and 8).
Build a summary table
To get the number of paid orders per rep, type the names in H2:H5, then enter this in I2 and fill it down:
=COUNTIFS($A$2:$A$13,H2,$F$2:$F$13,"Paid")
| H | I | |
|---|---|---|
| 1 | Rep | Paid orders |
| 2 | Emma | 3 |
| 3 | James | 1 |
| 4 | Olivia | 2 |
| 5 | Harry | 2 |
The dollar signs lock the ranges; only H2 changes from row to row. The total (8) matches the 8 paid orders in the data. Without a formula, a PivotTable can count the orders per rep: put Rep in Rows and in Values (a text field there is counted), and Status in Filters set to "Paid".
OR criteria: add COUNTIFS results together
COUNTIFS only does AND. For OR, you add results.
Several values in the same column
Laptops sold in Leeds or London:
=COUNTIFS(B2:B13,"Leeds",C2:C13,"Laptop")+COUNTIFS(B2:B13,"London",C2:C13,"Laptop")
Result: 3 (2 in Leeds + 1 in London). When the list grows, put the values in cells (K2 = Leeds, K3 = London) and let SUMPRODUCT do the adding:
=SUMPRODUCT(COUNTIFS(B2:B13,K2:K3,C2:C13,"Laptop"))
Same result, 3. COUNTIFS returns one count per city in K2:K3 and SUMPRODUCT adds them up. To include another city, type it in K4 and extend the range to K2:K4.
A condition on one column or a condition on another
Orders placed in Leeds or for a laptop: adding two COUNTIFs would count Leeds laptops twice, so subtract the overlap:
=COUNTIF(B2:B13,"Leeds")+COUNTIF(C2:C13,"Laptop")-COUNTIFS(B2:B13,"Leeds",C2:C13,"Laptop")
Result: 9 (6 in Leeds, plus 5 laptops, minus the 2 Leeds laptops).
SUMPRODUCT when COUNTIFS is not enough
COUNTIFS compares each cell as it is. As soon as the condition needs a calculation on the column (the month of a date, part of a text, a test across two columns at once), SUMPRODUCT takes over. Each condition in brackets returns TRUE or FALSE; multiplying them gives 1 only when every condition is true.
Leeds orders placed in April, without building start and end dates:
=SUMPRODUCT((B2:B13="Leeds")*(MONTH(E2:E13)=4))
Result: 2 (rows 10 and 12). MONTH ignores the year: on data that spans several years, add *(YEAR(E2:E13)=2026).
OR across two columns can also be written with SUMPRODUCT, adding the conditions instead of multiplying them:
=SUMPRODUCT(((B2:B13="Leeds")+(C2:C13="Laptop")>0)*1)
Result: 9, the same as the subtraction method. The >0 stops a row that meets both conditions from being counted twice.
If the result is 0 or wrong
The usual causes are hidden spaces in cells, numbers stored as text, ranges of different sizes or a badly joined operator. Each one is covered in COUNTIFS not working: causes and fixes. To add up amounts rather than count rows, see SUMIF. Microsoft's reference page for the function is on Microsoft Support.


