Sumproduct, Index for second (or third) row

[mrsmcm]

Good afternoon, I was hoping you all could assist with the following problem. I have tried to outline the situation below. "Sheet 1" is the table of data. "Sheet 2" is where I would like to sum the second row of each set. Currently, I am able to sum the first row no problem using sumproduct and index. Is there another solution to sum the second row instead? Please let me know if I can provide additional information. TIA.

"Sheet 1"

A1	B1	C1	D1
A	10	20	30
	5	5	5
	1	1	1
B	5	5	5
	1	1	1
	10	10	10
C	1	1	1
	10	10	10
	5	5	5

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

"Sheet 2"
A =SUMPRODUCT((Sheet1!A2:A12=Sheet2!A2)*INDEX(Sheet1!B2:D12,0,1):INDEX(Sheet1!B2:D12,1,0))
A = 60

Want A to = 15

 

[Marcelo Branco]

Try

Sheet2

	A​	B​	C​
1​	Criteria​	Row​	Result​
2​	A​	2​	15​
3​	B​	2​	3​
4​	C​	2​	30​

Formula in C2 copied down
=SUM(INDEX(Sheet1!B$2:D$12,MATCH(A2,Sheet1!A$2:A$12,0)+B2-1,0))

Hope this helps

M.

 

[mrsmcm]

Thanks, Marcelo, unfortunately this doesn't seem to be working. I am getting #N/A.

 

[mrsmcm]

The formula works on my simplified version, but not on the more complex actual workbook. It's actually returning a number, but I can't figure out where that number is coming from. Any other suggestions would be much appreciated. I greatly appreciate your help.

 

[mrsmcm]

I got it working. Must have been something I was doing wrong on my end. Thank you!!

 

[Marcelo Branco]

You are welcome.

M.

 

[mrsmcm]

I just realized I have a part two. What if the B1, C1, D1 -> Z1 were dates, specifically quarter end dates, i.e. 3/31/17; 6/30/17; 9/30/17; 12/31/17; etc. How could I tell the formula to stop at 9/30/2017, or some other date of my choosing? This would be a criteria on Sheet 2 - A1 - A; B1 - 9/30/17

Any thoughts? TIA

 

[Marcelo Branco]

Try to provide a small data sample along with expected results.

M.

 

[mrsmcm]

Hopefully this makes sense...I would like the formula to stop at a specified date. The formula I'm currently using is below and sums the second row in each table. I can not change the output of this information.

Any help is greatly appreciated.

"Sheet 1"

	A	B	C	D	E
1	Type	3/31/2017	6/30/2017	9/30/2017	12/31/2017
2	A	10	20	30	40
3		5	5	5	5
4		1	1	1	1
5
6	B	5	5	5	5
7		1	1	1	1
8		10	10	10	10
9
10	C	1	1	1	1
11		10	10	10	10
12		5	5	5	5

<tbody>
</tbody>

"Sheet 2"

	A	B	C	D
1	Type	Date	Currently (see below formula	Would like to see
2	A	6/30/2017	20	10
3	B	9/30/2017	4	3
4	C	3/31/2017	40	10

<tbody>
</tbody>

Current formula (to sum second row of data) =SUM(INDEX(Sheet1!$B$3:$E$13,MATCH(Sheet2!A3,Sheet1!$A$3:$A$13,0)+2-1,0))

 

[Marcelo Branco]

Try

	A​	B​	C​	D​
1​	Type​	Date​	Row​	Formula​
2​	A​	06/30/2017​	2​	10​
3​	B​	09/30/2017​	2​	3​
4​	C​	03/31/2017​	2​	10​

Formula in D2 copied down
=SUM(INDEX(Sheet1!B$2:E$12,MATCH(A2,Sheet1!A$2:A$12,0)+C2-1,1):INDEX(Sheet1!B$2:E$12,MATCH(A2,Sheet1!A$2:A$12,0)+C2-1,MATCH(B2,Sheet1!B$1:E$1,0)))

M.