Excel: Subtotals for your cal­cu­la­tions

Microsoft’s spread­sheet programme lets you produce com­pre­hens­ive tables and complex cal­cu­la­tions. Functions in Excel can assist in many situ­ations and are rarely affected by the design and ap­pear­ance of your tables. Want to hide certain cells for clarity? No problem: The functions still count the filtered values. However, you may not always want that. If you want your cal­cu­la­tions to react to filters and hidden cells, the SUBTOTAL function is quite useful. It combines multiple cal­cu­la­tion op­er­a­tions and gives you more control over the cells Excel includes.

You may not always want to see all cells, es­pe­cially if you are working with large volumes of data. You can hide rows or certain values in order to make it easier to work with com­pre­hens­ive tables. This reduces tables to a size that only shows the essential data, which makes it far simpler to work with them. However, the hidden values are not lost – they are still part of the worksheet and are therefore still used for cal­cu­la­tion functions. That is a major advantage of Excel.

But what is generally an advantage can also be a problem in some situ­ations. For example, if you want the Excel functions to react to what is currently visible and adapt your results ac­cord­ingly, you can’t do so with the normal cal­cu­la­tion methods. You would have to create a new table that does not include the values that are filtered out. The SUBTOTAL function gives you an al­tern­at­ive: You can configure the function to prevent hidden cells being included in the cal­cu­la­tion.

SUBTOTAL combines no less than eleven different functions: Users choose which cal­cu­la­tion method is to be used in the function, and whether to include hidden cells or not. The function does not include values currently hidden by a filter in the cal­cu­la­tions. Ac­cord­ingly, SUBTOTAL is ideal as an ad­di­tion­al result to com­ple­ment the final result. Comparing the two values often gives ad­di­tion­al in­form­a­tion.

The­or­et­ic­ally, the SUBTOTAL only needs two items of in­form­a­tion. First, specify which cal­cu­la­tion is to be performed. The second and all following arguments contain in­form­a­tion on the range of cells used. One entry is required, but you can add up to 254 ranges to the formula.

The first parameter transfers the prop­er­ties of another function to the SUBTOTAL function – Excel provides a list of functions. You enter the cor­res­pond­ing number and SUBTOTAL performs the cor­res­pond­ing cal­cu­la­tion. However, there are two numbers for every function. Values between 1 and 11 configure SUBTOTAL to include hidden values. If you enter 101 to 111, the function ignores hidden cells in the specified range. Here is an overview.

Hidden cells remain part of the cal­cu­la­tion:

• 1: AVERAGE
• 2: COUNT
• 3: COUNTA
• 4: MAX
• 5: MIN
• 6: PRODUCT
• 7: STDEV
• 8: STDEVP
• 9: SUM
• 10: VAR
• 11: VARP

Hidden cells are not included in cal­cu­la­tions:

• 101: AVERAGE
• 102: COUNT
• 103: COUNTA
• 104: MAX
• 105: MIN
• 106: PRODUCT
• 107: STDEV
• 108: STDEVP
• 109: SUM
• 110: VAR
• 111: VARP

Cal­cu­lat­ing a sum is the best example of the SUBTOTAL function. Besides the final result including all values, the function also lets you calculate only the values not affected by filters or hidden values.

In this example, we take values from two columns and add them. As we entered 109 for the first para­met­ers, only values that are currently visible are included.

The same settings work with other cal­cu­la­tion methods. For example, you can calculate the average.

You can also combine SUBTOTAL with other functions. Ac­cord­ingly, the result of the function can be passed on directly to other cal­cu­la­tions – without requiring auxiliary cells. For example, you can add up multiple subtotals.

It can also be helpful to combine SUBTOTAL with the IF function. You can set up a cell in which you specify the required cal­cu­la­tion type – for example using a drop-down menu. While that makes the formula itself somewhat longer, once you write it you never have to edit it af­ter­wards. For example, if we assume that you want to switch between the three cal­cu­la­tions SUM, COUNT and AVERAGE regularly.

Use Auto­Fil­ter to set the data that to be in­cor­por­ated, and choose the required cal­cu­la­tion from the dropdown list. The IF queries then always run the correct SUBTOTAL function.

Besides the function, which is easy to integrate in formulas, Excel offers another tool of the same name. You can find it in the “Data” tab under the “Subtotal” button. This tool is useful if you have a multi-column list in Excel, for example. You can include a fixed set of values (generally non-numerical) – for example names – in one column. In turn, these entries are assigned to numerical values: revenues, point scores, meas­ure­ments and similar data. The tool helps you calculate subtotals from groups – all identical entries in one column are grouped and the cor­res­pond­ing values in the other column are cal­cu­lated.