Subtotal all the sumproducts in cells above.

[StephenSLR]

In cell E88 I have the formula =SUMPRODUCT(B84:B86,E84:E86)

In cell E121 I have the formula =SUMPRODUCT(B92:B119,E92:E119)

and so on for many other subtotal cells in the E column.

I would like to incorporate all the Sumproducts into Subtotals so I can add up all instances of the Sumproducts at the bottom of my spreadsheet with a single subtotal formula at the last cell in column E at the bottom.

What is the best way to do this?

s

 

[Logit]

.
The simpliest method would be to set aside a single cell for your GRAND TOTAL and use a formula =SUM(E88,E121, etc. etc.)

 

[StephenSLR]

a single cell for your GRAND TOTAL and use a formula =SUM(E88,E121, etc. etc.)

Thanks.

That's something I would do if I had no choice but I find it can lead to errors if I add in rows and forget to update the Grand Total cell and my sheets can be very long so a chance of error if I don't include every cell with a Sumproduct formula.

s

 

[fhqwgads]

assuming your subtotals are the only cells with formulas, something like this maybe?

Book1
	A	B
1	10	20
2	10	20
3	10	20
4	10	20
5	10	20
6	10	20
7	10	20
8	Subtotal	210
9
10	30	40
11	30	40
12	30	40
13	30	40
14	30	40
15	30	40
16	30	40
17	30	40
18	Subtotal	560
19
20	Grandtotal	770
Sheet14

Cell Formulas
Range	Formula
B8	=SUM(A1:B7)
B18	=SUM(A10:B17)
B20	{=SUM((--(ISFORMULA(B1:B19)=TRUE))*(B1:B19))}
Press CTRL+SHIFT+ENTER to enter array formulas.

this is my first time working with what i think are arrays so i hope it works

 

[StephenSLR]

assuming your subtotals are the only cells with formulas

Unfortunately just about every cell includes a formula; they either include the no. of hours x rate or a percentage of hours taken to complete a job, etc.

s

 

[Logit]

.
I believe you should consider a worksheet design change.

 

[fhqwgads]

maybe something like this?

Book1
	A	B	C
1	Units	Price	Total
2	3	20	60
3	3	20	60
4	3	20	60
5	3	20	60
6	3	20	60
7	3	20	60
8	3	20	60
9		Subtotal	420
10
11	1	40	40
12	1	40	40
13	1	40	40
14	1	40	40
15	1	40	40
16	1	40	40
17	1	40	40
18	1	40	40
19		Subtotal	320
20
21		Grandtotal	740
Sheet14

Cell Formulas
Range	Formula
C2	=B2*A2
C9	=SUMPRODUCT(A2:A8,B2:B8)
C19	=SUMPRODUCT(A11:A18,B11:B18)
C21	{=SUM((--(ISNUMBER(SEARCH("SUMPRODUCT",FORMULATEXT(C2:C20)))))*(C2:C20))}
Press CTRL+SHIFT+ENTER to enter array formulas.

however this wouldnt work if any cell in the range is text. i think it's because using FORMULATEXT on a cell with text returns an error and i dont know how to work around it. so for example if i changed the range in my C21 formula to C1:C20 which would include the column heading which is text then you'd get an error in the grand total