Using sumproduct and an autofiltr

[craig007]

Hi there,
We use a flat file of data results which requires a sumproduct to get the correct daily result for the activities performed.

Am looking for a way for this sumproduct function to work after an auto filter is applied (eg change the cells referenced in the sumproduct formula to reflect the change in the auto filter).

I have attached a sample file.

The top is the result I am looking for when the data is just the single day.

the next section is how the data looks - with the auto filter.

Is there any way to make the sumproduct interact with the results of the auto filter?

Any assistance would be greatly appreciated.

I am sure I have seen it done before I just don't seem to be able to get it to work.

[dredwolf]

Sorry to burst your bubble, BUT SUMPRODUCT() does not follow Filtering, Depending on how much actual time you are willing to put into the spreadsheet, you could run a filter, copy the results as values to another column, then run a sumproduct function on that...but sumproduct itself won't ignore hidden cells..

[benishiryo]

hi craig007, welcome to the forum. try:
=SUMPRODUCT(L9:L56,N9:N56,SUBTOTAL(3,OFFSET(L9,ROW(L9:L56)-ROW(L9),)))/SUBTOTAL(9,L9:L56)

if it helps, i did an explanation of something similar here:
http://www.excelforum.com/excel-form...19#post3131419

[jaslake]

Hi craig007

You might try the SUBTOTAL Function

Please Login or Register  to view this content.

Please Login or Register  to view this content.