Excel COUNTIFS: count rows that meet multiple criteria

By Emma Updated 2 November 2026 4 min read

COUNTIFS: Count with 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.

ABCDEF
1RepCityProductAmount (£)DateStatus
2EmmaLeedsPrinter32005/03/2026Paid
3JamesLondonLaptop115006/03/2026Pending
4OliviaLeedsLaptop98009/03/2026Paid
5EmmaLondonMonitor21012/03/2026Paid
6JamesLeedsPrinter29016/03/2026Cancelled
7HarryBristolLaptop132019/03/2026Paid
8EmmaLeedsLaptop105023/03/2026Pending
9OliviaLondonPrinter34002/04/2026Paid
10HarryLeedsMonitor18507/04/2026Paid
11JamesBristolMonitor23014/04/2026Paid
12EmmaLeedsPrinter31021/04/2026Paid
13OliviaBristolLaptop120028/04/2026Cancelled

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:B13 together with C2:C12 returns #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")
HI
1RepPaid orders
2Emma3
3James1
4Olivia2
5Harry2

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.

Emma · Writer, Excel Zone

Emma writes the tutorials on formulas and data cleaning: COUNTIF, SUMIF, DATEDIF, duplicates, drop-down lists. Each method is shown on a small table you can copy.