Excel: SUMIF multiple columns with one or more criteria

This tutorial demonstrates several methods for summing multiple columns in Excel based on single or multiple criteria. Conditional summing is straightforward when values are in a single column, but summing across multiple columns presents a challenge because SUMIF and SUMIFS require equally sized ranges. This tutorial offers workarounds.

Summing Multiple Columns with One Criterion

Consider a table of monthly sales with multiple entries for the same product:

To find the total sales for a specific item, a naive approach like =SUMIF(A2:A10, "apples", C2:E10) fails because the sum range is incorrectly interpreted.

Method 1: Helper Column

The simplest solution is to add a helper column (e.g., column F) summing each row's sales: =SUM(C2:E2). Then, use =SUMIF(A2:A10, I1, F2:F10) where I1 contains the desired item. This works because the sum and criteria ranges are now equal in size.

Method 2: Multiple SUMIF Formulas

Sum the results of individual SUMIF formulas for each column:

=SUM(SUMIF(A2:A10,H1,C2:C10), SUMIF(A2:A10,H1,D2:D10), SUMIF(A2:A10,H1,E2:E10))

or

=SUMIF(A2:A10, H1, C2:C10) SUMIF(A2:A10, H1, D2:D10) SUMIF(A2:A10, H1, E2:E10)

This becomes cumbersome with many columns.

Method 3: Array Formula

Use an array formula: =SUM((C2:E10)*(--(A2:A10=H1))). In older Excel versions (pre-365/2021), press Ctrl Shift Enter. This multiplies the sales data by an array of 1s and 0s based on the criterion, summing only the relevant values.

Method 4: SUMPRODUCT Formula

A simpler alternative is the SUMPRODUCT function: =SUMPRODUCT((C2:E10) * (A2:A10=H1)). This avoids the need for array formula entry.

Summing Multiple Columns with Multiple Criteria

The above methods extend to multiple criteria.

Method 1: Multiple SUMIFS Formulas

Use multiple SUMIFS, one for each column:

=SUMIFS(C2:C10, A2:A10, H1, B2:B10, H2) SUMIFS(D2:D10, A2:A10, H1, B2:B10, H2) SUMIFS(E2:E10, A2:A10, H1, B2:B10, H2)

Method 2: Array Formula (Multiple Criteria)

Extend the array formula to include additional criteria:

=SUM((C2:E10) * (--(A2:A10=H1)) * (--(B2:B10=H2)))

Remember Ctrl Shift Enter for older Excel versions.

Method 3: SUMPRODUCT Formula (Multiple Criteria)

The SUMPRODUCT function simplifies this:

=SUMPRODUCT((C2:E10) * (A2:A10=H1) * (B2:B10=H2))

This tutorial provides multiple solutions for efficient conditional summing in Excel, catering to different Excel versions and data complexities. A practice workbook is available for download.

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