CODEWITHSUNDEEP
excelexcel-formula
Natan
Natan

Reputation: 71

vlookup multiple values and sum them against one reference

I have a sheet with all unique ID's and I'd need to run a vlookup against that ID in sheet2.

Sheet 2 looks like below:

 ID:     NUMBERS:
  A         488
  A         980
  A         32
  B         693
  C         78
  C         45

IF A is found multiple times in sheet2, sum all values from NUMBERS column with reference to A and return in front of A in sheet1.

I am not sure if index(match) would be able to do this because I'd need sums in the end against a single reference not multiple times pull multiple values.

Please suggest a workaround. Thanks!

Upvotes: 0

Views: 1350

Answers (2)

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

You can use Pivot table as below. First select data and destination sheet as shown below. Here data is in Sheet 2 and summary will be populated in Sheet 1.

enter image description here

Once done then Pivot Menu will appear. Put field ID in "Rows" and Numbers in "Values" as shown in the image below.

enter image description here

Upvotes: 0

Harun24hr
Harun24hr

Reputation: 36870

Use SUMIF() formula to achieve your desired result.

=SUMIF(Sheet2!A:A,A2,Sheet2!B:B)

enter image description here

Upvotes: 3

Related Questions