Friday Challenge – Advanced Excel Summation Skills

Excel greek summation symbol
Excel greek summation symbol

 

Excel greek summation symbol
Excel greek summation symbol

This is a fun skills challenge to make sure you are well rounded.  What is the SUM of your Excel Game?  You will find out today.

 

The Data

For today’s challenge, we will see if you can sum a range of data points.

The data is pretty simple, and here is a sample:





Date Store ID Sales
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
6/19/2016 Store 6 318
8/18/2016 Store 3 518
9/11/2016 Store 5 732
6/7/2016 Store 8 266
9/3/2016 Store 7 77
8/31/2016 Store 8 890
5/3/2016 Store 9 197

There are 10 stores worth of data for 150,000 data points with sales per day per store.

You can download the sample data file here: Friday-Challenge-Many-Sums-Sample-Data.xlsx

The Challenge

1) SUMIFS

Fill in the table of 2016 sales by month by store using only 1 SUMIFS Formula

This may be the easier of the 3 tests, but it does take a little thinking on exactly how it should be created so that you are only using one formula.  If you figure it out, put your grand total for 2016 in the comments below as well as the formula you used.

SUMIFS 1/1/2016 2/1/2016 3/1/2016 4/1/2016 5/1/2016 6/1/2016 7/1/2016 8/1/2016 9/1/2016 10/1/2016 11/1/2016 12/1/2016
Store 1
Store 2
Store 3
Store 4
Store 5
Store 6
Store 7
Store 8
Store 9
Store 10

2) SUMPRODUCT

Fill in the table of 2016 sales by month by store using only 1 SUMPRODUCT Formula

This one is more difficult and will take some knowledge of the formula.  There are many ways to write this formula using SumProduct and if you get the right answer, it probably doesn’t matter if you have the same formula that I created.  If you figure it out, your grand total should match the SUMIFS function results so if it does, then just put the formula you used in the comments below.

SUMPRODUCT 1/1/2016 2/1/2016 3/1/2016 4/1/2016 5/1/2016 6/1/2016 7/1/2016 8/1/2016 9/1/2016 10/1/2016 11/1/2016 12/1/2016
Store 1
Store 2
Store 3
Store 4
Store 5
Store 6
Store 7
Store 8
Store 9
Store 10

3) PIVOT TABLE

Create a Pivot Table that only shoes the 2016 sales by month by store.

Same format as you see above, however, do it with a Pivot Table.  This is a bit of a challenge if you are not very familiar with Pivot Tables, but it can be done even though the data is in Days and not Months.  Of course this is easier if you add helper columns to the data, but to get a top grade, you should do it without any helper columns.

To recap, if you use helper columns, it will work, but will make your spreadsheet bigger than it needs to be, so lets keep the file small as we can.

Once again, if you figure this one out too, then your grand total should match the SUMPRODUCT and SUMIFS results.  So if it does, then put how you did it in the comments below.





 

Scoring Your Results (Don’t worry, I made this up so not completely scientific):

Question 1 only: D

Question 1 and 3 with helper columns: C





If you found the website and tutorials helpful, please consider donating to keep the lights on.

Donate with PayPal here:





Question 1 and 3 without helper columns: B-

Question 1, 2 and 3 with helper columns: B

Question 1, 2 and 3 without helper columns: A

Question 3 only with helper columns: D

Question 3 only without helper columns: D+

4) Extra Credit

If you dare. (This will move you up one grade or if you are already at an A it will give you a + to your grade)





This is only for the bold and the brave.  I am no Excel Array Formula Expert, but I am sure that it can be done.

Same format as above.  This one is tricky and once you learn Array’s, you probably won’t stop using them for your Excel Chart data series and other calculations.

5)  Extra Extra Credit

GURU’s need apply.  (You can skip a grade in school)

Identify any other way to do this conditional summation by store by month for 2016 that is not any of the techniques used above.  This is a really tough one.  I don’t have an answer yet for this one, so not sure if it is possible.

Video Review of the Excel Challenge

Also, if you want to make sure you get the next Friday Challenge in your mailbox to test out your Excel prowress, make sure you subscribe below.

Good luck!

Steve=True





4 COMMENTS

  1. Hi Solve this Question
    SumProduct = =SUMPRODUCT(($C$2:$C$150000)*($B$2:$B$150000=$E3)*($A$2:$A$150000=F$2))

    Sumifs = =SUMIFS($C$2:$C$150000,$B$2:$B$150000,$E16,$A$2:$A$150000,F$15)

    I hope this is correct Answer

  2. =SUMPRODUCT((F$2=$A$2:$A$150000)*($E3=$B$2:$B$150000),($C$2:$C$150000))
    =SUMIFS($C$2:$C$150000,$A$2:$A$150000,F$15,$B$2:$B$150000,$E16)

LEAVE A REPLY

Please enter your comment!
Please enter your name here