Google-sheets – Summing up amounts if one row contains a key within another sheet

formulasgoogle sheetsgoogle-sheets-arrayformulavlookup

I have two sheets in one Google Sheets document:

| Key | Amount  |
|-----|---------|
| ABC |    1    |
| DEF |    2    |
| GHI |    3    |


|  Keys |
|-------|
|  ABC  |
|  XYZ  |
|  JKL  |
|  GHI  |

Now, I'd like to add the amounts in the rows of the first sheet but only where the key is contained anywhere in the second sheet. For the example above, the sum would be 4 (keys ABC with amount 1 and GHI with amount 3).

I've been trying QUERY, SUMIF and VLOOKUP but haven't been able to find a solution. Any help would be greatly appreciated!

Best Answer

=SUM(IFERROR(ARRAYFORMULA(IF(LEN(A2:A); 
 VLOOKUP(A2:A; Sheet1!A2:B; 2; 0); )); ))

enter image description here

  • VLOOKUP searches for A2:A in Sheet1!A2:B and returns 2nd column from Sheet1!A2:B as it is eg. 0 for not sorted
  • LEN(A2:A) checks how many entries are in the range which stops ARRAYFORMULA after last entry
  • ARRAYFORMULA makes sure that whole formula will not stop after 1st row
  • IFERROR removes all errors if there is no match in VLOOKUP part
  • SUM sums it all
Related Topic