SUMIFS Function: Introduction

What does it do?

Sums multiple criteria

Formula breakdown:

=SUMIFS(Sum_Range,Criteria_Range1,Criteria1,Criteria_Range2,Criteria2…)

What it means:

=SUMIFS(Return the Sum from this Range,Evaluate this Range,With this Criteria,Evaluate that Range,With that Criteria…)

---

The SUMIFS function allows you to Sum multiple criteria.

For example, you can select one Sales Rep from a list of Sales Reps and select one Region from a list of Regions and return the Sum of those arguments from a Sales list.  See how easy it is…

 

We want to get the sum of the sales amounts for John in the North Region.

STEP 1: We need to enter the SUMIFS function in a blank cell:
=SUMIFS(

 

STEP 2: The SUMIFS arguments:

Range

What is your range that contains the source data to add together?

Highlight the column that contains the sales data
=SUMIFS(D15:D23,

Criteria_range1

For the first criteria, which range contains the source data?

Let us target the sales rep first, so select that column
=SUMIFS(D15:D23, B15:B23,

Criteria1

What is your filtering criteria?

We want to filter for sales amounts of John, so type in “John”
=SUMIFS(D15:D23, B15:B23, "John",

Criteria_range2

For the second criteria, which range contains the source data?

Let us now target the Region, so select that column
=SUMIFS(D15:D23, B15:B23, "John", C15:C23,

Criteria2

What is your filtering criteria?

We want to filter for sales amounts of the North Region, so type in “North”
=SUMIFS(D15:D23, B15:B23, "John", C15:C23, "North")

Just like that, Excel has selectively found the values and summed them together!