Sum a range of a nested index match

[Grengis]

Hi
I have data laid out as follows

Record date	Paid date	Income 1	Income 2	Income 3
Investment 1
30/06/2022	12/07/2022	0	5	10
Subtotal		0	5	10
Investment 2
31/12/2021	12/01/2022	5	10	20
30/06/2022	12/07/2022	5	4	6
Subtotal		10	14	26

I'm trying to create a formula in another worksheet which will have the following output

Investment name (manually entered)	Income 1 (formulas)	Income 2 + 3 (formulas)
Investment 1	0	15
Investment 2	10	40

I need a formula which will:

1. Look up the investment name in the first table
2. Find the "Subtotal" line related to this line. Please note, this isn't an excel subtotal line rather, the data is simple called "subtotal"
3. From the subtotal line, return a particular income figure (so Income 1 in table 2 above) or sum of multiple columns (income 2 and 3 in table 2 above).

I believe it's necessary to use an Index/Match formula combination to do this. Some googling of this has allowed me to simple return a single value (e.g. Income 1 figure) with the following formula:

=INDEX(INDEX(C1:C7,MATCH(G1,A1:A7,0)):C7,MATCH("Subtotal",INDEX(A1:A7,MATCH(G1,A1:A7,0)):A7,0))

but I don't fully understand how this is working which also means I can't figure out sum part.

I'd appreciate some advice on:

1. How to achieve the Income 2+3 formulas in table two
2. Some explanation as to how the Income 1 formula index/match works.

If there's a better way to achieve the result rather than index/match, I'm all ears. Thank you.

 

[awoohaw]

try this:
I added a couple extra rows for Inv 1 as part of my testing:
This requires SUBTOTAL row immediately after the last investment row of each section.
And I am using a blank row after the last SUBTOTAL.

Book1
	A	B	C	D	E
1	Record date	Paid date	Income 1	Income 2	Income 3
2	Investment 1
3	2022-06-30	2022-07-12	0	5	10
4	2022-09-30	2022-10-15	1	2	3
5	2022-11-30	2022-12-10	4	5	6
6	Subtotal		5	12	19
7	Investment 2
8	2021-12-31	2022-01-12	5	10	20
9	2022-06-30	2022-07-12	6	4	6
10	Subtotal		11	14	26
11
12	Investment 1	5	31
13	Investment 2	11	40
Grengis

Cell Formulas
Range	Formula
C6:E6	C6	=SUBTOTAL(9,C3:C5)
C10:E10	C10	=SUBTOTAL(9,C8:C9)
B12	B12	=SUM(INDEX($A$1:$E$11,MATCH($A12,$A$1:$A$11,0)+1,3):INDEX($A$1:$E$11,MATCH($A13,$A$1:$A$11,0)-2,3))
C12	C12	=SUM(INDEX($A$1:$E$11,MATCH($A12,$A$1:$A$11,0)+1,4):INDEX($A$1:$E$11,MATCH($A13,$A$1:$A$11,0)-2,5))
B13	B13	=SUM(INDEX($A$1:$E$11,MATCH($A13,$A$1:$A$11,0)+1,3):INDEX($A$1:$E$11,IF($A14="",COUNTA($A$1:$A$11)-1,MATCH($A14,$A$1:$A$11,0)-2),3))
C13	C13	=SUM(INDEX($A$1:$E$11,MATCH($A13,$A$1:$A$11,0)+1,4):INDEX($A$1:$E$11,IF($A14="",COUNTA(A1:A11)-1,MATCH($A14,$A$1:$A$11,0)-2),5))

 

[awoohaw]

try this:
I added a couple extra rows for Inv 1 as part of my testing:
This requires SUBTOTAL row immediately after the last investment row of each section.
And I am using a blank row after the last SUBTOTAL.

Book1
	A	B	C	D	E
1	Record date	Paid date	Income 1	Income 2	Income 3
2	Investment 1
3	2022-06-30	2022-07-12	0	5	10
4	2022-09-30	2022-10-15	1	2	3
5	2022-11-30	2022-12-10	4	5	6
6	Subtotal		5	12	19
7	Investment 2
8	2021-12-31	2022-01-12	5	10	20
9	2022-06-30	2022-07-12	6	4	6
10	Subtotal		11	14	26
11
12	Investment 1	5	31
13	Investment 2	11	40
Grengis

Cell Formulas
Range	Formula
C6:E6	C6	=SUBTOTAL(9,C3:C5)
C10:E10	C10	=SUBTOTAL(9,C8:C9)
B12	B12	=SUM(INDEX($A$1:$E$11,MATCH($A12,$A$1:$A$11,0)+1,3):INDEX($A$1:$E$11,MATCH($A13,$A$1:$A$11,0)-2,3))
C12	C12	=SUM(INDEX($A$1:$E$11,MATCH($A12,$A$1:$A$11,0)+1,4):INDEX($A$1:$E$11,MATCH($A13,$A$1:$A$11,0)-2,5))
B13	B13	=SUM(INDEX($A$1:$E$11,MATCH($A13,$A$1:$A$11,0)+1,3):INDEX($A$1:$E$11,IF($A14="",COUNTA($A$1:$A$11)-1,MATCH($A14,$A$1:$A$11,0)-2),3))
C13	C13	=SUM(INDEX($A$1:$E$11,MATCH($A13,$A$1:$A$11,0)+1,4):INDEX($A$1:$E$11,IF($A14="",COUNTA(A1:A11)-1,MATCH($A14,$A$1:$A$11,0)-2),5))

To see how each formula works, use the Evaluate formula section and cycle through each step. You will see that by using the colon ( : ) in the formula that the index function creates a RANGE and not values (not sure if that is the exact definition of the reference functionality of the INDEX function, but it works for me.)

 

[Peter_SSs]

Welcome to the MrExcel board!

With MS365 you could try these

23 07 15.xlsm
	A	B	C	D	E
1	Record date	Paid date	Income 1	Income 2	Income 3
2	Investment 1
3	30/06/2022	12/07/2022	0	5	10
4	30/09/2022	15/10/2022	1	2	3
5	30/11/2022	10/12/2022	4	5	6
6	Subtotal		5	12	19
7	Investment 2
8	31/12/2021	12/01/2022	5	10	20
9	30/06/2022	12/07/2022	6	4	6
10	Subtotal		11	14	26
11
12		Inc 1	Inc 2 + 3
13	Investment 1	5	31
14	Investment 2	11	40
Income

Cell Formulas
Range	Formula
B13:B14	B13	=INDEX(FILTER(C$2:C$10,(A$2:A$10="Subtotal")*(ROW(C$2:C$10)>MATCH(A13,A:A,0)),0),1)
C13:C14	C13	=SUM(INDEX(FILTER(D$2:E$10,(A$2:A$10="Subtotal")*(ROW(D$2:E$10)>MATCH(A13,A:A,0)),0),1,0))