Sign up ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I'd like to sum up some named values by referring to their names in a separate sheet. Can I do this with a single formula in the second sheet?

Sheet 1:

|  A  | 3 |
|  B  | 5 |
|  C  | 2 |
|  D  | 4 |

Sheet 2:

|  A  |
|  A  |
|  C  |
|  B  | 
| =13 |
share|improve this question

1 Answer 1

up vote 2 down vote accepted

That's possible.


Add the following formula in Sheet2!A5:



  1. the VLOOKUP retrieves the corresponding value
  2. the SIGN returns an array of the same length to that of the VLOOKUP, so that the ARRAYFORMULA works
  3. the IFERROR returns nothing upon error
  4. the ARRAYFORMULA will take on a range, rather than a cell
  5. the SUM sums it up !!


Make sure the ranges are fixed (A1:A4) or place the result of the ARRAYFORMULA somewhere else.


I've prepared an example file for you and added a second column in sheet2, so that you can see what happens. Try replacing an A with an E. Example file: SUM VLOOKUP

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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