Excel SUMIF with multiple OR criteria

This article explores three methods for summing numbers in Excel based on multiple OR conditions in different columns. The standard SUMIFS function uses AND logic, so alternative approaches are necessary for OR logic.

Method 1: SUMIF SUMIF

This straightforward method handles each condition separately and adds the results. For example, to sum sales for "Apples" and "Lemons" in column B where product names are in column A:

=SUMIF(A2:A10, "apples", B2:B10) SUMIF(A2:A10, "lemons", B2:B10)

Alternatively, you can reference cells containing the criteria:

=SUMIF(A2:A10, E1, B2:B10) SUMIF(A2:A10, E2, B2:B10) (where E1 and E2 contain "apples" and "lemons" respectively).

This approach becomes cumbersome with many criteria.

Method 2: SUMIF with Array Constant

A more compact solution uses an array constant within the SUMIF function:

=SUM(SUMIF(A2:A10, {"Apples","Lemons","Oranges"}, B2:B10))

This sums sales for Apples, Lemons, and Oranges. For flexibility, reference a cell range containing the criteria:

=SUM(SUMIF(A2:A10, E1:E3, B2:B10)) (where E1:E3 contains the criteria).

In Excel 365 and later, this is a regular formula. In older versions, enter it as an array formula (Ctrl Shift Enter).

Method 3: SUMPRODUCT and SUMIF

For a non-array formula using criteria from separate cells, use SUMPRODUCT:

=SUMPRODUCT(SUMIF(A2:A10, E1:E3, B2:B10)) (where E1:E3 contains the criteria).

SUMPRODUCT inherently handles arrays, eliminating the need for Ctrl Shift Enter.

Wildcards with SUMIF

SUMIF supports wildcards. To sum sales for all "Apples" and "Bananas" variations:

=SUM(SUMIF(A2:A10, {"*Apples","*Bananas"}, B2:B10))

Or, using cell references and wildcards within those cells:

This article provides multiple solutions for summing with multiple OR conditions in Excel, offering flexibility depending on your Excel version and preference. A practice workbook is available for download. [SUMIF multiple criteria (.xlsx file)]

The above is the detailed content of Excel SUMIF with multiple OR criteria. For more information, please follow other related articles on the PHP Chinese website!