Search code examples
excelexcel-formula

Automatically sum the value in columns in excel with filtering on

I have a worksheet with 50 rows & 10 column. One of the column has an amount. I need to display this at the bottom.
I can do this by =SUM(Range) function.

However when the filter is applied and say only 5 colums are dispalyed, I need to now show the total for just these 5 columns. The filtering can be on one column or on a combination of 3-4 columns. If its possible using worksheet functions Great, else even a VBA solution would also do.

Solution

  • Use the SubTotal function. This can optionally ignore hiddden cells. Refer Excel help for details