Sum Pound sterling (out of Pounds and Euros)

[Pastafarian]

Hi all,

Haven't found anything like this posted before, so I had to post it.

Im looking for a way to sum the Britisch pounds sterling from a column which consists out of Pounds and Euro's in Excel (not VBA).

Example:
£ 3050,00
€ 630,54
£ 2300,00
£ 4500,50
€ 219,00
€ 7853,86
€ 234,63
€ 421,11

Total Pound sterling: £9850,50

Any help would be appreciated

 

[Andrew Poulsom]

In column B put 1 against the GBP numbers and leave the cells next to the Euro numbers blank. Then try:

=SUMPRODUCT(A1:A8,B1:B8)

 

[Pastafarian]

Thanks for your reply.

The aim is to get it to work automatically, because it is part of a 'invoice-tool'.
Therefore im looking for a way to automatically 'detect' the cells with GBP and sum them.
If there is no way to get it to work in Excel, I'd like to know how to do it in VBA.

 

[VoG]

With a UDF in a standard module

Function SumPound(r As Range) As Double
Dim c As Range
For Each c In r
    If c.NumberFormat Like "*£*" Then SumPound = SumPound + c.Value
Next c
End Function

Excel Workbook
	A	B
1	3,050.00	9,850.50
2	630.54
3	2,300.00
4	4,500.50
5	219.00
6	7,853.86
7	234.63
8	421.11
Sheet6

[/B]http://www.excel-jeanie-html.de/index.php?f=1

 

[Pastafarian]

That did it!

Very easy to use, thanks a lot!