Need to Sum data by Number Format Code

Or, you can use a macro to write the currency codes into a new column:

Please Login or Register  to view this content.

Adjust ranges to suit.

Then you can use a pivot table or the usual suspects (Sumif, Sumifs, etc) to aggregate the data.

This macro only needs to run once and you don't have to save the file as a macro-enabled workbook after you've run it, since the values will now be stored in the cells.

Surely it must be easier for you to use a seperate column with data validation lists allowing AUD, EUR, or USD?
As it stands you have to change the formats for each individual entry, that must be tiresome!

See this workbook
1/. Specify the currency for each entry from the drop-downs in Column A

2/. Select the currency to sum from the drop-downs in D2

Marcol, I may be wrong, but I read the question as the data being present with the format in place (obviously created by someone with too much time on their hands) and the predicament is not about formatting the numbers as a currency, but extracting the currency out of the already formatted numbers.

@ npamcpp

Your abolutely right, every cell has been manually formatted, and if changes are needed you have to reformat the cells.
There's no pattern to the formatting!

How awkward and error prone is that?

Combine that with the fact that formulae can't detect formatting, then you'll see that this is a very convoluted way to do a very simple job.

I agree. But maybe the asker is not in a position to change that. You know as well as I do that sometimes people get served with spreadsheets and need to run with what's there. So they come here for help.

Lately, I feel that this forum is declining, though. Members are being stalked and personal details are being published without the member's consent. A certain moderator is on a power trip.

A much safer and much more pleasant site for Office related questions is The Code Cage. Sign up and enjoy. Most ex-moderators of this forum are active there. And happy.

Okay, maybe I missed the point ...

Try this UDF
In a Standard Module

Please Login or Register  to view this content.

Enter in B2, Drag/Fill Down as required.

Formula:

Please Login or Register  to view this content.

In the sheet module, this will format the result in D3

Please Login or Register  to view this content.

Select from the drop-down in D2