Conditional SUMPRODUCT or SUMPRODUCT with Filters

[Ted M H]

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?

 

[Peo Sjoblom]

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

 

[Teethless mama]

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

 

[Ted M H]

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.

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

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?

 

[Ted M H]

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.

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

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?