sum currency values based on format

[HYSEN]

I am trying to sum values (by currency format) from a column of values.
They are all just typed in values, and the currency symbol is set using Format Cells.
So the currency symbol showing is because the cell has been formatted using Format Cells-Number-Currency.

Is there a way of doing this without VBA?
Thanks in advance

$1,000
£1,000
£1,000
$500
etc..
etc..

So from the above, my output should be
$1,500
£2,000

 

[Steve=True]

You will probably need to create a helper column

In my example, currency data is in a1:a10

Put this in cell b1

=CELL("format",a1)
and copy it down to the last row of data b10

You will notice that USdollars = C2 and All Others = ,2

Then this formula will work

=SUMPRODUCT((a1:a10)*((b1:b10)="C2")) to find USdollars
=SUMPRODUCT((a1:a10)*((b1:b10)=",2")) to find other currencies. Doesn't look like any other currencies have thier own codes.

 

[MikeWx]

Another more obscure solution uses a helper cell with an old Excel XLM function called GET.CELL that Bill Jelen talks about in his "Excel Gurus Gone Wild" book. To use this function, use the Define Name (Excel 2003) or Name Manager (Excel 2007) to define a new range. I called mine CELLFORMAT. In the Refers to block, type =GET.CELL(53,Sheet1!A1). The 53 is the option displays a cell as text, including any symbols associated with formatting. Assuming the amounts in your data start in Cell A1, type =left(CELLFORMAT,1) in Cell B1 and copy down. The result will be either the $ for US dollars or the £ for pounds.

Use =sumif(b1:b4,"$",a1:a4) for the total dollars
Use =sumif(b1:b4,"£",a1:a4) for the total pounds

 

[Steve=True]

Mike, That is a much better solution as mine will only distinguish USdollars vs any other currency.

 

[HYSEN]

Thanks All. I used Mike version as I need to apply this for multiple currencies. Cheers.