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