SUMPRODUCT with AutoFilter

G

Gustavo Strabeli

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.
 
J

Jim Jackson

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

Bob Phillips

=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


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.
 
G

Gustavo Strabeli

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

"Jim Jackson" <[email protected]> escreveu na mensagem I left out the statement that this should be placed in Cell E1.
=SUBTOTAL(1,C2:C29)
 
G

Gustavo Strabeli

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

Tks a bunch.
Gustavo.

"Bob Phillips" <[email protected]> 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


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.
 
G

Gustavo Strabeli

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" <[email protected]> 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


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.
 
B

Bob Phillips

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


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" <[email protected]> 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


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.
 
G

Gustavo Strabeli

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

Gustavo


"Bob Phillips" <[email protected]> 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


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" <[email protected]> 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


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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUMPRODUCT with Autofilter 3
Error 1004 on Range.Text with AutoFilter 3
Autofilter problem 9
Consolidation Table from Different Excel Tabs with Different Format 1
Sumproduct with Autofilter 4
Autofilter macro 2
how to design spreadsheets to collect data and display the totalmaterial used in a plating departmen 13
SUMIF with AutoFilter 17

Top