Overview

When building reports, users will oftentimes bring in large amounts of information by using Range Lists. Users will typically want to be able to add totals or, in the casing of grouped data, subtotals, to give the person using the report an at-a-glance summary of how the numbers are looking. 

By leveraging standard Excel functionality, adding sums and subtotals is simple and is covered in detail below.

Details

To add sums and subtotals into reports that use Range Lists, the user will use standard Excel functionality in the form of the SUM and SUBTOTAL function.

The main difference between the two is that the SUM function will total up all of the numbers that are included in the specified range, while the SUBTOTAL function will ignore other SUBTOTAL functions in the specified range. If your report has multiple levels of grouping, then using a SUBTOTAL function can be useful because it allows the user to easily add both subtotals and grand totals without having the numbers counted multiple times.

Using the SUM Function in Non-Grouping Reports

Working with Rows Range Lists

If the report does not include grouping then, in many cases, a SUM function will work fine. The user would identify the range of the Range List (colored in green below), and a spacer row below the Range List (the yellow row) and then add a SUM function that includes both the Range List field to sum up along with the empty cell in the spacer row. 

The SUM function, as shown in the red box above, would be =SUM(C2:C3) to cover both rows. The reason that the spacer row needs to be included is because, as the Range List expands (in this example by listing out Bill-to Customer No's), it will insert new rows between Row 2 and Row 3 to account for all of the customer numbers that it needs to insert. By having the SUM() function sit on both sides of this area where the rows will be inserted, the SUM() function will also be automatically expanded to encompass the entire area when the report is run. Because of this, whether the report returns 10 customers numbers or 1,000 customer numbers (or more) the SUM function will always grow to be the correct size.

Working with Columns Range Lists

If the area to be summed up is based on a Columns Range List instead of a Rows Range List the same concept applies with having to include a spacer area, the area will just be the column to the right of the Columns Range List.

In the example below, there is a Columns Range List that covers cells C1:C2 that will list out months. If the user runs the report for a year, twelve columns would be created, one for each month in the year. If the user wanted to include a total off to the right, they would determine the region of the Columns Range List (colored in green below), and a spacer column next to the Range List (the yellow column) and then add a SUM function that includes both the Range List field to sum up along with the empty cell in the spacer column. 

The SUM function, as shown in the red box above, would be =SUM(C2:D3) to cover both columns. This would ensure that when the columns are inserted for months when the report is run the SUM function will adjust in size proportionately.

Using the SUBTOTAL Function in Grouping Reports

When building reports that include grouping, such as first creating a list of salespeople and then, for each salesperson, returning a list of all customers that had sales for that salesperson, if the user inserts SUM functions to total up the numbers they may notice that their totals are doubled up. This is because the grand total, for example, will include all the numbers for each customer but will also include the salesperson subtotals as well. 

The example below illustrates what this looks like when the SUM function is used with grouping. The customer had $50 worth of sales, so the associated salesperson also had $50 worth of sales. You notice, however, that the grand total is incorrectly showing $100 because it includes both the $50 for the customer and $50 for the salesperson, even though this is the same $50.

Once simple solution to this is the leverage the SUBTOTAL function in Excel. The SUBTOTAL function is meant to behave in a similar way to Excel functions such as COUNT and SUM, but the SUBTOTAL function will ignore other SUBTOTAL functions. This solves the issue of double counting as mentioned in the paragraph above. 

In the example below you can see that when the SUBTOTAL function is used for both the salesperson total and the grand total that the grand total is now correctly showing $50. 

You will notice in the SUBTOTAL function above that there is a "9" placed in the function prior to the cell reference range; this is telling the SUBTOTAL to sum up the numbers. The SUBTOTAL function is a versatile function will many uses and more details on it can be found on Microsoft's site here: Subtotal Function Reference.