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
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
