Subtotals with formulas
- Aug. 17, 2019
 | Example files with this article: |
Introduction
Do you know the =SUBTOTAL( ) formula in Excel ? Or the =AGGREGATE( ) formula ? Both functions allows to to summarize a dataset in an easy and straightforward way. The functions are rather generic: you can apply them to sum up a dataset, but just as easy you can compute averages, or the maximum/minimum, etc. The name for the formulas does not change, but rather the arguments that are used inside the formula. Next to this, it is very convenient that these functions can be used to ignore hidden rows, of cells with error values, or cells that contain a subtotal themselves. Put differently we are talking about versatile functions that we need to look into.
The example where I applied these functions only recently, is about a statement of work for a project: the estimation of the effort and costs needed to deliver the full project. The project is divided into tasks and subtasks. Each of the subtasks needs to be estimated, and finally this will result in an estimation for the full project. Please note that Excel already has the built-in functionality of subtotals, but, it requires a specific layout of your sheet and some things to take into account. If you would like to use it, have a look in the 'Data' menu of the ribbon.
The picture below (and the attached file) show the anonimised data. The empty cells in column G and H will need to be populated with the sum total of the lines below in the same header. Just think about column G containing hours or days, and column H contains the costs by multiplying G with 400. Of course we are using Excel... we don't want hardcoded values in these (now) empty cells, we want formulas. Formulas that expand and collapse with the number of lines within the header. In addition, we do not want to have double countings at the level of the project. This means that a task is the sum of its subtasks, but when we make the grand total of the project as the sum of cells/tasks, we need to eliminate the values for the subtasks to avoid double countings. All this can be done with the functions =SUBTOTAL( ) and =AGGREGATE( ).
Elaboration
Eventually I went a bit further. I wrote a bit of VBA coding to automate writing the formulas in the correct cells. This allows us to select the cells, push a button, and have VBA put the desired totals in the cells by means of formulas.
Select cells G4:G7,G9,G11:G14,G17:G20, etc. (so press the Ctrl key). Press the green button and then choose OK. The result will be that sum totals will be made in the empty cells above the selected cells - the cells received a formula that was chosen in the userform.
Looking at the AGGREGATE formula in cell G3 we notice the range G4:G7. The sum equals 17 hours/days... Besides this the AGGREGATE formula also features 2 other arguments. That is, the formula needs to know whether the user wants the total as a sum/average/maximum/minimum/... And we can decide to ignore hidden rows, cells with error values, or cells containing a AGGREGATE/SUBTOTAL formula. Below in this article I will give you a list of the possible argument values and what they stand for.
For hiding rows, we have the following possibilities:
- hiding rows by manually hiding them in the worksheet
- hiding rows by setting a row height of 0
- hiding rows by an active autofilter or advanced filter
- hiding rows by a filter in a table (ListObject)
- hiding rows by applying a grouping to rows
How did I proceed ? Well, I create a number of names in the workbook, like AGGR_FUN and AGGR_OPT. If you want sums as your totals, then you need a 9 for the value of AGGR_FUN. Please look in the defined names of the workbook via Ctrl-F3. Do you require averages, then you choose 1 instead of 9. Take 2 for counts, etc. With a value of 0 for AGGR_OPT you will ignore nested SUBTOTAL and AGGREGATE formulas, but not the cells containing error values or hidden rows. Error values can also stem from formulas defined in addins, for instance.
Then what about that nFACTOR in the formula ? That is also a defined name in the workbook, and buy default its value is 1. If you would like to quickly have only a fraction of the hours, then take for example 0.5 as the factor. Or increase all hours with a markup of say 5%: choose 1.05 as the factor.
Finally I give you 2 tips to avoid selecting potentially a lot of cells manually, as we did in the exercise above:
- select a range of cells, press F5 > Special... > Constants > OK
- select a range of cells, make your choices in the userform and just as you are about to click OK, press the Shift key. I programmed this feature.
Would you like to easily add tasks and substasks by duplicating a row ? Read my earlier article here, it explains my shortcut key Ctrl + e. I use it all the time to duplicate the selected rows.
Wrapping up I list 2 tables with the possible values for arguments to the function AGGREGATE, and 1 table for SUBTOTAL.
| Function number | Function |
| 1 | AVERAGE |
| 2 | COUNT |
| 3 | COUNTA |
| 4 | MAX |
| 5 | MIN |
| 6 | PRODUCT |
| 7 | STDEV.S |
| 8 | STDEV.P |
| 9 | SUM |
| 10 | VAR.S |
| 11 | VAR.P |
| 12 | MEDIAN |
| 13 | MODE.SNGL |
| 14 | LARGE |
| 15 | SMALL |
| 16 | PERCENTILE.INC |
| 17 | QUARTILE.INC |
| 18 | PERCENTILE.EXC |
| 19 | QUARTILE.EXC |
| Option | Behavior |
| 0 or omitted | Ignore nested SUBTOTAL and AGGREGATE functions |
| 1 | Ignore nested SUBTOTAL, AGGREGATE functions, and hidden rows |
| 2 | Ignore nested SUBTOTAL, AGGREGATE functions, and error values |
| 3 | Ignore nested SUBTOTAL, AGGREGATE functions, hidden rows, and error values |
| 4 | Ignore nothing |
| 5 | Ignore hidden rows |
| 6 | Ignore error values |
| 7 | Ignore hidden rows and error values |
| Function number (1) | Function number (2) | Function |
| 1 | 101 | AVERAGE |
| 2 | 102 | COUNT |
| 3 | 103 | COUNTA |
| 4 | 104 | MAX |
| 5 | 105 | MIN |
| 6 | 106 | PRODUCT |
| 7 | 107 | STDEV.S |
| 8 | 108 | STDEV.P |
| 9 | 109 | SUM |
| 10 | 110 | VAR.S |
| 11 | 111 | VAR.P |
(1): do not ignore hidden values
(2): ignore hidden values
Microsoft help page
on SUBTOTAL
Microsoft help page
on AGGREGATE
