How to combine two table information?

[EduPAz]

Hi,

Can someone please help me with this:

I have a table with these columns:

Product	Original currency	Jan-16	Feb-16	...	Dec-16	Total 2016 in original currency	Total 2016 in CAD
A	USD	3	5		8	XX	(3*1.3)+(5*1.28)+...+(8*1.294)
C	EUR	3.4	3.2		1	XXX	(3.4*1.4)+(3.2*1.42)+(1*1.5)

<tbody>
</tbody>

I have another table with the monthly exchanges rates:

	Jan-16	Feb-16	...	Dec-16
USD	1.3	1.28		1.294
EUR	1.4	1.42		1.5
CAD	1	1		1

<tbody>
</tbody>

How can I get in an efficient way the Total 2016 in CAD?

Thanks,

Edu

 

[JoeMo]

You need to extend the ranges in the formula below to cover all 12 months. Here B9:E9 hold s the exchange rates for US to CAD.
Sheet10

	A	B	C	D	E	F	G	H
1	Product	Original currency	16-Jan	16-Feb	16-Mar	16-Dec	Total 2016 in original currency	Total 2016 in CAD
2	A	USD	3	5		8	XX	20.652

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:41px;"><col style="width:113px;"><col style="width:49px;"><col style="width:49px;"><col style="width:49px;"><col style="width:49px;"><col style="width:84px;"><col style="width:114px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula H2 =SUMPRODUCT($C$2:$F$2,$B$9:$E$9) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[EduPAz]

Hi,

Thank you for your answer.

The formula works for USD (column B). How could the EUR be incorporated into the formula?

Thanks,

Edu

 

[JoeMo]

Hi,

Thank you for your answer.

The formula works for USD (column B). How could the EUR be incorporated into the formula?

Thanks,

Edu

You are welcome - thanks for the reply.

For EUR use same formula but change the ranges to adhere to the position of the raw data in the two tables.

 

[Peter_SSs]

How could the EUR be incorporated into the formula?

Like this

<b>Total Currency</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:119px;" /><col style="width:59px;" /><col style="width:59px;" /><col style="width:60px;" /><col style="width:60px;" /><col style="width:124px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >H</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">Product</td><td style="font-size:10pt; ">Original currency</td><td style="font-size:10pt; text-align:right; ">Jan-16</td><td style="font-size:10pt; text-align:right; ">Feb-16</td><td style="font-size:10pt; ">...</td><td style="font-size:10pt; text-align:right; ">Dec-16</td><td style="font-size:10pt; text-align:right; ">Total 2016 in CAD</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">A</td><td style="font-size:10pt; ">USD</td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; text-align:right; ">5</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">8</td><td style="font-size:10pt; text-align:right; ">20.652</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">C</td><td style="font-size:10pt; ">EUR</td><td style="font-size:10pt; text-align:right; ">3.4</td><td style="font-size:10pt; text-align:right; ">3.2</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; text-align:right; ">10.804</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">Jan-16</td><td style="font-size:10pt; text-align:right; ">Feb-16</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">Dec-16</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; ">USD</td><td style="font-size:10pt; text-align:right; ">1.3</td><td style="font-size:10pt; text-align:right; ">1.28</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">1.294</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; ">EUR</td><td style="font-size:10pt; text-align:right; ">1.4</td><td style="font-size:10pt; text-align:right; ">1.42</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">1.5</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; ">CAD</td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >H2</td><td >=SUMPRODUCT($C2:F2,INDEX<span style=' color:008000; '>(B$9:E$11,MATCH<span style=' color:#0000ff; '>(B2,A$9:A$11,0)</span>,0)</span>)</td></tr></table></td></tr></table>