Don't forget to update the formula as you add new rows.Consider putting your subtotals at the top of the range not the bottom so you don't have to keep moving the subtotals down to make room for new rows. Data tables tend to to get longer as more rows are added.that have not been filtered out).īased on the table above, it should be easy to see that changing the formula to the following will calculate the AVERAGE rather than the SUM of the visible rows in column H:Ī couple of quick tips on using SUBTOTAL() on filtered lists: If you then change the filter criteria, then the SUBTOTAL() function will automatically recalculate based on the rows that are visible (i.e. If rows 3, 5, 7, 9 and 11 are not visible because they have been filtered out, the subtotal will not include those values.
This formula will calculate the sum of all the visible values in column H, and assumes that the table has 19 rows, from H2 to H20. Possible values for function_num are as follows: 1įor example, to add up the values in column H (let's say this is the Sales Amount column) that are currently visible, enter the following formula: Where function_num determines what the calculation should be (see below) and ref1, ref2 etc is the range of cells to be calculated. The syntax of the SUBTOTAL() function is as follows: If you're using the Autofilter feature, you're probably wondering how to perform calculations on only those values that are being displayed by the filter.
0 kommentar(er)
