What does it do?

Sums multiple criteria

Formula breakdown:


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 Formula


STEP 2: The SUMIFS arguments:


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

Highlight the column that contains the sales data


Sumifs Formula


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,

Sumifs Formula


What is your filtering criteria?

We want to filter for sales amounts of John, so type in “John”

=SUMIFS(D15:D23, B15:B23, “John”,

Sumifs Formula


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,

Sumifs Formula


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”)

Sumifs Formula

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

Sumifs Formula

How to Use the SumIfs Formula in Excel

SumIfs - Intro


How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel



If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn


Share on Google+


Related Posts

Two Way Lookup Using The SUM Intersect Function There are various lookup functions that bring back values from a range of data like the VLOOKUP, INDEX, INDEX-MATCH and OFFSET functions, just to name a few. There is another lookup formula that you can use that will return the intersection of two Named Ranges! This is done...
Sum the Last 7 Transactions with the Offset Functi... What does it do? It returns a reference to a range, from a starting point to a specified number of rows, columns, height and width of cells Formula breakdown: =OFFSET(reference, rows, columns, , ) What it means: =OFFSET(start in this cell, go up/down a number of ro...
Jump To A Cell Reference Within An Excel Formula When writing, editing or auditing Excel formulas you will come across a scenario where you want to view and access the referenced cells within a formula argument. This is helpful if you want to check how the formula works or to make any changes to the formula. There is ...
RANDBETWEEN Function   What does it do? Returns a random integer number between the numbers you specify. Formula breakdown: =RANDBETWEEN(bottom number, top number) What it means: =RANDBETWEEN(10, 10000) I use the RANDBETWEEN function all the time whenever I need to cr...