Currency Sum

[James bolton]

VLOOKUP COUNT

Hi,

LEVEL 1.

I have a list of payments that I am trying to summarise, column A shows the Vendor Name, & column B the Payment.

Many Vendors appear on the list several times, and I want to see the total of their payments without using a Pivvott Table.

I have listed all the unique vendors on a summary tab, what formula can I use to show, next to the vendor, the total payments they have made.

LEVEL 2.

What makes this harder, on column C, I have listed the currency they have paid in (either EUR, USD or GBP) - I would ideally like to include in the formula a calculation that automatically multiplies them by a fixed FX rate, which would be on cell G1(GBP), G2(USD) and G3(EUR) - don't worry if it is too complicated to put it in the same formula, I can just create a new column and create a seperate formula to find out the FX rate.

LEVEL 3.

What makes this even harder still, there are 4 different worksheets for the different types of products. Vendors can appear on more than 1 worksheet. Is there a way to include the calculation in the formula to search across all 4 worksheets.

-- I know this is quite complicated, please let me know if you would like an example spreadsheet

 

[Chihiro]

It'd help if you can upload sample workbook with enough data.

 

[James bolton]

I thought you might ask...

Please see attached the spreadsheet.

I want to see the sum of each product and their USD total.

What's awkward about this spreadsheet is that on the last 2 worksheets, there are sales, fees and service charges. I only want to include the sales - otherwise it will double count client total order number and won't show the true sale total.

I don't mind if you amend the summary pages as much as possible but the product worksheets are in the format of a system extract and can't be changed.

Any help would be much appreciated.

>Many thanks.

 

[David Evans]

JB -

Have you considered combining your data (Product 1, 2, 3, etc) into 1 Excel Table with 5 columns being the current 4 that Product 3 and 4 have and and extra column for Product? You could add a further element being the USD value of each record. Simplifying your data structure will radically simplify the challenge of summarizing the data in the manner you desire.

And of course, the big question is what is the basis for the aversion to using Pivot Tables?

I've re-created your data - the green tabs are my solution - the yellow your original sheet.

My experience with these situations is that it is easier to get the data to arrive in a manner that is useful than to write formulae to adapt disparate data sources - I'm happy to defer to other opinions on this matter, though

 

[James bolton]

Hi,

Thankyou - I would combine my data and use a pivvot table/consolidated pivvot table. The problem is, my original data source are system extracts with around 20 columns each, 1000s of rows of data and none of the product extracts have the same layout format- the data is also constantly changing, so I don't want to have to re-create the whole table and then pivvot table each month, ideally - I want a master summary that is fed through via each product worksheet - and all I would have to do is dump the system extract into each worksheet.

Kind Regards
Jobo

 

[David Evans]

Hi,

Thankyou - I would combine my data and use a pivvot table/consolidated pivvot table. The problem is, my original data source are system extracts with around 20 columns each, 1000s of rows of data and none of the product extracts have the same layout format- the data is also constantly changing, so I don't want to have to re-create the whole table and then pivvot table each month, ideally - I want a master summary that is fed through via each product worksheet - and all I would have to do is dump the system extract into each worksheet.

Kind Regards
Jobo

Changing data is to be expected - varying structures is not conducive to good analysis. Trying to overcome poor data structure with Excel formulae is a Sisyphean task which you will face on whatever cycle (Monthly?) your data is generated. Best to bite the bullet up front and address the true problem.

Why don't you share your system extract sources so that we can help you structure the data properly before it gets to excel? 20 columns and 1000s of records are no problem - inconsistent layouts are an issue but not insurmountable. Its very likely that a simple piece of code can assist in doing it, in my experience.