SUMPRODUCT with Autofilter
 

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.

SUMPRODUCT with Autofilter
 

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


"Gustavo Strabeli" wrote in message
...
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.

SUMPRODUCT with Autofilter
 

Dear Peo,

Worked perfectly!!

Thanks a bunch.
Gustavo.


"Peo Sjoblom" 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


"Gustavo Strabeli" wrote in message
...
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.

SUMPRODUCT with Autofilter
 

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


On Apr 12, 3:41 pm, "Gustavo Strabeli"
wrote:
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.