Q:

Summing visible cells in excel VBA

I am trying to sum a set of visible cells in a data sheet using the formula:
=SUMPRODUCT(SUBTOTAL(109,OFFSET(C2:C6,ROW(C2:C6)-MIN(ROW(C2:C6)),,1,1))*(A2:A6="peter")*(B2:B6=201104))

due to the fact of my dashboard having a huge back end dataset (170k), the dashboard freezes when I use this formula (I have to use this on every table I want to display on the front end).
Is there a way to optimize this?  Or perhaps a faster method using index and match.

A:

It works perfectly if you copy the data to another sheet.  Enable a new pivot table with all columns.  Thus, when the data is filtered in the data sheet, the copied sheet will contain only the filtered values.  
The pivot table will now obtain only the copied data (i.e., filtered data).  Once this is complete, you will need to add a line of code in vba :
worksheets("Summary").pivottables("summary_view").pivotcache.refresh

and it automatically updates the pivot table.  Hence, when you filter the data in the sheet using a listbox, the pivot automatically gets updated

