Setup of summary sheet to graph for dashboard

Andrea Westrich

I currently have a master sheet where we gather weekly data. You can see in the photo that I have helper columns for year and week. These entries are individuals. The other important columns are the Pack name and total columns.

Master sheet

What i need to do is to create a summary sheet that will total the points by "Pack" and week number AND such that I can create a graph that shows total points by "Pack" and week.

This is the desired result

Big question, how can I setup the summary sheet so I can put in formulas to total all the individual entries into totals for each team and by each week AND then create the above graph?

Second question, what is the formula to sumifs, Pack Name in master sheet = pack name in summary sheet AND week in master sheet = week in summary sheet?

This is a challenge, but I'm reaching out to this great group because I haven't created this stuff for 6 months and of course, I need it done by tomorrow afternoon.

Thank you for your ideas!

Hollie Green

The way I did a similar sheet was put the date for each week in 22 in my week over week column those are the dates :) I was actually using count if statements to get a % but the same principle should work for sumifs. would look something like this

=SUMIFS({Total}, {week}, week@row, {Pack}, "fanny pack"). Do this for each of your pack columns changing the pack to the appropriate pack to match that column. You can then create a chart on your dashboard to reference the specific weeks on your summary sheet you want to reference.

01/03/22

01/10/22

01/17/22

01/24/22

01/31/22

02/07/22

02/14/22

02/21/22

02/28/22

03/07/22

03/14/22

03/21/22

03/28/22

04/04/22

04/11/22

04/18/22

04/25/22

05/02/22

05/09/22

05/16/22

05/23/22

05/30/22

06/06/22

06/13/22

06/20/22

06/27/22

07/04/22

07/11/22

07/18/22

07/25/22

08/01/22

08/08/22

08/15/22

08/22/22

08/29/22

09/05/22

09/12/22

09/19/22

09/26/22

10/03/22

10/10/22

10/17/22

10/24/22

10/31/22

11/07/22

11/14/22

11/21/22

11/28/22

12/05/22

12/12/22

12/19/22

12/26/22

Hollie Green

The way I did a similar sheet was put the date for each week in 22 in my week over week column those are the dates :) I was actually using count if statements to get a % but the same principle should work for sumifs. would look something like this

=SUMIFS({Total}, {week}, week@row, {Pack}, "fanny pack"). Do this for each of your pack columns changing the pack to the appropriate pack to match that column. You can then create a chart on your dashboard to reference the specific weeks on your summary sheet you want to reference.

01/03/22

01/10/22

01/17/22

01/24/22

01/31/22

02/07/22

02/14/22

02/21/22

02/28/22

03/07/22

03/14/22

03/21/22

03/28/22

04/04/22

04/11/22

04/18/22

04/25/22

05/02/22

05/09/22

05/16/22

05/23/22

05/30/22

06/06/22

06/13/22

06/20/22

06/27/22

07/04/22

07/11/22

07/18/22

07/25/22

08/01/22

08/08/22

08/15/22

08/22/22

08/29/22

09/05/22

09/12/22

09/19/22

09/26/22

10/03/22

10/10/22

10/17/22

10/24/22

10/31/22

11/07/22

11/14/22

11/21/22

11/28/22

12/05/22

12/12/22

12/19/22

12/26/22

Hollie Green

apologies you should reference your weekending column instead of your week number column that way it will have the date you are looking to have on your chart