Cellwise Try it free

Excel guide · 4 min read

SUMIF with OR: sum if a cell matches one of several values

Quick answerAdd one SUMIF per value: =SUMIF(B2:B11,"Travel",D2:D11)+SUMIF(B2:B11,"Meals",D2:D11) totals the rows whose category is Travel or Meals. SUMIFS cannot do this on its own, because every condition in SUMIFS must be true at once (AND logic).

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDEF
1DateCategoryDepartmentAmountCategories
2Oct 1, 2026TravelSales420Travel
3Oct 2, 2026SoftwareOps89Meals
4Oct 5, 2026MealsSales64.5
5Oct 7, 2026TravelOps310
6Oct 9, 2026Office suppliesOps47.2
7Oct 12, 2026MealsOps38
8Oct 14, 2026TravelSales615
9Oct 19, 2026TrainingSales250
10Oct 22, 2026MealsSales92.75
11Oct 28, 2026SoftwareSales129

Download this sheet as an Excel practice file (.xlsx)

Method 1: add two SUMIFs (any version)

October's expenses are in rows 2 to 11. Finance wants travel and meals together. Each SUMIF adds the amounts for one category, and adding the two results gives the OR total. Because a cell cannot be Travel and Meals at the same time, no row is counted twice.

This is the easiest version to read and check, and it works in every Excel version. Use it when you have two or three values.

=SUMIF(B2:B11,"Travel",D2:D11)+SUMIF(B2:B11,"Meals",D2:D11)

Result: 1540.25

1,540.25: Travel is 1,345 and Meals is 195.25.

Why SUMIFS with two values in one column returns 0

The first thing most people try is SUMIFS with both values. SUMIFS joins its conditions with AND: a row must pass every test. No category cell can equal Travel and Meals together, so nothing passes and the result is 0, with no error to warn you.

SUMIFS is still the right tool when the conditions are on different columns, such as Department is Sales AND Category is Travel.

=SUMIFS(D2:D11,B2:B11,"Travel",B2:B11,"Meals")

Result: 0

0, because no row is both Travel and Meals.

Method 2: SUMPRODUCT with + for OR

Each comparison such as (B2:B11="Travel") gives a list of TRUE and FALSE values. Adding two lists turns them into 1s and 0s: 1 where either test is true. Multiplying by the amounts keeps only those rows.

This scales better than chaining SUMIFs, since each extra value is one more +(...) inside the brackets. It also works in every version without Ctrl+Shift+Enter. In Excel 365 and 2021, =SUM(FILTER(D2:D11,(B2:B11="Travel")+(B2:B11="Meals"))) gives the same result.

=SUMPRODUCT(((B2:B11="Travel")+(B2:B11="Meals"))*D2:D11)

Result: 1540.25

1,540.25, the same as the two SUMIFs.

OR plus an AND condition

To get travel and meals for the Sales department only, add the department to each SUMIFS, or multiply the SUMPRODUCT by one more test. Multiplying works like AND; adding works like OR.

The SUMIFS version is =SUMIFS(D2:D11,C2:C11,"Sales",B2:B11,"Travel")+SUMIFS(D2:D11,C2:C11,"Sales",B2:B11,"Meals"), which also returns 1,192.25. Pick whichever you find easier to maintain.

=SUMPRODUCT((C2:C11="Sales")*((B2:B11="Travel")+(B2:B11="Meals"))*D2:D11)

Result: 1192.25

1,192.25: Sales travel (420 + 615) plus Sales meals (64.5 + 92.75). The 310 Ops trip and the 38 Ops lunch are left out.

OR across two different columns: avoid double counting

"Travel or Sales" is different: the tests are on two columns, and one row can pass both. Adding the tests gives 2 for such a row, so its amount is added twice. =SUMPRODUCT(((B2:B11="Travel")+(C2:C11="Sales"))*D2:D11) returns 2,916.25, far more than the true figure, because the two Sales trips are counted twice.

Wrap the sum of tests in (...)>0 so every row counts at most once. Two SUMIFs have the same problem here, which is why you would have to subtract the overlap with a SUMIFS.

=SUMPRODUCT((((B2:B11="Travel")+(C2:C11="Sales"))>0)*D2:D11)

Result: 1881.25

1,881.25: all six Sales rows (1,571.25) plus the 310 Ops trip, each counted once.

Many values: array constants and criteria lists

With a long list of values, chaining SUMIFs gets unwieldy. Excel lets you give SUMIF a list of criteria in braces, =SUM(SUMIF(B2:B11,{"Travel","Meals"},D2:D11)). SUMIF returns one total per value and SUM adds them. This works as a normal formula in every version.

To keep the values editable, list them in cells (F2:F3 here) and use =SUMPRODUCT(SUMIF(B2:B11,F2:F3,D2:D11)). Adding a category is then just adding a cell, and the formula's range must cover it. In Excel 2019 and earlier, keep SUMPRODUCT here rather than SUM, or SUM needs Ctrl+Shift+Enter. Keep each value in the list once, or it is counted twice.

Common mistakes

Two criteria for the same column in SUMIFS

SUMIFS needs every condition to be true, so "Travel" and "Meals" on the same column always returns 0. Add one SUMIF per value instead.

Double counting with OR on different columns

A row can pass both tests. Use ((test1)+(test2)>0) inside SUMPRODUCT so it counts once.

Using * when you mean OR

In SUMPRODUCT, * between tests is AND and + is OR. (B2:B11="Travel")*(B2:B11="Meals") is always 0, just like the SUMIFS version.

Ranges of different sizes in SUMPRODUCT

B2:B11 and D2:D11 must have the same number of rows, or SUMPRODUCT returns #VALUE!. Use whole matching ranges, or convert the data to a table.

FAQ

Can SUMIF have multiple criteria in the same column?

Not in one SUMIF. Add one SUMIF per value, =SUMIF(B:B,"Travel",D:D)+SUMIF(B:B,"Meals",D:D), or give it an array constant: =SUM(SUMIF(B:B,{"Travel","Meals"},D:D)).

Why does SUMIFS return 0 with two values for the same column?

SUMIFS uses AND logic, so a row must match every condition. One cell cannot equal two different values, so no row qualifies and the total is 0.

How do I sum with OR logic across two columns?

Use SUMPRODUCT with the tests added and checked with >0: =SUMPRODUCT((((B2:B11="Travel")+(C2:C11="Sales"))>0)*D2:D11). The >0 stops rows that pass both tests from being counted twice.

How do I count with OR criteria instead of summing?

Use the same patterns with COUNTIF: =COUNTIF(B2:B11,"Travel")+COUNTIF(B2:B11,"Meals"), or =SUMPRODUCT((B2:B11="Travel")+(B2:B11="Meals")) for one formula.

Functions used

Related guides

Updated