Calculate Subtotals and Cumulative Totals

Instructions: If it is easier to work directly in Excel, you can open this in Excel by clicking on the Excel Icon.

Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

Putting subtotals into your dataset can be a useful way of viewing your data in blocks, split and summed by one or more columns that you choose. If you just want to see the totals by these columns then a pivot table is a far quicker and easier way of doing this, but if you have a particular requirement to see all the underlying data as well as the totals by column then subtotals are a useful feature.

There are two main ways of inserting subtotals – one the automatic way through using Excel’s in-built Subtotals feature, and the other through use of the SUBTOTAL function. The main advantage of the SUBTOTAL function is that it allows you to filter your data in any way you want and then see the total for the unfiltered cells (whereas a SUM function for example will still sum cells that aren’t visible). The main advantage of using Excel’s in-built Subtotal Function is that it automatically groups the rows in each block which gives you the expand/collapse icons in the margin.

In this topic we’ll also look at calculating cumulative totals – when viewing data sorted by time for example, this quick technique is useful for things like seeing the payback period (the point at which cumulative cash-flows turn from negative to positive).

Before using the subtotal feature, ensure that your data is sorted by the field(s) you want to split/sum by using the Sort feature located in the Data tab. For example if we wanted to sub-total our data in the Topic Example Workbook by Salesperson then before applying the sub-total we would sort our data by Salesperson. To do this, we click somewhere in the data range, go to Data -> Sort and select ‘Salesperson’ as our Sort By Option, as shown in the screen shot below.

Once we have sorted our data, we can then insert sub-totals to group our data by Salesperson – the sales relating to each salesperson will be shown in a separate block with a sub-total row underneath each one showing the total Quantity sold by that salesperson.

To insert Subtotals, click on Data -> Subtotal and you will be presented with the dialog box shown below.

We select the column we want to sub-total by in the ‘At each change in’ drop-down box. In this case we want to sub-total by Salesperson, so we select this. In the ‘Add subtotal to’ area we tick the column(s) that we want to total – in this case it is just Quantity sold so we select that. We want our sub-total row to show the sum of quantity sold for each salesperson so we ensure that ‘Sum’ is the function being used to subtotal.

Once we click OK, subtotal rows will be inserted into our data (in bold) underneath each change in Salesperson, with a sub-total of total quantity sold by each Salesperson. Our data is also grouped by Salesperson – clicking on the + and – icons in the left hand side allow us to collapse/expand the data to view blocks of data by Salesperson.

To remove the subtotals from our data set, click on Data -> Subtotal again and click ‘Remove All’.

Rather than have Excel insert subtotal rows in your dataset, you can insert rows yourself and use the SUBTOTAL function to sum/aggregate rows above it.

The syntax of the SUBTOTAL formula is =SUBTOTAL(function_num,ref1,ref2..). The function_num is the aggregation function (such as Sum, Max, Min, Average) which we want to use and this is followed by the range we want to aggregate. When you type in =SUBTOTAL( the intelli-sense will present you with a list of all the aggregate functions – for example 9 is the function number for SUM and 4 is the function number for MAX.

So =SUBTOTAL(9,$D$2:$D$10) is the same as =SUM($D$2:$D$10) except for two important differences:

• The SUBTOTAL function will ignore any subtotals in the range it is summing, whereas the SUM function will include them
• When you filter your data, the SUBTOTAL function will return a total for visible rows only whereas the SUM function will include rows which aren’t visible too.

This is a major advantage for SUBTOTAL over using the standard functions such as SUM, COUNT, MAX or MIN. To see this in practice in the Topic Example workbook:

• We insert a subtotal row e.g. in row 5 which sums the sales for Salesperson Andrew using the formula =SUBTOTAL(9,D2:D4) which returns a value of 1500.
• We have a Grand Total row in row 12 also based on a SUBTOTAL formula: =SUBTOTAL(9,$D$2:$D$10).
• Comparing this with our Sum grand total formula in row 13 [=SUM($D$2:$D$10)], our Sum total gives a figure of 5200 whereas our Subtotal grand total gives the correct grand total of 3700. This is because our sum formula includes the 1500 subtotal for Andrew in row 5 whereas the SUBTOTAL function recognizes that this is a subtotal rather than a true value to be summed and hence ignores it.
• If we filter the dataset (e.g. filtering for Salesperson ‘James’) our SUBTOTAL grand total shows only the records relating to James – i.e. a total of 1000, whereas the SUM grand total still includes the unfiltered records and shows the total of 3700.

One type of rows that would be included in both =SUBTOTAL(9,$D$2:$D$10) and =SUM($D$2:$D$10) is hidden rows. If we wanted to exclude hidden rows (as well as cells which aren’t visible in a filtered dataset) we can use the sum subtotal function number 109 instead of 9, making our SUBTOTAL function =SUBTOTAL(109, $D$2:$D$10).

It is sometimes useful to see a running cumulative total of data, particularly when rows are sorted by time where the cumulative data allows you to see the total to date at any point in time.

In the ‘Cumulative Total’ tab in the Topic Example workbook, we want to have a running total of quantity sold in Column E. To do this, we write our formula firstly as a SUM formula containing only a single cell – in cell E2 we write the formula = SUM(D2:D2). This of course just returns 100 – the value in cell D2 – but as this is the first value in our range does represent our cumulative total. In cell E3 we want the total of our values in cells D2 and D3, i.e.=SUM(D2:D3) and in cell E4 we want the sum of cells D2,D3 and D4 i.e. = SUM(D2:D4). The common factor in all of these is the beginning of the SUM function ‘=SUM(D2’.

So if we use absolute references to keep our reference to the first cell reference of the SUM range constant ($D$2 instead of D2) whilst keeping the second cell reference absolute (D2), this ensures that only the ‘to’ range changes as the formula drags down

So our formula in cell E2 is =SUM($D$2:D2) – as this is dragged down it becomes =SUM($D$2:D3) in cell E3 and =SUM($D$2:D4) in cell E4 as required – calculating the total from cell D2 to the cell in column D of the current row.

See the video clip in the Try it out section for an example of how this works

In the Topic Example Workbook, in the ‘SUBTOTAL Feature’ sheet apply a subtotal (using the Subtotal feature in the data tab of the ribbon), summing Quantity Sold for each Salesperson.

In the ‘SUBTOTAL Formula’ sheet:

• Insert a row underneath Row 4 which will become a sub-total row for Andrew (Sum of Quantity Sold). Use the SUBTOTAL function to write this, putting the subtotal result in what is now cell D5
• In what is now cell D13 (Grand Total using Sub-Total), use a SUBTOTAL function to sum cells D2:D10
• In cell D14, use a SUM function to sum cells D2:D10. This should give a different answer to the sum using SUBTOTAL function because the SUBTOTAL sum should ignore other the other SUBTOTAL function whereas the SUM function includes it.
• In Cell M4, write a SUBTOTAL sum function to sum Cells D2:D10 but using a Sum parameter that excludes hidden/cells which aren’t visible. This should give the same result as the SUBTOTAL sum in cell D12
• Hide Row 10 (or filter the list and exclude a row); the SUBTOTAL sum function in cell M4 should now give a different result to that in cell D12
  In the Cumulative Totals’ sheet, create a cumulative total of quantity Sold (Column D) in Column E

A demonstration is shown in the video clips below. Resist the temptation to view the answers file below until you have tried this!
Subtotal Formula

Cumulative Total

Once you have completed the ‘Try it Out’ exercise, the completed Excel file should look like this:

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.