If we have a table according to which the totals should be calculated, then it plays an important role which function they are calculated, because. the table can be:
- Filters included
- Some lines are hidden
- Collapsed grouped rows
- Subtotals inside a table
- Errors in formulas
Some of the methods below are sensitive to these factors, some are not. This must be taken into account when performing calculations:
SUM (SUM) – stupidly sums everything in the selected range indiscriminately, i.e. and hidden lines as well. If there is any error in at least one cell, it stops counting and also gives an error at the output.
SUBTOTALS (SUBTOTALS) with code 9 in the first argument – sums all cells visible after the filter. Ignores other similar functions that may consider internal subtotals in the source range.
SUBTOTALS (SUBTOTALS) with code 109 in the first argument – sums all the cells visible after the filter and grouping (or hiding) cells. Ignores other similar functions that may consider internal subtotals in the source range.
If you need not to sum, then you can use other values of the code of the mathematical operation:
UNIT (AGGREGATE) – the most powerful feature that appeared in Office 2010. Just like SUBTOTALS, it can not only sum up, but also calculate the average, number, minimum, maximum, etc. — the operation code is given by the first argument. Plus, it has many options for counting, which can be specified as a second argument:
- Selective calculations for one or more conditions
- Paste in filtered rows
- Quickly hide and show unwanted rows and columns