FIND SUM OF MULTIPLE OUTPUT DATA FOR SINGLE INPUT DATA UNTIL NEXT INPUT DATA IS FOUND

[cclp]

My query is regarding MS Excel. Please refer below image for better understanding. I have one input data and it parameter, for which I get multiple output data and their respective parameters. I need to build a table where I feed the input data and receive a sum of output data parameters.

 

[alansidman]

cannot manipulate data in a picture. Please reload your sample data using XL2BB. See my signature.

 

[KRice]

Based on what you've described, something like this should give you the sum, but you should consider flattening your table to avoid the difficulties in determining where the range to sum begins and ends.

MrExcel_20220603.xlsx
	A	B	C	D	E	F	G	H
1
2	A1	X1		1
3				2			colA	A2
4				3			colB	X2
5				4			sum of D	26
6	A2	X2		5
7				6
8				7
9				8
10	A3	X3		9
11				10
12				11
13	something	something		12
14				13
15				14
16				15
17	x	x
Sheet8

Cell Formulas
Range	Formula
H5	H5	=LET(seq,ROW(A$2:A$100)-ROW(A$2)+1,cidx,FILTER(seq,(A$2:A$100<>"")*(B$2:B$100<>"")),sidx,FILTER(seq,(A$2:A$100=$H$3)*(B$2:B$100=H4)),eidx,INDEX(cidx,MATCH(sidx,cidx,0)+1),SUM(FILTER(D$2:D$100,(seq>=sidx)*(seq<eidx))))

If you flattened the table so that it appears like this, then the summation can be done with a much simpler approach:

MrExcel_20220603.xlsx
	J	K	L	M	N	O	P	Q
1
2	A1	X1		1
3	A1	X1		2			colA	A2
4	A1	X1		3			colB	X2
5	A1	X1		4			sum of D	26
6	A2	X2		5
7	A2	X2		6
8	A2	X2		7
9	A2	X2		8
10	A3	X3		9
11	A3	X3		10
12	A3	X3		11
13	something	something		12
14	something	something		13
15	something	something		14
16	something	something		15
17	x	x
Sheet8

Cell Formulas
Range	Formula
J3:K5,J14:K16,J11:K12,J7:K9	J3	=J2
Q5	Q5	=SUMIFS(M$2:M$100,J$2:J$100,$Q$3,K$2:K$100,$Q$4)

To flatten the table, you could use a VBA script to fill in the blanks, Power Query, or even the approach described in the 1st method here (which is what I used).

Fill Down Blank Cells Until the Next Value in Excel (3 Easy Ways)

Watch Video - Fill Down in Excel

trumpexcel.com

 

[cclp]

Based on what you've described, something like this should give you the sum, but you should consider flattening your table to avoid the difficulties in determining where the range to sum begins and ends.

MrExcel_20220603.xlsx
	A	B	C	D	E	F	G	H
1
2	A1	X1		1
3				2			colA	A2
4				3			colB	X2
5				4			sum of D	26
6	A2	X2		5
7				6
8				7
9				8
10	A3	X3		9
11				10
12				11
13	something	something		12
14				13
15				14
16				15
17	x	x
Sheet8

Cell Formulas
Range	Formula
H5	H5	=LET(seq,ROW(A$2:A$100)-ROW(A$2)+1,cidx,FILTER(seq,(A$2:A$100<>"")*(B$2:B$100<>"")),sidx,FILTER(seq,(A$2:A$100=$H$3)*(B$2:B$100=H4)),eidx,INDEX(cidx,MATCH(sidx,cidx,0)+1),SUM(FILTER(D$2:D$100,(seq>=sidx)*(seq<eidx))))

If you flattened the table so that it appears like this, then the summation can be done with a much simpler approach:

MrExcel_20220603.xlsx
	J	K	L	M	N	O	P	Q
1
2	A1	X1		1
3	A1	X1		2			colA	A2
4	A1	X1		3			colB	X2
5	A1	X1		4			sum of D	26
6	A2	X2		5
7	A2	X2		6
8	A2	X2		7
9	A2	X2		8
10	A3	X3		9
11	A3	X3		10
12	A3	X3		11
13	something	something		12
14	something	something		13
15	something	something		14
16	something	something		15
17	x	x
Sheet8

Cell Formulas
Range	Formula
J3:K5,J14:K16,J11:K12,J7:K9	J3	=J2
Q5	Q5	=SUMIFS(M$2:M$100,J$2:J$100,$Q$3,K$2:K$100,$Q$4)

To flatten the table, you could use a VBA script to fill in the blanks, Power Query, or even the approach described in the 1st method here (which is what I used).

