Scan a column and sum values in other column


I have a table with values such as "EUR/USD" or "GBP/EUR", "CAD/USD" etc. in col Asset and the relative amount in col Open Value. I want to be able to scan col Asset and add up values in col Open Value. The inbuilt table filter would not be helpful here becasue the combination of currency pairs is high. 

Asset       ¦ Buy TimeStamp ¦ Buy        ¦ Units    ¦ Open Value

EUR/GBP¦ Fri 08 03-13:42  ¦ 0.85895 ¦ 50,000 ¦ 42,947.50

EUR/USD¦ Fri 08 03-14:26  ¦ 1.12311 ¦ 50,000 ¦ 56,155.50

Ideally I would scan the Asset col with a RIGHT() function in order to extract its righmost value, i.e. "GBP", "USD" but is not strictly necessary I can do that in some other col. 

I then want to sum all values in Open Value relevant to "GBP", "USD" etc. This is necessary because it makes no sense to add up a total of values in different currencies. 

If I could update data by automatically add a currency not previously listed with relative sum such as example below - that would be great

GBP ¦ 2,158.25

USD ¦ 15,657.65

EUR ¦ 1,298.36

CAD ¦ 26,357.41

new currency not listed before ¦ total

Any ideas anyone?

Thanks - Michael

[p.s. this editor is dreadful]



Selected Answer

Hi Michael and welcome! The editor is meant for basic text and code blocks because it is much more helpful to simply upload a sample Excel file instead of trying to output some type of table-like formatting in a paragraph context.

For your table, I sugges you create two helper columns, one that has the currency on the left and one for the currency on the right. You can use a simple formula to breaks these values out into those columns or use Text-to-Columns for it. Once you do this, you can use simple and easily maintainable formulas like SUMIF() SUMIFS() or convert the data to a Table format with a Totals row at the bottom and perform basic filtering.

If I misunderstaood your question, then please edit your question, elaborate on the issue, and upload a sample file that shows what you're trying to do.



Thank you for the solution and also for the tip about uploading a file, my bad, I'll keep it in mind for the future
Michael_Php (rep: 2) Mar 11, '19 at 5:58 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login