How to count with OR across two columns in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(--((B2:B11=F1)+(C2:C11>F2)>0))Add the two tests and count rows where the sum is above zero: =SUMPRODUCT(--((B2:B11=F1)+(C2:C11>F2)>0)).
- Result on the example
- 6 in
F3 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F3. Change any input and the result updates, or edit the formula to experiment.
How it works
Each test gives TRUE or FALSE per row, and adding them gives 0, 1 or 2: the number of conditions the row meets. >0 keeps every row that meets at least one, so a row that meets both is still counted once.
Three orders are East and four are over 1,000, but O-3 is both, so the answer is 6, not 7.
=SUMPRODUCT(--((B2:B11=F1)+(C2:C11>F2)>0))Result6
B2:B1110 rows × 1 col: "East", "West", "East", "North", …A range: 10 cells in one column.
F1"East"The value in F1.
B2:B11=F1{10 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …The = operator tests whether the two sides are equal.
C2:C1110 rows × 1 col: $420, $1,250, $1,480, $310, …A range: 10 cells in one column.
F2$1,000The value in F2.
C2:C11>F2{10 rows × 1 col} FALSE, TRUE, TRUE, FALSE, …The > operator tests greater than.
B2:B11=F1)+(C2:C11>F2{10 rows × 1 col} 1, 1, 2, 0, …The + operator adds.
B2:B11=F1)+(C2:C11>F2)>0{10 rows × 1 col} TRUE, TRUE, TRUE, FALSE, …The > operator tests greater than.
-((B2:B11=F1)+(C2:C11>F2)>0{10 rows × 1 col} -1, -1, -1, 0, …Makes the value negative.
--((B2:B11=F1)+(C2:C11>F2)>0{10 rows × 1 col} 1, 1, 1, 0, …Makes the value negative.
SUMPRODUCT(--((B2:B11=F1)+(C2:C11>F2)>0))6SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--((B2:B11=F1)+(C2:C11>F2)>0{10 rows × 1 col} 1, 1, 1, 0, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(--((B2:B11=F1)+(C2:C11>F2)>0)). We ran it in Google Sheets mode on the same data and it returns 6.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =COUNTIF(B2:B11,F1)+COUNTIF(C2:C11,">"&F2)-COUNTIFS(B2:B11,F1,C2:C11,">"&F2) | 6 | Adds the two counts and subtracts the rows counted twice. No array logic, so it is easy to audit. |
| =COUNTIF(B2:B11,F1)+COUNTIF(C2:C11,">"&F2) | 7Different result | The tempting shortcut. Counts O-3 twice because it meets both conditions. |
Common mistakes
- Adding two COUNTIFs on different columns double counts the rows that meet both conditions.
- Without
>0, SUMPRODUCT adds the 2s from rows that meet both tests, which also double counts.
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 OR across two columns
F3, count the orders that are in the region in F1 or have an amount over F2.Questions
How do I count with OR on two columns and an AND on a third?
Multiply the OR part by the AND test: =SUMPRODUCT(((B2:B11=F1)+(C2:C11>F2)>0)*(D2:D11="Paid")).
How do I list those rows instead?
In Excel 365 or Google Sheets: =FILTER(A2:C11,(B2:B11=F1)+(C2:C11>F2)).
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.