Pinterest Pixel

Advanced Excel SUMPRODUCT Function: Sum Multiple Criteria

What does it do? It returns the sum of multiple criteria from the corresponding ranges or arrays.... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

What does it do?

It returns the sum of multiple criteria from the corresponding ranges or arrays.

Formula breakdown:

=SUMPRODUCT((array 1 criteria) * (array2 criteria) * array values)

What it means:

=SUMPRODUCT((find my criteria in this array) * (find my criteria in that array) * return the values from the values array)


The SUMPRODUCT function is my favorite Excel function by a stretch!

You can create some powerful calculations with the EXCEL SUMPRODUCT function by creating a criteria for a selected array.

For example, you can see how much sales your sales rep did in a particular region and for a particular quarter without having to create a Pivot Table.

It takes some practice to get comfortable with Excel SUMPRODUCT multiple criteria function but when you master it, it opens up another Excel world!

Let’s understand how to use this function to sumproduct multiple columns.

Advanced Excel SUMPRODUCT Function: Sum Multiple Criteria | MyExcelOnline

download excel workbook Advanced-Sumproduct_Multiple-Criteria.xlsx

 

 

In our example, we want to get the total sales of John in the North Region in Q1:

Advanced Excel SUMPRODUCT Function: Sum Multiple Criteria

STEP 1: We need to enter the SUMPRODUCT Excel function:

+SUMPRODUCT(

Advanced Excel SUMPRODUCT Function: Sum Multiple Criteria

 

STEP 2: Create the criteria for the Sales Rep “John”:

+SUMPRODUCT((B15:B23=”john”)*

Advanced Excel SUMPRODUCT Function: Sum Multiple Criteria

Create the criteria for the Region “North”:

+SUMPRODUCT((B15:B23=”john”)*(C15:C23=”north”)*

Advanced Excel SUMPRODUCT Function: Sum Multiple Criteria

Create the criteria for the Quarter “1”:

+SUMPRODUCT((B15:B23=”john”)*(C15:C23=”north”)*(E15:E23=1)*

Advanced Excel SUMPRODUCT Function: Sum Multiple Criteria

Create the sum array to total the values of the Sales column:

+SUMPRODUCT((B15:B23=”john”)*(C15:C23=”north”)*(E15:E23=1)*D15:D23)

Advanced Excel SUMPRODUCT Function: Sum Multiple Criteria

Once your formula is complete, you can see that it magically calculated the sum of the matching values! So, this is how you can use sumproduct with multiple criteria.

Advanced Excel SUMPRODUCT Function: Sum Multiple Criteria

Further Learning:

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

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!