How to use SUMIFS in Excel

SUMIFS adds numbers from a sum range when the corresponding rows meet every criterion you specify. Use it when you need a total across matching rows rather than a value from a single record.

SUMIFS syntax

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

• sum_range: the cells containing the numbers to add.
• criteria_range: the cells Excel checks for a condition.
• criteria: the value or condition a row must meet.

Sum sales that meet two conditions

Suppose column A contains names, column B contains departments, and column C contains sales amounts. G2 contains the name and H2 contains the department. This formula totals sales for rows matching both:

=SUMIFS(C2:C100, A2:A100, G2, B2:B100, H2)

SUMIFS checks each row in A2:A100 against G2 and each row in B2:B100 against H2. It adds the corresponding values from C2:C100 only when both conditions are met. The sum and criteria ranges should have matching dimensions.

Lookup or sum?

Use XLOOKUP when you need a value from a matching row. Use SUMIFS when multiple matching rows should contribute to one total. To count matching rows without adding values, use COUNTIFS.

Continue to the Excel lookup learning path or browse all Excel tutorials.