Fill Down Blank Cells Until the Next Value in Excel (3 Easy Ways)

Watch Video - Fill Down in Excel

trumpexcel.com

Thanks for replying
How will the formula modify if we don't need column B, Just Column A and Sum of corresponding elements in Column D

 

[KRice]

The "without B" version is shown in H6 and Q6:

MrExcel_20220603.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2	A1	X1		1						A1	X1	x1	1
3				2			colA	A2		A1	X1		2			colA	A2
4				3			colB	X2		A1	X1		3			colB	X2
5				4			sum of D	26		A1	X1		4			sum of D	26
6	A2	X2		5			sum of D (w/o B)	26		A2	X2	x2	5			sum of D (w/o B)	26
7				6						A2	X2		6
8				7						A2	X2		7
9				8						A2	X2		8
10	A3	X3		9						A3	X3	x3	9
11				10						A3	X3		10
12				11						A3	X3		11
13	something	something		12						something	something		12
14				13						something	something		13
15				14						something	something		14
16				15						something	something		15
17	x	x								x	x
Sheet8

Cell Formulas
Range	Formula
J3:K5,J14:K16,J11:K12,J7:K9	J3	=J2
H5	H5	=LET(seq,ROW(A$2:A$100)-ROW(A$2)+1,cidx,FILTER(seq,(A$2:A$100<>"")*(B$2:B$100<>"")),sidx,FILTER(seq,(A$2:A$100=$H$3)*(B$2:B$100=H4)),eidx,INDEX(cidx,MATCH(sidx,cidx,0)+1),SUM(FILTER(D$2:D$100,(seq>=sidx)*(seq<eidx))))
H6	H6	=LET(seq,ROW(A$2:A$100)-ROW(A$2)+1,cidx,FILTER(seq,A$2:A$100<>""),sidx,FILTER(seq,A$2:A$100=$H$3),eidx,INDEX(cidx,MATCH(sidx,cidx,0)+1),SUM(FILTER(D$2:D$100,(seq>=sidx)*(seq<eidx))))
Q5	Q5	=SUMIFS(M$2:M$100,J$2:J$100,$Q$3,K$2:K$100,$Q$4)
Q6	Q6	=SUMIFS(M$2:M$100,J$2:J$100,$Q$3)

 

[Peter_SSs]

@cclp
In relation to your thread title ..
Please refer to #14 of the Forum Rules

 

[cclp]

The "without B" version is shown in H6 and Q6:

MrExcel_20220603.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2	A1	X1		1						A1	X1	x1	1
3				2			colA	A2		A1	X1		2			colA	A2
4				3			colB	X2		A1	X1		3			colB	X2
5				4			sum of D	26		A1	X1		4			sum of D	26
6	A2	X2		5			sum of D (w/o B)	26		A2	X2	x2	5			sum of D (w/o B)	26
7				6						A2	X2		6
8				7						A2	X2		7
9				8						A2	X2		8
10	A3	X3		9						A3	X3	x3	9
11				10						A3	X3		10
12				11						A3	X3		11
13	something	something		12						something	something		12
14				13						something	something		13
15				14						something	something		14
16				15						something	something		15
17	x	x								x	x
Sheet8

Cell Formulas
Range	Formula
J3:K5,J14:K16,J11:K12,J7:K9	J3	=J2
H5	H5	=LET(seq,ROW(A$2:A$100)-ROW(A$2)+1,cidx,FILTER(seq,(A$2:A$100<>"")*(B$2:B$100<>"")),sidx,FILTER(seq,(A$2:A$100=$H$3)*(B$2:B$100=H4)),eidx,INDEX(cidx,MATCH(sidx,cidx,0)+1),SUM(FILTER(D$2:D$100,(seq>=sidx)*(seq<eidx))))
H6	H6	=LET(seq,ROW(A$2:A$100)-ROW(A$2)+1,cidx,FILTER(seq,A$2:A$100<>""),sidx,FILTER(seq,A$2:A$100=$H$3),eidx,INDEX(cidx,MATCH(sidx,cidx,0)+1),SUM(FILTER(D$2:D$100,(seq>=sidx)*(seq<eidx))))
Q5	Q5	=SUMIFS(M$2:M$100,J$2:J$100,$Q$3,K$2:K$100,$Q$4)
Q6	Q6	=SUMIFS(M$2:M$100,J$2:J$100,$Q$3)

Thanks for your help!!

 

[cclp]

@cclp
In relation to your thread title ..
Please refer to #14 of the Forum Rules

Sorry about that, will keep that in mind henceforth