SUMPRODUCT with AutoFilter
 

Good Day to all!
Is there any possibility to use the SUMPRODUCT function along with AutoFilter?
I have the following situation:

Column A: Commodities
Column B: Origin
Column C: Weight
Column D: Quantity

On cell E1, I need a function to give me the average weight of the commodities, based on a selection with the AutoFilter.
For example, in other words:

If I select "France" in column B using the AutoFilter, I'd like a function to give me the average weight of all the commodies, i.e, multiplying the quantities by the weight and dividing by the total quantity.
It's more or less SUBTOTAL with SUMPRODUCT

Any chance to do it?

Thanks a lot,
Gustavo.

SUMPRODUCT with AutoFilter
 

=SUBTOTAL(1,C2:C290)
--
Best wishes,

Jim


"Gustavo Strabeli" wrote:

Good Day to all!
Is there any possibility to use the SUMPRODUCT function along with AutoFilter?
I have the following situation:

Column A: Commodities
Column B: Origin
Column C: Weight
Column D: Quantity

On cell E1, I need a function to give me the average weight of the commodities, based on a selection with the AutoFilter.
For example, in other words:

If I select "France" in column B using the AutoFilter, I'd like a function to give me the average weight of all the commodies, i.e, multiplying the quantities by the weight and dividing by the total quantity.
It's more or less SUBTOTAL with SUMPRODUCT

Any chance to do it?

Thanks a lot,
Gustavo.

SUMPRODUCT with AutoFilter
 

I left out the statement that this should be placed in Cell E1.
=SUBTOTAL(1,C2:C29)
--
Best wishes,

Jim


"Gustavo Strabeli" wrote:

Good Day to all!
Is there any possibility to use the SUMPRODUCT function along with AutoFilter?
I have the following situation:

Column A: Commodities
Column B: Origin
Column C: Weight
Column D: Quantity

On cell E1, I need a function to give me the average weight of the commodities, based on a selection with the AutoFilter.
For example, in other words:

If I select "France" in column B using the AutoFilter, I'd like a function to give me the average weight of all the commodies, i.e, multiplying the quantities by the weight and dividing by the total quantity.
It's more or less SUBTOTAL with SUMPRODUCT

Any chance to do it?

Thanks a lot,
Gustavo.

SUMPRODUCT with AutoFilter
 

=AVERAGE(IF((SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$20 )-ROW($B$1),,1))*(B2:B20="France"))<0,(C2:C20)*(D2: D20)))

which is an array formula, so commit with Ctrl-Shift-Enter, not just Enter


"Gustavo Strabeli" wrote in message
...
Good Day to all!
Is there any possibility to use the SUMPRODUCT function along with
AutoFilter?
I have the following situation:

Column A: Commodities
Column B: Origin
Column C: Weight
Column D: Quantity

On cell E1, I need a function to give me the average weight of the
commodities, based on a selection with the AutoFilter.
For example, in other words:

If I select "France" in column B using the AutoFilter, I'd like a function
to give me the average weight of all the commodies, i.e, multiplying the
quantities by the weight and dividing by the total quantity.
It's more or less SUBTOTAL with SUMPRODUCT

Any chance to do it?

Thanks a lot,
Gustavo.

SUMPRODUCT with AutoFilter
 

Jim,
It's not exactly what I need.
In fact, I need an weight average, not a simple average...

"Jim Jackson" escreveu na mensagem ...
I left out the statement that this should be placed in Cell E1.
=SUBTOTAL(1,C2:C29)
--
Best wishes,

Jim


"Gustavo Strabeli" wrote:

Good Day to all!
Is there any possibility to use the SUMPRODUCT function along with AutoFilter?
I have the following situation:

Column A: Commodities
Column B: Origin
Column C: Weight
Column D: Quantity

On cell E1, I need a function to give me the average weight of the commodities, based on a selection with the AutoFilter.
For example, in other words:

If I select "France" in column B using the AutoFilter, I'd like a function to give me the average weight of all the commodies, i.e, multiplying the quantities by the weight and dividing by the total quantity.
It's more or less SUBTOTAL with SUMPRODUCT

Any chance to do it?

Thanks a lot,
Gustavo.

SUMPRODUCT with AutoFilter
 

Dear Bob,
Guess it's the way.
Will try and revert with results.

Tks a bunch.
Gustavo.

"Bob Phillips" escreveu na mensagem ...
=AVERAGE(IF((SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$20 )-ROW($B$1),,1))*(B2:B20="France"))<0,(C2:C20)*(D2: D20)))

which is an array formula, so commit with Ctrl-Shift-Enter, not just Enter


"Gustavo Strabeli" wrote in message
...
Good Day to all!
Is there any possibility to use the SUMPRODUCT function along with
AutoFilter?
I have the following situation:

Column A: Commodities
Column B: Origin
Column C: Weight
Column D: Quantity

On cell E1, I need a function to give me the average weight of the
commodities, based on a selection with the AutoFilter.
For example, in other words:

