Sum Left to Right

[Pestomania]

I am using SumProduct to identify the row that I want to get values from and the columns to create a "right" limit. Now I want to add everything from left to right and drag it left to get the value change.

I am going to try and show what I have below:

Table 1

Program	Operation 1	Operation 2	Operation 3	Operation 4	Operation 5	Total Days
Program Name 1	5	10	12	18	25	70
Program Name 2	3	9	14	15	18	59

Table 2

Program	Operation 1	Operation 2	Operation 3	Operation 4	Operation 5	Total Days
Program Name 1	Total Days = 70	Total Days - Sum(Operation 1:Operation 1) = 65	Total Days - Sum(Operation 1:Operation 2) = 55	Total Days - Sum(Operation 1: Operation 3) = 43	Total Days - Sum(Operation 1:Operation 4) = 25	70
Program Name 2	Total Days = 59	Total Days - Sum(Operation 1:Operation 1) = 56	Total Days - Sum(Operation 1:Operation 2) = 47	Total Days - Sum(Operation 1: Operation 3) = 33	Total Days - Sum(Operation 1:Operation 4) = 18	59

I want to make table 2 update if I change Table 1.

I have been using sumproduct to find my Total Days, but I cannot figure out how to do the dynamic ranger for Operation #'s.

Thank you!

 

[AlanY]

like this?

Book1
	A	B	C	D	E	F	G
1	Table 1
2	Program	Operation 1	Operation 2	Operation 3	Operation 4	Operation 5	Total Days
3	Program Name 1	5	10	12	18	25	70
4	Program Name 2	3	9	14	15	18	59
5
6	Table 2
7	Program	Operation 1	Operation 2	Operation 3	Operation 4	Operation 5	Total Days
8	Program Name 1	70	65	55	43	25	70
9	Program Name 2	59	56	47	33	18	59
Sheet2

Cell Formulas
Range	Formula
B8:B9	B8	=G3
C8:F8	C8	=B8-SUM(B$3:B3)
C9:F9	C9	=B9-SUM(B$4:B4)

 

[James006]

Hi,

Not sure to understand your question ...
May be in cell G2 =SUM(F2:OFFSET(F2,0,-4))
-4 does indicate how many columns to the Left ...

 

[Pestomania]

like this?

Book1
	A	B	C	D	E	F	G
1	Table 1
2	Program	Operation 1	Operation 2	Operation 3	Operation 4	Operation 5	Total Days
3	Program Name 1	5	10	12	18	25	70
4	Program Name 2	3	9	14	15	18	59
5
6	Table 2
7	Program	Operation 1	Operation 2	Operation 3	Operation 4	Operation 5	Total Days
8	Program Name 1	70	65	55	43	25	70
9	Program Name 2	59	56	47	33	18	59
Sheet2

Cell Formulas
Range	Formula
B8:B9	B8	=G3
C8:F8	C8	=B8-SUM(B$3:B3)
C9:F9	C9	=B9-SUM(B$4:B4)

Yes in simple terms that is how it would work but I want it in a dynamic version similar to sumproduct so that it can maintain dynamic as I may add programs to the table and I want it to update.