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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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
88
Peter_SSs
Peter_SSs
N
  • Solved
Sumproduct with Table Rows doesn't work
Replies
8
Views
311
Nicha
N
I
  • Question Question
Sum column by priority (Y-axis) and date (Y-axis)
Replies
3
Views
255
kvsrinivasamurthy
K
M
  • Question Question
Sum together the larger amount of two ranges by row
Replies
3
Views
73
Cubist
Cubist
R
  • Solved
Approach to sorting and index/match
2
Replies
16
Views
459
Alex Blakenburg
A

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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