Looking for the right formula

[Adimm]

I have created a budget file with multiple sheets, each sheet has rows by category and columns for each month.
The expenses I wish to track can be in one of the following currencies: $, €, ₪, I would like to enter the cost is the currency I have paid at, let's say € so could always know the original currency. now when I sum the columns I wish to multiply each relevant amount with its rate so the sum of the column will be in $ (the currency I manage my budget). I have a table of rates on the last sheet.
I have looked and looked and couldn't find the right formula to give me the desired result.
here is the table:
[TABLE="class: outer_border, width: 940"]
<colgroup><col><col><col span="5"></colgroup><tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="colspan: 2, align: center"]B[/TD]
[TD="colspan: 2, align: center"]C[/TD]
[TD="colspan: 2, align: center"]D[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD="colspan: 2, align: center"]Jan-18[/TD]
[TD="colspan: 2, align: center"]Feb-18[/TD]
[TD="colspan: 2, align: center"]Mar-18[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD] [/TD]
[TD]Budget[/TD]
[TD]Actual[/TD]
[TD]Budget[/TD]
[TD]Actual[/TD]
[TD]Budget[/TD]
[TD]Actual[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]SOFTWARE[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Design/photo editing (e.g. Photoshop, Illustrator, InDesign)[/TD]
[TD] € 10.00[/TD]
[TD] € - [/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Adobe license (Creative directore)[/TD]
[TD] ₪ 70.00[/TD]
[TD] ₪ 70.00[/TD]
[TD] ₪ 70.00[/TD]
[TD] ₪ 70.00[/TD]
[TD] ₪ 70.00[/TD]
[TD] ₪ 70.00[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Animation (e.g. After Effects)[/TD]
[TD] $ 10.00[/TD]
[TD] $ - [/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Wireframing (e.g. Balsamiq)[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Prototyping (e.g. InVision)[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Project management (e.g. Basecamp)[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]HARDWARE[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Graphics-optimized computer (e.g. MacBook Pro)[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]HD display[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]SD cards/external hard drives[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]EQUIPMENT RENTALS / PURCHASES[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Camera[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Tripod[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Microphone[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Lighting[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]TOTAL[/TD]
[TD] $ 90.00[/TD]
[TD] $ 70.00[/TD]
[TD] $ 70.00[/TD]
[TD] $ 70.00[/TD]
[TD] $ 70.00[/TD]
[TD] $ 70.00[/TD]
[/TR]
</tbody>[/TABLE]

Total (line 19) formula is =SUM(B7:B12,B14:B16,B18:B21)
[TABLE="class: grid, width: 327"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]From[/TD]
[TD]to[/TD]
[TD]date[/TD]
[TD]Exchange rate[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]EUR[/TD]
[TD]USD[/TD]
[TD="align: right"]29/07/2018[/TD]
[TD]1.17[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ILS[/TD]
[TD]USD[/TD]
[TD="align: right"]29/07/2018[/TD]
[TD]0.273[/TD]
[/TR]
</tbody>[/TABLE]

So the sum i want to calulate:
B7 identifies it is in euro and multiply by the right rate on E2 (another sheet)+B8 identifies it is in shekel and multiply by the right rate on E3+B9 identifies it is Doller so no need to do anything.

I'm using excel 2016

I hope it is clear and that someone here can help me,
Thanks in advance

 

[Special-K99]

You can identify currency using CELL("format",B7)

My Excel says
",2" is dollars
"C2" is pounds
However it also says "C2" is schekel (ILS - i cant find schekel symbol)

So CELL("format",B7) may not work for all currencies.

I would reformat the table and have a separate column to indicate what the currency is.
You can then use VLOOKUP to find the currency in the second table and multiply by the required rate.

NOTE: If you include $ in the rate table with Exchange rate 1.00 you can have one simple formula that looks up any rate, regardless of currency.

 

[Adimm]

Thank, I was hoping for an easier solution as adding columns mean adding 24 columns for each sheet of the budget.

What do you mean by "NOTE: If you include $ in the rate table with Exchange rate 1.00 you can have one simple formula that looks up any rate, regardless of currency." how?

 

[Special-K99]

1. IDENTIFYING CURRENCY

I don't see how you're gonna distinguish currency from a cell unless you add columns or can read the format of the cell.
As stated earlier some currencies produce the same cell format.
If you're just working with schekel and $ and euros you might be ok. You'll need to test the formats of the cell for the different currencies you're using.
Bear in mind it may work now but if you plan to add currencies in the future you may run into problems.


2. RE - ADDING $ TO CURRENCY TABLE

If the currency is a $ then the value stays the same as you said earlier "no need to do anything"

This means you first need to check if the currency is a dollar, if not then perform some form of VLOOK/INDEX-MATCh to get the currency.
So you need to say something like "IF(currency<>dollar, VLOOKUP..." something like that.
However, if you add the $ to the currency table with an exchange rate of 1.00 you can simply perform the VLOOKUP/INDEX-MATCH against the table irrespective of what the currency is thereby removing the need for checking if the currency is a dollar or not.
Admittedly for dollars you're multiplying a value by 1 which doesn't change anything but it does allow you to remove the IF condition.

Assume A1 is a currency symbol, $ £ etc and B1 is a value.
You could either say

B1*IF(A1<>"$",VLOOKUP...,1) requiring an IF condtion

or

if you add the $ and 1.00 to the exchange rate table it's simply

B1*VLOOKUP(...)

regardless of the currency

 

[Adimm]

Great, I went for your second solution, added $ = 1.00 in the exchange rate table and used the Vlookup, it worked great.
Now how do I sum it all? do I need to create now formula manually for each one of the cells same I started doing below?
=C7*VLOOKUP(B7, 'Exchange rate'!A:E, 5, 0)+C8*VLOOKUP(B8, 'Exchange rate'!A:E, 5, 0)+C9*VLOOKUP(B9, 'Exchange rate'!A:E, 5, 0)

Tx,
Adi

 

[Special-K99]

I think you can do this

Checking it out now...

 

[Special-K99]

Try this

=SUMPRODUCT(VLOOKUP(INDEX($B7:$B9,N(IF(1,ROW(B7:B9)-6))),'Exchange rate'!Sheet2!A$1:E$3,5,0)*(C7:C9))
Array formula, use Ctrl-Shift-Enter

I tried googling but couldnt find an answer so I asked on another forum for a similar setup, total cost for a shopping list of fruit against a cost of fruit table

 

[Adimm]

Try this

=SUMPRODUCT(VLOOKUP(INDEX($B7:$B9,N(IF(1,ROW(B7:B9)-6))),'Exchange rate'!Sheet2!A$1:E$3,5,0)*(C7:C9))
Array formula, use Ctrl-Shift-Enter

I tried googling but couldnt find an answer so I asked on another forum for a similar setup, total cost for a shopping list of fruit against a cost of fruit table

Thank you very much for your help, it's not obvious you took the time to look for the solution