Conditional SUMPRODUCT or SUMPRODUCT with Filters
 

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?

Conditional SUMPRODUCT or SUMPRODUCT with Filters
 

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" wrote in message
...
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?

Conditional SUMPRODUCT or SUMPRODUCT with Filters
 

=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?

Conditional SUMPRODUCT or SUMPRODUCT with Filters
 

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" wrote in message
...
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?

Conditional SUMPRODUCT or SUMPRODUCT with Filters
 

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?