SUMPRODUCT with Autofilter

[Gustavo Strabeli]

Good Day to all!

I need your help in order to know what function to use to solve my problem.
I have following issue:

- My data starts on A7, finishing on G150, with Autofilter on row 7
- I have a little "summary" on A1:B5 where I'm using the SUBTOTAL function to have the correct figures when I use the Autofilter on row 7.

Among some other informations in the summary (B4), I need the average weight (weighted average) of the commodities in my list, but it needs to work with the Autofilter.
I mean, If I select one location with the Autofilter, I need to have the weighted average for that location only.
Or, If I select "Citrus Fuits" with the Autofilter, I need to have the weighted average for those commodities.

Any chance to do that?

Thanks,
Gustavo.

 

[Peo Sjoblom]

There might be a prettier way but this seems to work

=SUMPRODUCT(((B8:B26)*SUBTOTAL(3,OFFSET(C$7,ROW(C8:C26)-ROW(C7),,1))),((C8:C26)*SUBTOTAL(3,OFFSET(C$7,ROW(C8:C26)-ROW(C7),,1))))/SUBTOTAL(9,C8:C26)

Would be the same as

=SUMPRODUCT(B8:B26,C8:C26)/SUM(C8:C26)

but it will work on a filtered list

--
Regards,

Peo Sjoblom


Good Day to all!

I need your help in order to know what function to use to solve my problem.
I have following issue:

- My data starts on A7, finishing on G150, with Autofilter on row 7
- I have a little "summary" on A1:B5 where I'm using the SUBTOTAL function
to have the correct figures when I use the Autofilter on row 7.

Among some other informations in the summary (B4), I need the average weight
(weighted average) of the commodities in my list, but it needs to work with
the Autofilter.
I mean, If I select one location with the Autofilter, I need to have the
weighted average for that location only.
Or, If I select "Citrus Fuits" with the Autofilter, I need to have the
weighted average for those commodities.

Any chance to do that?

Thanks,
Gustavo.

 

[Gustavo Strabeli]

Dear Peo,

Worked perfectly!!

Thanks a bunch.
Gustavo.


"Peo Sjoblom" <[email protected]> escreveu na mensagem There might be a prettier way but this seems to work

=SUMPRODUCT(((B8:B26)*SUBTOTAL(3,OFFSET(C$7,ROW(C8:C26)-ROW(C7),,1))),((C8:C26)*SUBTOTAL(3,OFFSET(C$7,ROW(C8:C26)-ROW(C7),,1))))/SUBTOTAL(9,C8:C26)

Would be the same as

=SUMPRODUCT(B8:B26,C8:C26)/SUM(C8:C26)

but it will work on a filtered list

--
Regards,

Peo Sjoblom


Good Day to all!

I need your help in order to know what function to use to solve my problem.
I have following issue:

- My data starts on A7, finishing on G150, with Autofilter on row 7
- I have a little "summary" on A1:B5 where I'm using the SUBTOTAL function
to have the correct figures when I use the Autofilter on row 7.

Among some other informations in the summary (B4), I need the average weight
(weighted average) of the commodities in my list, but it needs to work with
the Autofilter.
I mean, If I select one location with the Autofilter, I need to have the
weighted average for that location only.
Or, If I select "Citrus Fuits" with the Autofilter, I need to have the
weighted average for those commodities.

Any chance to do that?

Thanks,
Gustavo.

 

[Pete_UK]

You can use different numbers for the first parameter of the SUBTOTAL
function - you probably have:

=SUBTOTAL(9,G8:G150)

where the 9 indicates to do a SUM. If you change the 9 to 2 it will
give you a COUNT, so you could have:

=SUBTOTAL(9,G8:G150)/SUBTOTAL(2,G8:G150)

Alternatively, you could change it to a 1 which will give you an
average:

=SUBTOTAL(1,G8:G150)

Hope this helps.

Pete