SUMPRODUCT and (perhaps) SUBTOTAL with AUTOFILTER

**Donovan** · 06-08-2006, 04:29 PM

Probably easy to ya'll but I am new to this.

I am trying to SUMPRODUCT two columns that automatically adjust the answer to the autofilter that is selected..Any advice on how to achieve this.

I assume it is some sort of SUMPRODUCT and SUBTOTAL combo formula but have been unable to figure it out.....

=SUMPRODUCT(F4:F21,G4:G21) to be combined with =SUBTOTAL(9,G4:G21) or similar subtotal

Hence when I select a suppliers name, it will display:
Column C = Supplier (Autofiltered)
Column D = Description of all the parts they supply us
Column F = Price per part
Column G through M = Quanity of each part in each region
At the top (above autofilter) of G through H, I am trying to display how much the cost is of all the parts for the filtered supplier.

Hence by changing the Autofilter selection, it will display a new supplier, their products and quantities x price per region.

Please help if you know how to resolve.

Peo Sjoblom · 06-08-2006, 06:00 PM

Example here

http://makeashorterlink.com/?B22062C3D

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

"Donovan" <[email protected]> wrote in
message news:[email protected]...
>
>

Probably easy to ya'll but I am new to this.
>
> I am trying to SUMPRODUCT two columns that automatically adjust the
> answer to the autofilter that is selected..Any advice on how to achieve
> this.
>
> I assume it is some sort of SUMPRODUCT and SUBTOTAL combo formula but
> have been unable to figure it out.....

>
> Please help if you know how to resolve.
>
>
> --
> Donovan
> ------------------------------------------------------------------------
> Donovan's Profile:
> http://www.excelforum.com/member.php...o&userid=35234
> View this thread: http://www.excelforum.com/showthread...hreadid=550112
>

**Donovan** · 06-08-2006, 06:13 PM

Dear PEO,

Followed to sample and tried it but came up with value error:

=SUMPRODUCT($F3:$F500,SUBTOTAL(9,I3:I500))

Is this correct or am I missing the boat?

R,
Donovan

Domenic · 06-08-2006, 06:55 PM

Try...

=SUMPRODUCT(F3:F500,SUBTOTAL(9,OFFSET(I3:I500,ROW(I3:I500)-ROW(I3),0,1)))

Hope this helps!

In article <[email protected]>,
Donovan <[email protected]> wrote:

> Dear PEO,
>
> Followed to sample and tried it but came up with value error:
>
> =SUMPRODUCT($F3:$F500,SUBTOTAL(9,I3:I500))
>
> Is this correct or am I missing the boat?
>
> R,
> Donovan