SUM with INDEX and MATCH

0
votes

I have 2 worksheets that are connected through a unique ID column shared by the two sheets. I made the two below spreadsheets to test the formula as my actual data is quite long.

enter image description here enter image description here

I am using the INDEX and MATCH formulas to pull a value from Sheet2 to populate a cell in Sheet1 if the ID value found in the row of Sheet1 matches an ID value found in Sheet2. I can accomplish this with the following formula

=INDEX(Sheet2!B:B,MATCH(Sheet1!A:A,Sheet2!A:A,0))

However, what I am actually after is for the INDEX to SUM of all values in the WGT column on Sheet2 on Sheet1 when there is a MATCH between the two ID columns (I know with this test data there are two instances of every ID on Sheet2). The above formula only INDEXes the first WGT value when a MATCH is found between the ID columns.

I am pretty sure I am supposed to use some variation of the SUMIF formula but I can't seem to get one that works at all. Thanks for any help!

2
excelexcel-formula
Use SUMIFS – Scott Craner

2 Answers

0
votes

The syntax for Sumif is Sumif(criteria range, criterion, [sum range]). So, you would need in Sheet2, cell B2

=sumif(Sheet1!A:A,Sheet2!A2,Sheet1!B:B)

In words: generate a sum or all numbers in column B of Sheet1, where column A of Sheet1 equals the value of cell A2 on Sheet2.

Copy that down.

Alternatively, you could click in the data of Sheet1 and insert a pivot table. Drag the ID field into the Rows area and the WGT field into the Values. Just a few clicks, no formula.

0
votes

Another option to use Sum + Index function

In sheet1, B2 array formula copied down :

=SUM(INDEX(Sheet2!B:B,N(IF(1,(Sheet2!A:A=A2)*ROW(Sheet2!A:A)))))

This is an array formula and needs to be confirmed by pressing with Ctl + Shift + Enter.

enter image description here