Take the 2-minute tour ×
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
add comment

1 Answer

up vote 2 down vote accepted

That's possible.

Formula

Add the following formula in Sheet2!A5:

=SUM(ARRAYFORMULA(IFERROR(VLOOKUP(A1:A4;Sheet1!A:B;2*SIGN(ROW(A1:A4));0),"")))

Explained

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

Note

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

Example

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
add comment

Your Answer

 
discard

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.