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

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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.