Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer

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