If I select "France" in column B using the AutoFilter, I'd like a function
to give me the average weight of all the commodies, i.e, multiplying the
quantities by the weight and dividing by the total quantity.
It's more or less SUBTOTAL with SUMPRODUCT

Any chance to do it?

Thanks a lot,
Gustavo.

SUMPRODUCT with AutoFilter
 

Dear Bob,
Have checked this function, however it's still not what I'm looking for.
On column B I have several countries of origin, so I can't limit only one country in my criteria, Ex: (B2:B20="France").
I'd like to have the weight weighted average for whichever my selection with the AutoFilter is
Ex: If I select Country=France, need to have the weight average
If I select Commodity=Sugar, need to have the weight average

Any clue?

Thanks,
Gustavo


"Bob Phillips" escreveu na mensagem ...
=AVERAGE(IF((SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$20 )-ROW($B$1),,1))*(B2:B20="France"))<0,(C2:C20)*(D2: D20)))

which is an array formula, so commit with Ctrl-Shift-Enter, not just Enter


"Gustavo Strabeli" wrote in message
...
Good Day to all!
Is there any possibility to use the SUMPRODUCT function along with
AutoFilter?
I have the following situation:

Column A: Commodities
Column B: Origin
Column C: Weight
Column D: Quantity

On cell E1, I need a function to give me the average weight of the
commodities, based on a selection with the AutoFilter.
For example, in other words:

If I select "France" in column B using the AutoFilter, I'd like a function
to give me the average weight of all the commodies, i.e, multiplying the
quantities by the weight and dividing by the total quantity.
It's more or less SUBTOTAL with SUMPRODUCT

Any chance to do it?

Thanks a lot,
Gustavo.

SUMPRODUCT with AutoFilter
 

Just use

=AVERAGE(IF((SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$20 )-ROW($B$1),,1)))<0,(C2:C20)*(D2:D20)))

then

HTH

Bob


"Gustavo Strabeli" wrote in message
...
Dear Bob,
Have checked this function, however it's still not what I'm looking for.
On column B I have several countries of origin, so I can't limit only one
country in my criteria, Ex: (B2:B20="France").
I'd like to have the weight weighted average for whichever my selection with
the AutoFilter is
Ex: If I select Country=France, need to have the weight average
If I select Commodity=Sugar, need to have the weight average

Any clue?

Thanks,
Gustavo


"Bob Phillips" escreveu na mensagem
...
=AVERAGE(IF((SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$20 )-ROW($B$1),,1))*(B2:B20="France"))<0,(C2:C20)*(D2: D20)))

which is an array formula, so commit with Ctrl-Shift-Enter, not just Enter


"Gustavo Strabeli" wrote in message
...
Good Day to all!
Is there any possibility to use the SUMPRODUCT function along with
AutoFilter?
I have the following situation:

Column A: Commodities
Column B: Origin
Column C: Weight
Column D: Quantity

On cell E1, I need a function to give me the average weight of the
commodities, based on a selection with the AutoFilter.
For example, in other words:

If I select "France" in column B using the AutoFilter, I'd like a function
to give me the average weight of all the commodies, i.e, multiplying the
quantities by the weight and dividing by the total quantity.
It's more or less SUBTOTAL with SUMPRODUCT

Any chance to do it?

Thanks a lot,
Gustavo.

SUMPRODUCT with AutoFilter
 

Hey, Bob!
Now it's OK. It worked like a charm!
Thanks a bunch, buddy.

Gustavo


"Bob Phillips" escreveu na mensagem ...
Just use

=AVERAGE(IF((SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$20 )-ROW($B$1),,1)))<0,(C2:C20)*(D2:D20)))

then

HTH

Bob


"Gustavo Strabeli" wrote in message
...
Dear Bob,
Have checked this function, however it's still not what I'm looking for.
On column B I have several countries of origin, so I can't limit only one
country in my criteria, Ex: (B2:B20="France").
I'd like to have the weight weighted average for whichever my selection with
the AutoFilter is
Ex: If I select Country=France, need to have the weight average
If I select Commodity=Sugar, need to have the weight average

Any clue?

Thanks,
Gustavo


"Bob Phillips" escreveu na mensagem
...
=AVERAGE(IF((SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$20 )-ROW($B$1),,1))*(B2:B20="France"))<0,(C2:C20)*(D2: D20)))

which is an array formula, so commit with Ctrl-Shift-Enter, not just Enter


"Gustavo Strabeli" wrote in message
...
Good Day to all!
Is there any possibility to use the SUMPRODUCT function along with
AutoFilter?
I have the following situation:

Column A: Commodities
Column B: Origin
Column C: Weight
Column D: Quantity

On cell E1, I need a function to give me the average weight of the
commodities, based on a selection with the AutoFilter.
For example, in other words:

If I select "France" in column B using the AutoFilter, I'd like a function
to give me the average weight of all the commodies, i.e, multiplying the
quantities by the weight and dividing by the total quantity.
It's more or less SUBTOTAL with SUMPRODUCT

Any chance to do it?

Thanks a lot,
Gustavo.