# Summing up amounts if one row contains a key within another sheet

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!

``````=SUM(IFERROR(ARRAYFORMULA(IF(LEN(A2:A);
VLOOKUP(A2:A; Sheet1!A2:B; 2; 0); )); ))`````` • `VLOOKUP` searches for `A2:A` in `Sheet1!A2:B` and returns `2`nd 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
• Wow, this works! I don't understand it, though. One thing: how would I need to adapt the formula if there are other columns between `key` and `amount` in Sheet1? – Jonas Jan 22 '19 at 10:40
• @Jonas I added an explanation. and yes. its possible, you just need to return proper number of column in `VLOOKUP` and don't forget to extend `Sheet1!A2:B range` – user0 Jan 22 '19 at 12:36

You probably want something like the below. In the brackets, first part is the logical expression 2nd is what to return if true and last is what to return if false. If it is not "ABC" then is will test to see if it is "DEF". If both are false it will return 0.

``````=if(AA7="ABC",123,IF(AA7="DEF",456,0))
``````

Let me know if this needs clarifying.

• Yes, I'm not sure how this would yield a sum. I clarified my example somewhat to make clearer that it's not just one cell that needs to be returned. – Jonas Dec 18 '18 at 12:49

Next to the keys on your second spreadsheet, add the following formula (assumes keys are on column A and amount on B)

``````=SUMIFS(Sheet1!B:B,Sheet1!A:A,"="&Sheet2!A1)
``````

Update: to calculate the sum, of all matches, use the following (assumes your labels and values are on column A and B of Sheet1, and labels to sum are on column A of Sheet2)

``````=sum(ArrayFormula(sumif(Sheet1!A:A,"="&Sheet2!A:A ,Sheet1!B:B)))
``````
• My example was probably a little short - the matching key is not necessarily on the first column but can be anywhere within. I'll change the example! – Jonas Dec 17 '18 at 10:01
• Your updated formula looks good but unfortunately it just yields an "Error while parsing formula". I'm not sure why though, I've never used `ArrayFormula` before. – Jonas Dec 18 '18 at 12:50
• Hello Jonas! Would you mind sharing a (sanitized) spreadsheet so that I can better help you – Oren Pinsky Dec 18 '18 at 16:54