How to count rows that meet at least two of three conditions in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(--((B2:B11>=G1)+(C2:C11>=G2)+(D2:D11=G3)>=2))Add the three tests and count rows where the total is 2 or more: =SUMPRODUCT(--((B2:B11>=G1)+(C2:C11>=G2)+(D2:D11=G3)>=2)).
- Result on the example
- 5 in
G4 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in G4. Change any input and the result updates, or edit the formula to experiment.
How it works
Each comparison returns TRUE or FALSE per lead, and adding them turns TRUE into 1, so each lead gets a score from 0 to 3. >=2 keeps the leads that score 2 or 3, and SUMPRODUCT counts them.
Five leads qualify; only L-01 and L-07 meet all three.
=SUMPRODUCT(--((B2:B11>=G1)+(C2:C11>=G2)+(D2:D11=G3)>=2))Result5
B2:B1110 rows × 1 col: $25,000, $8,000, $12,000, $5,000, …A range: 10 cells in one column.
G1$10,000The value in G1.
B2:B11>=G1{10 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …The >= operator tests at least.
C2:C1110 rows × 1 col: 120, 60, 20, 15, …A range: 10 cells in one column.
G250The value in G2.
C2:C11>=G2{10 rows × 1 col} TRUE, TRUE, FALSE, FALSE, …The >= operator tests at least.
B2:B11>=G1)+(C2:C11>=G2{10 rows × 1 col} 2, 1, 1, 0, …The + operator adds.
D2:D1110 rows × 1 col: "US", "US", "UK", "US", …A range: 10 cells in one column.
G3"US"The value in G3.
D2:D11=G3{10 rows × 1 col} TRUE, TRUE, FALSE, TRUE, …The = operator tests whether the two sides are equal.
B2:B11>=G1)+(C2:C11>=G2)+(D2:D11=G3{10 rows × 1 col} 3, 2, 1, 1, …The + operator adds.
B2:B11>=G1)+(C2:C11>=G2)+(D2:D11=G3)>=2{10 rows × 1 col} TRUE, TRUE, FALSE, FALSE, …The >= operator tests at least.
-((B2:B11>=G1)+(C2:C11>=G2)+(D2:D11=G3)>=2{10 rows × 1 col} -1, -1, 0, 0, …Makes the value negative.
--((B2:B11>=G1)+(C2:C11>=G2)+(D2:D11=G3)>=2{10 rows × 1 col} 1, 1, 0, 0, …Makes the value negative.
SUMPRODUCT(--((B2:B11>=G1)+(C2:C11>=G2)+(D2:D11=G3)>=2))5SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--((B2:B11>=G1)+(C2:C11>=G2)+(D2:D11=G3)>=2{10 rows × 1 col} 1, 1, 0, 0, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(--((B2:B11>=G1)+(C2:C11>=G2)+(D2:D11=G3)>=2)). We ran it in Google Sheets mode on the same data and it returns 5.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =COUNTIFS(B2:B11,">="&G1,C2:C11,">="&G2,D2:D11,G3) | 2Different result | COUNTIFS requires every condition. Counts only the 2 leads that meet all three. |
Common mistakes
- COUNTIFS can't express "at least two of"; its conditions are always all required.
- Brackets matter: the three tests must be added before the
>=2comparison, or only the last test is compared.
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 rows that meet at least two of three conditions
G4, count the leads that meet at least two of the three conditions in G1:G3.Questions
How do I weight the conditions differently?
Multiply each test by its weight: =SUMPRODUCT(--((B2:B11>=G1)*2+(C2:C11>=G2)+(D2:D11=G3)>=3)).
How do I show each lead's score?
In a helper column: =(B2>=$G$1)+(C2>=$G$2)+(D2=$G$3).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.