Client Hours Rate
AAA 10 50
AAA 8 65
BBB 20 50
CCC 10 55
BBB 10 65

Using an Excel 2007 Table. I have this formula in the total row:
=SUMPRODUCT([Hours],[Rate])
which works just fine for the whole table. I need the same functionality to
work with filtering applied to the data; for example if I use the filter to
display just Client AAA I want the SUMPRODUCT formula to return
(10*50)+(8*65).
It would be great if the SUBTOTAL function had a SUMPRODUCT option, but it
doesn't. Any suggestions?

It's still possible



=SUMPRODUCT(B2:B20,C2:C20,(SUBTOTAL(3,OFFSET($B$2,ROW(B2:B20)-MIN(ROW(B2:B20)),,))))


=SUMPRODUCT(Hours,Rate,(SUBTOTAL(3,OFFSET($B$2,ROW(Hours)-MIN(ROW(Hours)),,))))

Where B2 is the first cell with data

--


Regards,


Peo Sjoblom

"Ted M H" <(E-Mail Removed)> wrote in message
news:4EE13984-0F20-4E3C-A40D-(E-Mail Removed)...
> Client Hours Rate
> AAA 10 50
> AAA 8 65
> BBB 20 50
> CCC 10 55
> BBB 10 65
>
> Using an Excel 2007 Table. I have this formula in the total row:
> =SUMPRODUCT([Hours],[Rate])
> which works just fine for the whole table. I need the same functionality
> to
> work with filtering applied to the data; for example if I use the filter
> to
> display just Client AAA I want the SUMPRODUCT formula to return
> (10*50)+(8*65).
> It would be great if the SUBTOTAL function had a SUMPRODUCT option, but it
> doesn't. Any suggestions?
>

=SUMPRODUCT((A2:A100="AAA")*B2:B100*C2:C100)


"Ted M H" wrote:

> Client Hours Rate
> AAA 10 50
> AAA 8 65
> BBB 20 50
> CCC 10 55
> BBB 10 65
>
> Using an Excel 2007 Table. I have this formula in the total row:
> =SUMPRODUCT([Hours],[Rate])
> which works just fine for the whole table. I need the same functionality to
> work with filtering applied to the data; for example if I use the filter to
> display just Client AAA I want the SUMPRODUCT formula to return
> (10*50)+(8*65).
> It would be great if the SUBTOTAL function had a SUMPRODUCT option, but it
> doesn't. Any suggestions?
>

Hi Peo,

Amazing! It works perfectly. Now I have a different problem: I want to
figure out why it works...I'll be noodling on that for a while.

Thanks so much for the quick, excellent response.

"Peo Sjoblom" wrote:

> It's still possible
>
>
>
> =SUMPRODUCT(B2:B20,C2:C20,(SUBTOTAL(3,OFFSET($B$2,ROW(B2:B20)-MIN(ROW(B2:B20)),,))))
>
>
> =SUMPRODUCT(Hours,Rate,(SUBTOTAL(3,OFFSET($B$2,ROW(Hours)-MIN(ROW(Hours)),,))))
>
> Where B2 is the first cell with data
>
> --
>
>
> Regards,
>
>
> Peo Sjoblom
>
> "Ted M H" <(E-Mail Removed)> wrote in message
> news:4EE13984-0F20-4E3C-A40D-(E-Mail Removed)...
> > Client Hours Rate
> > AAA 10 50
> > AAA 8 65
> > BBB 20 50
> > CCC 10 55
> > BBB 10 65
> >
> > Using an Excel 2007 Table. I have this formula in the total row:
> > =SUMPRODUCT([Hours],[Rate])
> > which works just fine for the whole table. I need the same functionality
> > to
> > work with filtering applied to the data; for example if I use the filter
> > to
> > display just Client AAA I want the SUMPRODUCT formula to return
> > (10*50)+(8*65).
> > It would be great if the SUBTOTAL function had a SUMPRODUCT option, but it
> > doesn't. Any suggestions?
> >
>
>
>

Hey Teethless!

Thanks for taking the time to reply. If I understand your solution it will
work as long as I use a criteria in the formula, but it won't work in a
filtered table with variable criteria defined by column filters.

"Teethless mama" wrote:

> =SUMPRODUCT((A2:A100="AAA")*B2:B100*C2:C100)
>
>
> "Ted M H" wrote:
>
> > Client Hours Rate
> > AAA 10 50
> > AAA 8 65
> > BBB 20 50
> > CCC 10 55
> > BBB 10 65
> >
> > Using an Excel 2007 Table. I have this formula in the total row:
> > =SUMPRODUCT([Hours],[Rate])
> > which works just fine for the whole table. I need the same functionality to
> > work with filtering applied to the data; for example if I use the filter to
> > display just Client AAA I want the SUMPRODUCT formula to return
> > (10*50)+(8*65).
> > It would be great if the SUBTOTAL function had a SUMPRODUCT option, but it
> > doesn't. Any suggestions?
> >