1

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!

1

=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
  • 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 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 at 12:36
1

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
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.