The SUM function in Excel provides the total SUM of all the cells mentioned in the range, be it hidden or not. Now, lets assume that it is the nth version of Excel working and you have hidden many rows, which you found to be irrelevant at that point in time. How will you find the sum of the visible cells only?
The solution to the problem is to use SUBTOTAL instead of SUM.
The result of the formula is again, not an outright answer, and depends on the usage. SUMTOTAL formula has a total of 11 parameters -Average (1), Count (2), CountA (3), Max (4), Min (5), Product (6), StdDev (7), StdDevP (8), Sum (9), Var (10), or VarP (11). To add a twist to it, when you add 100 to this parameter, Excel includes only visible cells in the result.
Do you notice the difference in SUBTOTAL when used with parameter 109 and 9? Try it out yourself.
The solution to the problem is to use SUBTOTAL instead of SUM.
The result of the formula is again, not an outright answer, and depends on the usage. SUMTOTAL formula has a total of 11 parameters -Average (1), Count (2), CountA (3), Max (4), Min (5), Product (6), StdDev (7), StdDevP (8), Sum (9), Var (10), or VarP (11). To add a twist to it, when you add 100 to this parameter, Excel includes only visible cells in the result.
Do you notice the difference in SUBTOTAL when used with parameter 109 and 9? Try it out yourself.
