How to count with multiple criteria in Excel
Formula (Excel and Google Sheets)
=COUNTIFS(B2:B10,G1,C2:C10,">="&G2,D2:D10,"Won")Use COUNTIFS with one range-and-condition pair per criterion; a row is counted only when all of them are true.
- Result on the example
- 4 in
G3 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in G3. Change any input and the result updates, or edit the formula to experiment.
How it works
Each pair in COUNTIFS is a range and a condition: B2:B10,G1 keeps the region, C2:C10,">="&G2 keeps deals of at least the minimum, and D2:D10,"Won" keeps won deals. The pairs are combined with AND, so a row counts only if it passes all three.
=COUNTIFS(B2:B10,G1,C2:C10,">="&G2,D2:D10,"Won")Result4
B2:B109 rows × 1 col: "East", "West", "East", "East", …A range: 9 cells in one column.
G1"East"The value in G1.
C2:C109 rows × 1 col: $12,000, $8,500, $15,500, $9,800, …A range: 9 cells in one column.
G2$10,000The value in G2.
">="&G2">=10000"The & operator joins as text.
D2:D109 rows × 1 col: "Won", "Won", "Lost", "Won", …A range: 9 cells in one column.
COUNTIFS(B2:B10,G1,C2:C10,">="&G2,D2:D10,"Won")4COUNTIFS: Counts rows that meet every condition.- criteria_range1
B2:B109 rows × 1 col: "East", "West", "East", "East", … - criteria1
G1"East" - criteria_range2
C2:C109 rows × 1 col: $12,000, $8,500, $15,500, $9,800, … - criteria2
">="&G2">=10000" - criteria_range3
D2:D109 rows × 1 col: "Won", "Won", "Lost", "Won", … - criteria3
"Won"
- criteria_range1
In Google Sheets
The same formula works in Google Sheets: =COUNTIFS(B2:B10,G1,C2:C10,">="&G2,D2:D10,"Won"). We ran it in Google Sheets mode on the same data and it returns 4.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT((B2:B10=G1)*(C2:C10>=G2)*(D2:D10="Won")) | 4 | Multiplying the tests is the same AND logic. Use it when a condition needs a calculation, such as a test on part of the text. |
Common mistakes
- All the ranges must be the same size.
B2:B10withC2:C11returns #VALUE!. - COUNTIFS joins conditions with AND. For OR (East or West), add two COUNTIFS together.
- A comparison needs quotes around the operator:
">="&G2, not>=G2.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Count with multiple criteria
G3, count the won deals in the region in G1 that are at least the size in G2.Questions
How do I count with OR criteria?
Add one COUNTIFS per alternative: =COUNTIFS(B2:B10,"East",D2:D10,"Won")+COUNTIFS(B2:B10,"West",D2:D10,"Won").
Can COUNTIFS use criteria on the same column twice?
Yes. Repeat the range with a second condition, for example a minimum and a maximum on C2:C10.
Checked by running on 2026-10-29: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.