SUMPRODUCT for multiple blocks of data

newSB

New Member
Joined
Sep 27, 2005
Messages
26
Hi All,
I got a sheet that is broken down by subtotals, but i need to sumproduct for 2 columns for each subtotals block. Below is the sample data...
hyld.xls
ABCDE
1AGYINC29210.5967.543310
2AGYINC7210.1479.733527
3AGYINC4520.0927.287290
4AGYINCTotal40940.83524.5631127
5DYINC29210.5967.543310
6DYINC7210.1479.733527
7DYINC4520.0927.287290
8DYINCTotal40940.83524.5631127
Sheet2


I need to do sumproduct for columns B&D and C&E for each subtotal.
Any suggestions how can i do this.

Thank you
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
It's not clear to me what it is you're looking for. Can you elaborate?
 
Upvote 0
Something like this... ??

=SUMPRODUCT(--(RIGHT(A15:A22,5)="Total"),B15:B22*D15:D22)
=SUMPRODUCT(--(RIGHT(A15:A22,5)="Total"),C15:C22*E15:E22)

but as Domenic said, it's unclear as to what you want.
 
Upvote 0
the original sheet i had duplicate rows and i needed summary totals of each name so i used subtotals from the excel menu but for columns D and E i need the sumproduct instead of totals for each grouping

Sumproduct sample for column C =SUMPRODUCT(B5:B7,D5:D7)/B8 and for column E =SUMPRODUCT(C5:C7,E5:E7)/C8

and my sheet has hundreds of rows like that are grouped for subtotals and i do not know how many rows each of the subtotals has. How can i do the sumproduct for each of columns using VBA or is there a simple way to do using formulas.

Please let me know if you need any other info regarding this.

Thank you
 
Upvote 0
Is Column A sorted in ascending order? If so, assuming that A5:E12 contains your data, let G5:G6 contain AGY Inc and DY Inc, and enter the following formula in H5, copy/drag down and over to Column I...

=SUMPRODUCT(--($A$5:$A$12=$G5),B$5:B$12,D$5:D$12)/INDEX($B$5:$E$12,MATCH($G5,$A$5:$A$12)+1,COLUMNS($H5:H5))

Adjust the range accordingly.

Hope this helps!
 
Upvote 0

Forum statistics

Threads
1,226,390
Messages
6,190,740
Members
453,615
Latest member
robbieb29

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top