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

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Create a Random List with Excel’s RAND Formu... What does it do? Gives you a random number between 0 and 1 Formula breakdown: =RAND() What it means: =RAND(Will automatically choose a random number between 0 and 1) Excel is able to do a lot of things that most users are unaware of!  One thing that amazes m...
Advanced SUMPRODUCT Function: Conditional Sum Another great way that you can use the SUMPRODUCT function is to create a conditional sum criteria. For example, you may want to find out how much sales were made above the $3,000 transactional level.  See how easy it is with this quick SUMPRODUCT example. DOWNLOAD WORKBOOK...
Evaluate Formulas Step By Step in Excel This is one of the coolest tricks I have seen in Excel, as there are countless times wherein I had a hard time understand formulas. Especially long and complex ones! Excel provides the way to evaluate your formula, and break it down step by step so that you can understand it! ...
Advanced Sumproduct Function: Sum the Top 3 Sales ... This is probably the most advanced level a SUMPRODUCT function can reach and that is by including a nested array formula. In our example below we want to return the 3 Largest values from the North region and sum them up.  As we are asking our formula to perform multiple calcul...