SUM of different currencies in single column, SUM of filtered data with criteria.

[Rahul Adapala]

Hi every one,

I'm preparing a database for my company's sales data. we deal in different currencies, but I want all the accounting values of one category in single column. This led to having different currencies in single column. Also I have categorized data into fiscal quarters with a formula. Now I have got two questions.
1. How to get the sum of different currencies($, £, ₹) which are in a single column.
2. How to filter the data based on fiscal quarters and then extract the sum of the order values in that quarter. Again these order values are in different currencies($, £, ₹) in single column. The solution should be dynamic.

Thank you

 

[yky]

If you put different currency in different column, you'd not need to ask these questions, would you? Having said that, I see two solutions for you. First, you put different currency in different column and hide the columns. In another column, you use formula like "=k1", "=l2", "=m3", etc. to "copy" the currencies to a single column. Then, you can sum different currency easily by summing the data in the hidden column. Second, you can use SUMIF to sum different currency if there is a way to tell what currency is in what cells. For example, if column C contains the currency code (USD, INR, GBP) and the amount is in column E, you can do "=SUMIF(C2:C30, "=USD", E2:E30). For quarterly data, you can use SUMIFS so you can have date and currency as the criteria.