# How to use COUNTIF with OR conditions in Excel

Give COUNTIF a list of criteria and add up the counts: `=SUMPRODUCT(COUNTIF(B2:B11,E1:E2))` counts cells equal to E1 or E2.

**Excel:** `=SUMPRODUCT(COUNTIF(B2:B11,E1:E2))` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Order | Region |  | Region | East |
| O-1001 | East |  | or | West |
| O-1002 | West |  | Orders |  |
| O-1003 | North |  |  |  |
| O-1004 | East |  |  |  |
| O-1005 | South |  |  |  |
| O-1006 | West |  |  |  |
| O-1007 | East |  |  |  |
| O-1008 | North |  |  |  |
| O-1009 | West |  |  |  |
| O-1010 | Central |  |  |  |

Formula in E3: `=SUMPRODUCT(COUNTIF(B2:B11,E1:E2))`

Result: 6

## How it works

When the criteria is a range, COUNTIF returns one count per criteria cell: 3 for East and 3 for West. SUMPRODUCT adds them up. Because one cell can't be East and West at once, nothing is counted twice.

COUNTIFS can't do OR on its own, since all its conditions must be true together.

## Other ways

- `=COUNTIF(B2:B11,E1)+COUNTIF(B2:B11,E2)`: The plainest version. Fine for two or three values.
- `=SUM(COUNTIF(B2:B11,{"East","West"}))`: With the values typed in an array constant instead of cells.

## Common mistakes

- `=COUNTIFS(B2:B11,"East",B2:B11,"West")` returns 0: it asks for cells that are East and West at the same time.
- Adding counts works for OR on one column only. For OR across two different columns, rows that match both get counted twice; see the page on counting rows that meet either of two conditions.

## Questions

**How do I combine OR with an AND condition?**

Put the OR list in COUNTIFS and add the results: `=SUM(COUNTIFS(B2:B11,{"East","West"},C2:C11,"Shipped"))`.

**How do I count cells that contain one of several words?**

Use wildcards, but watch for cells that contain both. See the page on counting cells that contain one of several words.

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/countif-with-or