0

I'd like to lookup several values in a second table and add them up. let me go into more detail

I have one table with numeric values and id's 

 id||amount
>----------<
  3||12
 34||8
 16||40
678||34

the id's are unique

I now want to build a partial sum of the amount giving a list of id's

 list of ids|| sum
 >---------------------<
     3,34,16||60
     34,16  ||48
     678    ||34

I figured out how to to look up the value of one id using match and index.

=INDEX(Data.A1:D6;MATCH(results.A4;Data.A1:A6;0);2)

the numeric values table is in the sheet "Data" the sum shall be displayed in the sheet "results". Currently the cell "results.A4" contains only one id. How can I have that "results.A4" contains a list and return the sum of the looked up values?

Improve this question

1 Answer 1

Reset to default
1

Use SUMPRODUCT with a SEARCH():

=SUMPRODUCT(ISNUMBER(SEARCH("," & DATA!$A$2:$A$5 & ",";"," & results!A2 & ","))*DATA!$B$2:$B$5)

enter image description here

Improve this answer
1
  • Thank you very much, one should add there may be no spaces in the list
    – Nivatius
    Commented Aug 26, 2016 at 16:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .