Sumproduct, Index for second (or third) row

M

mrsmcm

New Member
Joined
Jul 12, 2017
Messages
14
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"

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]A1[/TD]
[TD="width: 64"]B1[/TD]
[TD="width: 64"]C1[/TD]
[TD="width: 64"]D1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

"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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Try

Sheet2

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td]
Criteria​
[/td][td]
Row​
[/td][td]
Result​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
A​
[/td][td]
2​
[/td][td]
15​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
B​
[/td][td]
2​
[/td][td]
3​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
C​
[/td][td]
2​
[/td][td]
30​
[/td][/tr]
[/table]

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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
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"
[TABLE="width: 343"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Type[/TD]
[TD="align: right"]3/31/2017[/TD]
[TD="align: right"]6/30/2017[/TD]
[TD="align: right"]9/30/2017[/TD]
[TD="align: right"] 12/31/2017[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]B[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]C[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

"Sheet 2"

[TABLE="width: 312"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Type[/TD]
[TD]Date[/TD]
[TD]Currently (see below formula[/TD]
[TD]Would like to see[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD="align: right"]6/30/2017[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]B[/TD]
[TD="align: right"]9/30/2017[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]C[/TD]
[TD="align: right"]3/31/2017[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]

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))
 
Last edited:
Upvote 0
Try


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td]
Type​
[/td][td]
Date​
[/td][td]
Row​
[/td][td]
Formula​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
A​
[/td][td]
06/30/2017​
[/td][td]
2​
[/td][td]
10​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
B​
[/td][td]
09/30/2017​
[/td][td]
2​
[/td][td]
3​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
C​
[/td][td]
03/31/2017​
[/td][td]
2​
[/td][td]
10​
[/td][/tr]
[/table]

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Using SUMPRODUCT with LEN to check if the amount of characters is okay minus second row ?
Replies
3
Views
77
Peter_SSs
Peter_SSs
N
  • Solved
Sumproduct with Table Rows doesn't work
Replies
8
Views
249
Nicha
N
I
  • Question Question
Sum column by priority (Y-axis) and date (Y-axis)
Replies
3
Views
186
kvsrinivasamurthy
K
R
  • Solved
Approach to sorting and index/match
2
Replies
16
Views
405
Alex Blakenburg
A
F
  • Solved
Formula to find location of second row from the bottom containing specific text
Replies
5
Views
77
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top