In this blog post, we want to learn how to use one of the most powerful but rarely-known technique in Microsoft Excel called Group Sheet. We will use the technique with SUMIFS function to pull across the Total sales for Account Managers based on Quarter 1, Quarter 2, Quarter 3 and Quarter 4 for years 2013 to 2016. We will run the Group Sheet with SUMIFS function in Sales for 2013 which will pull the data from the Data sheet (source data) and auto-extend to Sales for 2014 to 2016. Let’s get started.
The caption below is the source data that we want to work with
The captions below are the Sales for 2013:2016 sheets
Note that Sales for 2013 to 2016 sheets have the exact structure which is very important for the Group Sheet with SUMIFS to be possible. In addition, in all the sheets, there are dates that makes up a quarter above each column. These dates are required to return the Total sales for each Quarter and Account Manager respectively.
To perform the main operation:
- Click on Sales for 2013 sheet and select cell B7
- Hold down the SHIFT key and select the Sales for 2016 sheet. This automatically group all the sheets
Note that the Source Data has been stored in Excel Table and named ranges applied
- In cell B7, execute the formula: =SUMIFS(Total,Account_Manager,$A7,Order_Date,”>=”&B$4,Order_Date,”<=”&B$5).
- Execute CTRL + Enter
- Using the fill handle, drag the formula across to the right and down.
The caption below is the Total Sales Report for 2013
- Finally, right-click on any of the grouped sheet and at the bottom, select Ungroup sheets
To confirm the Sales Report for Sales 2014, 2015 and 2016 sheet, click on sheets:
The group sheet and SUMIFS function worked perfectly.