How to sum the multiplications of rows, being the multiplier variable in a spreadsheet

Question

I have this spreadsheet:

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.

Answer 1

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.