How to use COUNTIF with OR conditions in Excel

Q: 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")).

Formula (Excel and Google Sheets)

=SUMPRODUCT(COUNTIF(B2:B11,E1:E2))

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

Result on the example: 6 in E3
Works in: Every current version
Google Sheets: Same formula

See it work

The formula is already in E3. Change any input and the result updates, or edit the formula to experiment.

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.

=SUMPRODUCT(COUNTIF(B2:B11,E1:E2))

Result6

B2:B1110 rows × 1 col: "East", "West", "North", "East", …
A range: 10 cells in one column.
E1:E22 rows × 1 col: "East", "West"
A range: 2 cells in one column.
COUNTIF(B2:B11,E1:E2){2 rows × 1 col} 3, 3
COUNTIF: Counts cells that meet one condition.
- rangeB2:B1110 rows × 1 col: "East", "West", "North", "East", …
- criteriaE1:E22 rows × 1 col: "East", "West"
SUMPRODUCT(COUNTIF(B2:B11,E1:E2))6
SUMPRODUCT: Multiplies arrays row by row and adds the results.
- array1COUNTIF(B2:B11,E1:E2){2 rows × 1 col} 3, 3

In Google Sheets

The same formula works in Google Sheets: =SUMPRODUCT(COUNTIF(B2:B11,E1:E2)). 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,E1)+COUNTIF(B2:B11,E2)	6	The plainest version. Fine for two or three values.
=SUM(COUNTIF(B2:B11,{"East","West"}))	6	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.

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.

Use COUNTIF with OR conditions

In E3, count the orders whose region is E1 or E2.

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.

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.