# How to count with OR across two columns in Excel

Add the two tests and count rows where the sum is above zero: `=SUMPRODUCT(--((B2:B11=F1)+(C2:C11>F2)>0))`.

**Excel:** `=SUMPRODUCT(--((B2:B11=F1)+(C2:C11>F2)>0))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Order | Region | Amount |  | Region | East |
| O-1 | East | 420 |  | or over | 1000 |
| O-2 | West | 1250 |  | Orders |  |
| O-3 | East | 1480 |  |  |  |
| O-4 | North | 310 |  |  |  |
| O-5 | South | 1090 |  |  |  |
| O-6 | East | 95 |  |  |  |
| O-7 | West | 640 |  |  |  |
| O-8 | North | 2210 |  |  |  |
| O-9 | South | 515 |  |  |  |
| O-10 | West | 880 |  |  |  |

Formula in F3: `=SUMPRODUCT(--((B2:B11=F1)+(C2:C11>F2)>0))`

Result: 6

## 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.

## Other ways

- `=COUNTIF(B2:B11,F1)+COUNTIF(C2:C11,">"&F2)-COUNTIFS(B2:B11,F1,C2:C11,">"&F2)`: 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)`: 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.

## 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))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/count-rows-with-or-across-two-columns