Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

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 have this spreadsheet:

enter image description here

I want to calculate the total income, but since the different rows are in different currencies the conversion must be done first. The currency values are mapped to codes in the Currencies table on the right side.

For example for A4 it would be: B4*F3.

I know I can get the currency value using VLOOKUP like this: VLOOKUP(C4, E3:F5,2). I can then multiply the amount by that returned value, but just row by row; I don't know how to sum all the amount x currency multiplications.

SUMPRODUCT is useless here because it requires same-size arrays of rows and it doesn't really do what I want (just in case anyone was going to suggest that).

I'm using LibreOffice (OpenOffice) but if you know how to do it in Excel it'll help anyway.

share|improve this question
up vote 4 down vote accepted

Well, I'm using the German version of OO-calc, and there is a function in the group table called 'SVERWEIS', which is 'S.-Verweis' or 'Spalten~' alias 'column-link'.

In column D, you write SVERWEIS(C4, E3:F5,2)*B3 for line 3, but you change it a little, to make the table absolute:

=VLOOKUP(C4, $E$3:$F$5,2)*B3

and copy it down the column, at the bottom (or at the top) you take the sum of the column.

You may hide columns if you don't want to show intermediate results, or use a white font on white background, but just a new column to transform the currencies.

share|improve this answer
Not really elegant, but gets the job done :P. Thanks! – Franco Jul 15 '11 at 6:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .