Sumproduct Filtered Data

[Jonno Ash]

Hi,

I know this has already been posted in here but I still cannot get my head around what I need to do.

I have a worksheet with a list of products from various suppliers. I want to work out the average cost per unit in total and filtered by supplier.

Column A has the supplier name.
Column H has the price per unit for each supplier.
Column M has the number of units supplied by each supplier.
M95 is the Subtotal of the number of units supplied.

If I filter the data the average price is incorrect as it Subtotal in M95 is correct but the Sumproduct is not ignoring the filtered data.

I know I need to use the Offset fucntion but I just cannot get it to work.

This is my Sumprodct formula and that is in M96

=IFERROR(SUMPRODUCT(H7:H93,M7:M93)/M95,0)

Any help would be greatly recieved.

Thanks in advance.

Jonathan

[PaulM100]

Upload a sample file with desired results as well. To upload GO Advanced - Manage attachments -

[Jonno Ash]

Hi,

I have attached a sample.

I want to be able to filter the data and the average prices at the bottom (Highlighted in red) to ignore filtered out rows.

Thanks

Jonathan

[PaulM100]

Try this:

Please Login or Register  to view this content.