Need Excel to automatically select correct columns to sum their values by currency

[gerhardmiener]

Hi there,
I have the following problem.
On tab 1 I have weekly sales numbers for different items which are sold in different currencies

Tab1--------------------------col A--------col B---------col C
Week number -------------------2-----------3------------4
1 Oranges (sales in USD)-----1,300USD---2,660USD-----3,980USD
2 Apples (sales in EUR)-------1,000EUR---2,000EUR------3,000EUR
3 Pears (sales in JPY)-------11,800JPY---23,650JPY- ----35,700JPY

On tab 2 I need to re-calculate all sales numbers from tab 1 into one common currency (EUR) and to sum them to one number

Tab2--------------------------col A--------col B---------col C
Week number -------------------2-----------3------------4

FX EUR/USD--------------------1.3---------1.33---------1.327
FX EUR/JPY--------------------11.8-------11.825--------11.9


1 Total sales in EUR per week---3,000EUR--6,000EUR-----9,000EUR

Using sumif() function is possible but not a good solution as I have to manually select the columns to be summed. Does anyone see a way how to make Excel to automatically select the right column on tab 1 and to culculate the totals for the weekly sales?
To better illustrate my problem I could email the Excel spreadsheet with the example above as obviuosly there is no way to make any attachment in this forum
Thanks

 

[hiker95]

gerhardmiener,

Welcome to the MrExcel forum.

What version of Excel are you using?

there is no way to make any attachment in this forum

Can you post the raw data worksheet, and, post the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker
Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.


You can upload your workbook to Box Net,
sensitive data scrubbed/removed/changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[nemoy]

Are the rows always the same currency? Meaning is 1 Oranges always going to be USD? So you could have a label for the currency at the beginning of the row?

I assume you have the conversion rate somewhere for each currency. Is sheet 2 supposed to be all the same data, but with converted values? I would do something like:

I'd create a table with the conversion rates and name it "Rates": (I put it in sheet 3)

EUR	1
JPY	0.01
USD	0.75

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

Sheet1 would look like:

Week		1	2	3
Orange	USD	1300	2660	3980
Apple	EUR	1000	2000	3000
Pear	JPY	11800	23650	35700

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>

Sheet2:

Week		1	2	3
Orange	EUR	975	1995	2985
Apple	EUR	1000	2000	3000
Pear	EUR	118	236.5	357
Total		2093	4231.5	6342

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>

The key is the formula is Sheet2!C2 that is copied to all the others:

=VLOOKUP(Sheet1!$B2,Rates,2,FALSE)*Sheet1!C2

This looks up the currency as listed on the same row in Sheet1 in the Rates table that has been created and then multiplies by the amount in the same cell on Sheet1.

Hope that helps!

 

[gerhardmiener]

Are the rows always the same currency? Meaning is 1 Oranges always going to be USD? So you could have a label for the currency at the beginning of the row?

I assume you have the conversion rate somewhere for each currency. Is sheet 2 supposed to be all the same data, but with converted values? I would do something like:

I'd create a table with the conversion rates and name it "Rates": (I put it in sheet 3)

EUR	1
JPY	0.01
USD	0.75

<tbody>
</tbody>

Sheet1 would look like:

Week		1	2	3
Orange	USD	1300	2660	3980
Apple	EUR	1000	2000	3000
Pear	JPY	11800	23650	35700

<tbody>
</tbody>

Sheet2:

Week		1	2	3
Orange	EUR	975	1995	2985
Apple	EUR	1000	2000	3000
Pear	EUR	118	236.5	357
Total		2093	4231.5	6342

<tbody>
</tbody>

The key is the formula is Sheet2!C2 that is copied to all the others:

=VLOOKUP(Sheet1!$B2,Rates,2,FALSE)*Sheet1!C2

This looks up the currency as listed on the same row in Sheet1 in the Rates table that has been created and then multiplies by the amount in the same cell on Sheet1.

Hope that helps!

Thanks for providing your solution. However. I wouldn't like to maintain one more sheet (sheet2) with converted into EUR sales numbers

On link below you can find the spreadsheet with this example and how I see the solution. My problem here is: in the function sumif() how to automatically dertermine the column (whose values should be added) depending on the week number.
thnks
https://www.box.com/s/4gx97bzgis7htib5k4fr

 

[gerhardmiener]

On link below you can find the spreadsheet with this example and how I see the solution. My problem here is: in the function sumif() how to automatically dertermine the column (whose values should be added) depending on the week number.
thnks
https://www.box.com/s/4gx97bzgis7htib5k4fr

 

[nemoy]

OK, I didn't understand that you had data between the columns on the first sheet.

I was also confused about your Tab2, looks like it contains conversion factors as they were during that week, correct?

I'm assuming that Tab1 will typically have many more rows, is this correct?

To me, maintaining the Sheet with the EUR values shouldn't be a huge load as it is just formulas that are created once and left alone as long as it is made big enough in the beginning for the expansion you want to do. The formula that you would need to select the proper column to sum and the proper currency conversion to do will be a beast of a formula and beyond what I can work out. Possibly someone else will be able to get that beautiful formula (I want to see it too!) Otherwise, perhaps my solution can be adapted with the second sheet looking for the proper column to convert as well.

 

[gerhardmiener]

OK, I didn't understand that you had data between the columns on the first sheet.

I was also confused about your Tab2, looks like it contains conversion factors as they were during that week, correct?

I'm assuming that Tab1 will typically have many more rows, is this correct?

To me, maintaining the Sheet with the EUR values shouldn't be a huge load as it is just formulas that are created once and left alone as long as it is made big enough in the beginning for the expansion you want to do. The formula that you would need to select the proper column to sum and the proper currency conversion to do will be a beast of a formula and beyond what I can work out. Possibly someone else will be able to get that beautiful formula (I want to see it too!) Otherwise, perhaps my solution can be adapted with the second sheet looking for the proper column to convert as well.

yes, Tab 2 contains FX rates for every week
yes, Tab 1 will have many more rows and colums
Anyway, many thanks for your help

 

[Matty]

Hi,

Here's an option, albeit not a particularly pretty one...

In C9 of the weekly totals Worksheet, try:

=SUMIF('weekly sales per item and CCY'!$B$3:$B$5,"USD",INDEX('weekly sales per item and CCY'!$C$3:$H$5,,MATCH(C3,'weekly sales per item and CCY'!$C$2:$H$2,0)))/C5+SUMIF('weekly sales per item and CCY'!$B$3:$B$5,"JPY",INDEX('weekly sales per item and CCY'!$C$3:$H$5,,MATCH(C3,'weekly sales per item and CCY'!$C$2:$H$2,0)))/C6+SUMIF('weekly sales per item and CCY'!$B$3:$B$5,"EUR",INDEX('weekly sales per item and CCY'!$C$3:$H$5,,MATCH(C3,'weekly sales per item and CCY'!$C$2:$H$2,0)))

And copy across to E9.

There will be a way to convert the values on-the-fly rather than having to have three separate SUMIFs, but such an array formula may struggle with a lot of data.

Another option would be to create an intermediate table of converted values, which would then just require the use of a simple SUM formula.

Hope this helps.

Matty