# SUMPRODUCT for multiple blocks of data

#### newSB

##### New Member
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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
It's not clear to me what it is you're looking for. Can you elaborate?

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.

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

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))

Hope this helps!

Replies
0
Views
64
Replies
5
Views
125
Replies
4
Views
172
Replies
1
Views
126
Replies
13
Views
132

1,203,762
Messages
6,057,219
Members
444,915
Latest member
getrdon24

### 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.

### Which adblocker are you using?

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

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