sum if based on cell forrmatting...is it possible and how to

[yetti]

£ - $ - C$ -



Hi All Guru's out there I do not know if this is a silly question or not but
here goes

I want to set up a spreadsheet proforma whereby i can total all the amounts
(they are results of other formulae) that end up in cells that are formatted
as currencies as laid out in example above having used.
format cells- accounting-
symbol $ English (United State)
symbol C$ Spanish (Nicaragua)
symbol £

is it possible to do a sum if on the cell values based purely on the cell
symbol format

an example would be most appreciated

Regards to all

Yetti

 

[Guest]

Hi Yetti:

Its very easy using VBA. Let's say we have:
1.00 € 1.00 € 1.00 € $1.00
1.00 € 1.00 € 1.00 € $1.00
1.00 € 1.00 € 1.00 € $1.00
1.00 € 1.00 € 1.00 € $1.00
1.00 € 1.00 € 1.00 € $1.00
$1.00 $1.00 $1.00 $1.00
in A1 thru D6 (mixed dollars and euros. First enter this small macro:

Function zum(r As Range, s As String) As Long
Dim rr As Range
zum = 0
For Each rr In r
If rr.NumberFormat = s Then
zum = zum + rr.Value
End If
Next

Then, elsewhere in the worksheet enter:
=zum(A16,"$#,##0.00") to yield 9
or
=zum(A16,"#,##0.00 [$€-1]") to yield 15

You can easily adapt the format string to any currency and the range to
cover any range of cells

 

[yetti]

Gary's Student,
Thank you for that sub routine.......VBA seems amazing and very advanced.
Just what I needed your help is very much appreciated
Regards
Yetti

Hi Yetti:

Its very easy using VBA. Let's say we have:
1.00 ? 1.00 ? 1.00 ? $1.00
1.00 ? 1.00 ? 1.00 ? $1.00
1.00 ? 1.00 ? 1.00 ? $1.00
1.00 ? 1.00 ? 1.00 ? $1.00
1.00 ? 1.00 ? 1.00 ? $1.00
$1.00 $1.00 $1.00 $1.00
in A1 thru D6 (mixed dollars and euros. First enter this small macro:

Function zum(r As Range, s As String) As Long
Dim rr As Range
zum = 0
For Each rr In r
If rr.NumberFormat = s Then
zum = zum + rr.Value
End If
Next

Then, elsewhere in the worksheet enter:
=zum(A16,"$#,##0.00") to yield 9
or
=zum(A16,"#,##0.00 [$?-1]") to yield 15

You can easily adapt the format string to any currency and the range to
cover any range of cells
--
Gary's Student

£ - $ - C$ -



Hi All Guru's out there I do not know if this is a silly question or not
but
here goes

I want to set up a spreadsheet proforma whereby i can total all the
amounts
(they are results of other formulae) that end up in cells that are
formatted
as currencies as laid out in example above having used.
format cells- accounting-
symbol $ English (United State)
symbol C$ Spanish (Nicaragua)
symbol £

is it possible to do a sum if on the cell values based purely on the
cell
symbol format

an example would be most appreciated

Regards to all

Yetti