=COUNTIFS(A2:A7,"North",C2:C7,">50") counts the rows where column A is North and column C is greater than 50. COUNTIFS takes pairs of a range and a condition, and a row is counted only when every condition is true.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Condition | Count | |
| 2 | North | Apple | 120 | North, over 50 | 2 | |
| 3 | South | Pear | 45 | North Apple | 2 | |
| 4 | North | Pear | 80 | North only | 3 | |
| 5 | East | Apple | 55 | |||
| 6 | South | Apple | 200 | |||
| 7 | North | Apple | 30 |
There are three North rows, and two of them are over 50, so F2 shows 2. Change C7 to 90 and the last North row joins the count. F3 pairs two text conditions: North and Apple.
COUNTIFS syntax
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
- Each condition is a pair: a range and a criterion. Up to 127 pairs are allowed.
- The criteria work exactly like COUNTIF's:
"North",">50","<>","*apple*", or a cell joined to an operator with&, such as">"&F2. - All the ranges must have the same size. They are read row by row: the first cell of every range is one row, the second cell of every range the next.
- The conditions are joined with AND. For OR, see below.
COUNTIFS is in every Excel since 2007 and in Google Sheets.
COUNTIFS between two numbers
To count values in a band, use the same range twice, once with the lower bound and once with the upper one.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Setting | Value | |
| 2 | North | Apple | 120 | Min | 50 | |
| 3 | South | Pear | 45 | Max | 150 | |
| 4 | North | Pear | 80 | Between | 3 | |
| 5 | East | Apple | 55 | |||
| 6 | South | Apple | 200 | |||
| 7 | North | Apple | 30 |
120, 80 and 55 fall inside 50 to 150, so F4 shows 3. Change the bounds in F2 and F3 and the count follows. ">=" and "<=" include the bounds; use ">" and "<" to leave them out.
COUNTIFS between two dates
Dates work the same way. Keep the start and end dates in cells and join them to the operators, so you never type a date inside the quotes.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Sales | Date | Setting | Value | |
| 2 | North | 120 | 2026-01-05 | Start | 2026-02-01 | |
| 3 | South | 45 | 2026-01-12 | End | 2026-02-28 | |
| 4 | North | 80 | 2026-02-03 | Orders | 2 | |
| 5 | East | 55 | 2026-02-18 | North orders | 1 | |
| 6 | South | 200 | 2026-03-02 | |||
| 7 | North | 30 | 2026-03-20 |
Two orders fall in February, and one of them is from North. F5 has three pairs: one for the region and two for the date window. If your dates carry a time (2026-02-28 16:30), "<="&F3 misses everything after 0:00 on the last day; use "<"&F3+1 instead.
COUNTIFS with OR logic
COUNTIFS has no OR. To count North or South orders over 50, give the region criterion a list in braces. COUNTIFS then returns one count per item, and SUM adds them up.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Condition | Count | |
| 2 | North | Apple | 120 | North or South, over 50 | 3 | |
| 3 | South | Pear | 45 | Same, two COUNTIFS | 3 | |
| 4 | North | Pear | 80 | |||
| 5 | East | Apple | 55 | |||
| 6 | South | Apple | 200 | |||
| 7 | North | Apple | 30 |
Both formulas give 3: two North rows and one South row over 50. The braces version is shorter when the list grows. Use the list on one criterion only. Two lists in the same formula pair their items up instead of trying every combination, which is rarely what you want.
When the items are in cells (say E8:E9), =SUMPRODUCT(COUNTIFS(A2:A7,E8:E9,C2:C7,">50")) does the same without braces.
COUNTIFS not blank
"<>" means "not empty" and "" means "empty", so a column of ship dates tells you which orders are still open.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Order | Shipped | Condition | Count | |
| 2 | North | A-101 | 2026-01-08 | North, shipped | 2 | |
| 3 | South | A-102 | North, not shipped | 1 | ||
| 4 | North | A-103 | 2026-02-06 | |||
| 5 | East | A-104 | 2026-02-20 | |||
| 6 | South | A-105 | ||||
| 7 | North | A-106 |
Two North orders have a ship date and one does not. Type a date into C7 and the counts move from F3 to F2. A formula that returns an empty string ("") is counted by "" and also by "<>"; COUNTIF not blank explains the workarounds.
Practice: two conditions
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Condition | Count | |
| 2 | North | Apple | 120 | South Apple | ||
| 3 | South | Pear | 45 | |||
| 4 | North | Pear | 80 | |||
| 5 | East | Apple | 55 | |||
| 6 | South | Apple | 200 | |||
| 7 | North | Apple | 30 | |||
| 8 | South | Apple | 75 |
Your turn: Count the rows where the region is South and the product is Apple. Write the formula in F2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Sales | Date | Setting | Value | |
| 2 | North | 120 | 2026-01-05 | Start | 2026-01-01 | |
| 3 | South | 145 | 2026-01-12 | End | 2026-02-28 | |
| 4 | North | 80 | 2026-02-03 | Count | ||
| 5 | East | 155 | 2026-02-18 | |||
| 6 | South | 200 | 2026-03-02 | |||
| 7 | North | 30 | 2026-03-20 | |||
| 8 | West | 110 | 2026-02-25 |
Your turn: Count the orders from the date in F2 to the date in F3 (both included) with sales of at least 100. Write the formula in F4.
Why COUNTIFS returns #VALUE! or 0
- Ranges of different sizes.
=COUNTIFS(A2:A7,"North",C2:C6,">50")returns#VALUE!because A2:A7 has six cells and C2:C6 five. Make every range start and end on the same rows. - An AND that can never be true.
=COUNTIFS(A2:A7,"North",A2:A7,"South")is always 0: one cell cannot be both. That is an OR, so use the braces version above. - A date typed inside the quotes.
">=1/2/2026"is read in your system's date order. Put the date in a cell and write">="&F2. - Operator and cell inside the quotes.
">=F2"compares against the text F2. Write">="&F2.
COUNTIFS counts rows. To add up a column for the same rows, the arguments are almost identical with SUMIFS; for conditions COUNTIFS cannot express (one column compared to another, a calculation on each row), use SUMPRODUCT.
Frequently Asked Questions
What is the difference between COUNTIF and COUNTIFS?
COUNTIF takes one range and one condition. COUNTIFS takes any number of range and condition pairs and counts the rows where all of them are true: =COUNTIFS(A2:A7,"North",C2:C7,">50"). With one pair, COUNTIFS gives the same result as COUNTIF.
How do I count between two dates with COUNTIFS?
Use the date column twice, once with each bound: =COUNTIFS(C2:C7,">="&F2,C2:C7,"<="&F3) counts the dates from F2 to F3, both included.
How do I use COUNTIFS with OR?
COUNTIFS always joins its conditions with AND. For OR, give one criterion a list in braces and add the results: =SUM(COUNTIFS(A2:A7,{"North","South"},C2:C7,">50")).
Why does COUNTIFS return #VALUE!?
The ranges are not the same size. Every range in COUNTIFS must have the same number of rows and columns, so A2:A7 cannot be paired with C2:C6.
How do I count values between two numbers with COUNTIFS?
Use the same range twice, once per bound: =COUNTIFS(C2:C7,">=50",C2:C7,"<=150") counts the values from 50 to 150, both included. Use ">" and "<" to leave the bounds out.
