Index Match to Sum full row

masterschef

New Member
Joined
Jan 9, 2018
Messages
3
[FONT=Arial, Helvetica Neue, Helvetica, sans-serif]Hi all,[/FONT]


[FONT=Arial, Helvetica Neue, Helvetica, sans-serif]I'm trying to build (what I thought to be a simple) Excel formula that matches a line item from one sheet with another line item from a second sheet and sums up all values in that matched row.[/FONT]

[FONT=Arial, Helvetica Neue, Helvetica, sans-serif]This formula needs to be flexible to accommodate new columns so I was hoping to use a mix of SUMIF and INDEX/MATCH.
[/FONT]

SHEET 1
LINE ITEM
ASummed value from sheet 2
BSummed value from sheet 2
CSummed value from sheet 2
DSummed value from sheet 2
SHEET 2
Line ItemValue 1Value 2Value 3Value 4
A423423
B423
C23432442323423
D23423

<tbody>
</tbody>


Please help!







Summed value from sheet 2

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

masterschef

New Member
Joined
Jan 9, 2018
Messages
3
Sorry for the followup

Is there a way to rewrite this so that it doesn't reference the enter column? I'm working with a large workbook that has ballooned to over 654MB. If I try to reference a cell range instead of full column, no data is returned
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Sorry for the followup

Is there a way to rewrite this so that it doesn't reference the enter column? I'm working with a large workbook that has ballooned to over 654MB. If I try to reference a cell range instead of full column, no data is returned

Of course...

=SUM(INDEX(Sheet2!$B$2:$E$5,MATCH($A2,Sheet2!$A$2:$A$5,0),0))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,900
Messages
5,598,761
Members
414,258
Latest member
Dbarton0231

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
Top