Advanced Excel Summation Skills Using SUMIFS and SUMPRODUCT

In this post, I will show you 2 other techniques that you can use to evaluate criteria and then create a sum of data based on that criteria in Excel.  Two posts ago, we showed presented with a challenge of summing 150,000 data points in many different ways.  If you want to try it for yourself and then come back and check your work, download the data here:  Friday Challenge – Advanced Excel Summation Skills

Now as I showed you in the last video, the fastest way is to use a Pivot Table.  You can see that post here: Advanced Excel Summation Using A Pivot Table

This tutorial will show you the skills to create a sum formula for 3 different criteria checks on a large set of data using the SUMIFS as well as the SUMPRODUCT functions.

SUMIFS Function Answer

If you don’t want to setup all of the overhead of a Pivot Table in your Excel spreadsheet, you may want to use the SUMIFS function to find the answers.  This is also very useful when creating data sets for Excel Charts and Dashboards.  So you should really get to know these techniques.

For the Sum IFS formula in this context we need to do the following formula in the sample data you can put in cell F16:

=SUMIFS($C$2:$C$150000,$B$2:$B$150000,$E16,$A$2:$A$150000,”>=”&F$15,$A$2:$A$150000,”<“&G$15)

Let’s break down the SUMIFS formula for this solution.

Formula Help Formula  Description
Function Name =SUMIFS( This is the beginning of the formula starting with the SUMIFS function
Sum Range $C$2:$C$150000, Select the Sales Data Range that will be used to sum if the following criteria is TRUE
Criteria Range 1,

Criteria 1

$B$2:$B$150000,$E16, Select the StoreID Data Range that will be used as criteria to check if the cell in the current row in the same column E matches.  Make sure you put a ‘$’ in front of the E so that the column is an Absolute Reference.
Criteria Range 2,

Criteria 2

$A$2:$A$150000,”>=”&F$15, Select the Date Data Range that will be used as criteria to check if the cell in the current column in the same row 15 matches.  Make sure you put a ‘$’ in front of the 15 so that the column is an Absolute Reference.
Criteria Range 3,

Criteria 3

$A$2:$A$150000,”<“&G$15) Select the Date Data Range that will be used as criteria to check if the cell in the adjacent column in the same row 15 matches.  Make sure you put a ‘$’ in front of the 15 so that the column is an Absolute Reference.

You should now be able to copy this formula into the entire range (F16:G26) of the sample spreadsheet since we formatted the data with absolute and relative references.

Also notice in the sample file that we have a helper column that we can hide for the start of 2017.  This is needed for the formula so that we can copy it across the range of cells.  Without this cell, we would have to modify the final column formulas by adding the month header data + 31 days instead of the month header data in column R.

SUMPRODUCT Function Answer

Well you may ask what is a SUM PRODUCT Function and why would I want to use it if SUMIF works so well?  Well that is a good question.  Back in the day 🙂 (that mean Excel 2003), you didn’t have a SUMIFS Formula.  Therefore, this was a great trick/work around that you had to use if you didn’t want to use Array formulas (Come back for the next post where I will detail how to do this with an Array formula).  In Excel 2007, Excel 2010, Excel 2013 and Excel 2016, it is not needed as much.  HOWEVER, you should really learn how to use this function.  It acts like an array formula, but doesn’t require CTRL+SHIFT+ENTER to activate it.  Also, if you need other criteria/validation checkers that SUMIFS, SUMIF, COUNTIF, COUNTIFS, AVERAGEIF and AVERAGEIFS functions don’t do.  I can’t think of one off the top of my head, but I am sure there is a good use case.

For the SUMPRODUCT formula in this context we need to do the following formula in the sample data you can put in cell F3:

=SUMPRODUCT(($C$2:$C$150000)*($B$2:$B$150000=$E3)*($A$2:$A$150000>=F$2)*($A$2:$A$150000<G$2))

Let’s break down the SUMPRODUCT formula for this solution.

Formula Help Formula  Description
Function Name =SUMPRODUCT( This is the beginning of the formula starting with the SUMPRODUCT function
Sum Range ($C$2:$C$150000)* Select the Sales Data Range that will be used to sum if the following criteria is TRUE
Criteria Range 1 =

Criteria 1

($B$2:$B$150000=$E3)* Select the StoreID Data Range that will be used as criteria to check if the cell in the current row in the same column E matches.  Make sure you put a ‘$’ in front of the E so that the column is an Absolute Reference.
Criteria Range 2 =

Criteria 2

($A$2:$A$150000>=F$2)* Select the Date Data Range that will be used as criteria to check if the cell in the current column in the same row 2 matches.  Make sure you put a ‘$’ in front of the 2 so that the column is an Absolute Reference.
Criteria Range 3 =

Criteria 3

($A$2:$A$150000<G$2)) Select the Date Data Range that will be used as criteria to check if the cell in the adjacent column in the same row 2 matches.  Make sure you put a ‘$’ in front of the 2 so that the column is an Absolute Reference.

Notice that this looks almost exactly like the SUMIFS formula except that instead of commas, we are structuring the SUMPRODUCT formula with several Boolean conditional formulas.  Here is how it works.

SUMPRODUCT will create 150,000 data points for the sales data.

Then it compares the Store IDs and if they match, then it will put a 1 in the matching cell data for all 150,000 data points.  If it doesn’t match, then it puts a 0 in the formula.

Then it compares the Date and checks if it is greater than or equal to the column date and if that is are true, then it will put a 1 in the matching cell data for all 150,000 data points.  If it is not greater than or equal to the column date, then it puts a 0 in the formula.

Then it compares the Date and checks if it is less than the adjacent column date and if that is are true, then it will put a 1 in the matching cell data for all 150,000 data points.  If it is not less than the adjacent column date, then it puts a 0 in the formula.

By multiplying sales data a series of 0’s or 1’s will either result in zero or in the value of matching sales.  Here is a sample of how it works on a small set of data.

For the Month of June column, just looking at the first 8 rows of data, here is what the formula values would look like:

6/3/2016 Store 2 385
7/7/2016 Store 3 190
6/1/2016 Store 5 189
9/1/2016 Store 7 676
8/1/2016 Store 9 370
7/16/2016 Store 4 979
7/25/2016 Store 6 402
6/25/2016 Store 1 818

Row 1 =SUMPRODUCT(385*0*1*1) = 0 +

Row 2 =SUMPRODUCT(190*1*0*0) = 0 +

Row 3 =SUMPRODUCT(189*0*1*1) = 0 +

Row 4 =SUMPRODUCT(676*1*0*0) = 0 +

Row 5 =SUMPRODUCT(370*1*0*0) = 0 +

Row 6 =SUMPRODUCT(979*1*0*0) = 0 +

Row 7 =SUMPRODUCT(402*1*0*0) = 0 +

Row 8 =SUMPRODUCT(818*1*1*1) = 818 =

June 2016 Store 1 Total = 818

One other thing to know…SUMPRODUCT on large sets of data will be more resource intensive than SUMIFS.  Not overly so, but you may notice it on large data sets or weaker computers.

 

Also discussed above, you can learn more about Absolute and Relative References here: Learn More About Absolute Reference and Relative References

Video Demonstration

Comeback for the next post to learn how do complete the same operation with an Excel ARRAY formula.  Also, if you haven’t already, please consider subscribing to the email list so that you get the next post delivered directly into your inbox below.

Steve=True