Excel SUMPRODUCT Multiple Criteria

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 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 this function but when you master it, it opens up another Excel world!

DOWNLOAD EXCEL WORKBOOK

 

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

Excel SUMPRODUCT Multiple Criteria

 

STEP 1: We need to enter the SUMPRODUCT function:

+SUMPRODUCT(

Excel SUMPRODUCT Multiple Criteria

 

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

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

Excel SUMPRODUCT Multiple Criteria

Create the criteria for the Region “North”:

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

Excel SUMPRODUCT Multiple Criteria

Create the criteria for the Quarter “1”:

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

Excel SUMPRODUCT 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)

Excel SUMPRODUCT Multiple Criteria

Once your formula is complete, you can see that it magically calculated the sum of the matching values!

Excel SUMPRODUCT Multiple Criteria

 

Excel SUMPRODUCT Multiple Criteria

Helpful Resource:

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

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

INDEX-MATCH Maximum Sales The INDEX-MATCH formula cannot only look up values to the left and right of your data but its versatility means that it can also lookup the largest values within an array.Say that you had a list of Sales Reps in one array and their corresponding Sales Values in another array....
Concatenate With A Line Break Excel's CONCATENATE functions joins two or more text strings into one string.  The item can be a text value, number, or cell reference.If you add a double quotation with a space in between " " then this will add a space between the texts selected on either side.You ca...
Top Excel Formulas & Function Examples To Get... Below you will find many Excel formula examples for key functions like VLOOKUP, INDEX, MATCH, IF, SUMPRODUCT, AVERAGE, SUBTOTAL, OFFSET, LOOKUP, ROUND, COUNT, SUMIFS, ARRAY, FIND, TEXT, and many more.Click on any Excel formula link below and it will take you to the free e...
Cleaning Data with Excel’s TRIM Formula What does it do?Removes unneeded spaces in your text, except single spaces in between wordsFormula breakdown:=TRIM(text)What it means:=TRIM(text that you want extra spaces to be removed)In the quest for cleaner data, one of the common scenarios is remo...