The biggest problem with SUM, AVERAGE, COUNT and other NUMBER functions is, that they include rows that are hidden by filter. They don’t work correctly when we want to sum, count, or do any other number task only on visible cell. Here steps in the SUBTOTAL function of Excel.
Syntax of SUBTOTAL
Function_number: Subtotal supports various number function. To easily access them, it uses function number. Below is the table of function number with corresponding functions.
|Function Number To Include Manually Hidden Rows||Function Number To IgnoreManually Hidden Rows||Function|
Ref1 : The named range or reference of numbers.
[ref2] : optional named range or reference range to numbers.
Lets see an example:
I have this table of real state. Now I want to add a totals row but it should show me sum of visible cell only when I apply a filter.
Write this formula in the end of table or the top. Your choice…
Now whenever you will apply a filter, only visible cells total will be shown.
So yeah guys, SUBTOTAL is very useful when you create interactive dashboards. Let me know which function you use most of subtotal in comments section below. And don’t forget ask you doubts, they are most important.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.