SUMIFS multiple criteria

Intermediate

In Excel, you often need totals that match more than one condition (for example: a region and a product line). SUMIFS is the go-to function for this.

SUMIFS adds up a range, but only for rows that meet all criteria you specify.

The pattern looks like:

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)

Your task:

  1. Look at the transactions table in A1:F13.
  2. Use the filter inputs on the right:
    • Region is in H2
    • Category is in H3
  3. In cell H4, calculate the total Sales for transactions where:
    • Region matches H2
    • Category matches H3

Use SUMIFS so the total updates if someone changes the filter values.

Need some help?

Hint 1

Your sum range is the Sales column (F). Each criteria range should be the same height as that Sales range.

Hint 2

You need two criteria pairs: Region (column B) equals H2, and Category (column C) equals H3.

Hint 3

Double-check that your ranges start on row 2 and end on row 13 so you only include the transaction rows.

Related function(s)
SUMIFSSUMIFCOUNTIFSAVERAGEIFS
More exercises
Dynamic date range SUMIFSIntermediate
AVERAGEIFS multiple criteriaIntermediate
COUNTIFS multiple criteriaIntermediate
Year-over-year comparisonAdvanced
Back to exercises overview

Exercise