Summing based on currency format of Cell

[Stav19]

Hi All

A fairly simple request i think, but I just can't seem to figure it
out, is it possible to sum a cell based on the format of it. I have a
sheet with AUD, EUR, JPY and SGD as the currency formats, and I want
to be able to sum by each currency.

The currency isn't actually written in the cell as it's just the
format, so i cannot use "SUMIF", one solution is to insert an extra
column and put the currency in each row and then use "SUMIF", but I
wondered if there was an easier way?

thanks for anyhelp you can offer!

 

[Gary''s Student]

First enter this tiny UDF:

Function txet(r As Range) As String
txet = r.Text
End Function

This returns the visible cell as a text string.

In A1 thru A10 we have:

1
2
3
4
$5.00
6
7
$8.00
9
10

In B1, enter:
=txet(A1) and copy down to see:

1 1
2 2
3 3
4 4
$5.00 $
6 6
7 7
$8.00 $
9 9
10 1

In column B the $'s are REAL. Finally, elsewhere:

=SUMPRODUCT(A1:A10,--(B1:B10="$")) which displays 13

 

[Stav19]

thanks for that, I will try that!

First enter this tiny UDF:

Function txet(r As Range) As String
txet = r.Text
End Function

This returns the visible cell as a text string.

In A1 thru A10 we have:

1
2
3
4
$5.00
6
7
$8.00
9
10

In B1, enter:
=txet(A1) and copy down to see:

1       1
2       2
3       3
4       4
$5.00   $
6       6
7       7
$8.00   $
9       9
10      1

In column B the $'s are REAL.  Finally, elsewhere:

=SUMPRODUCT(A1:A10,--(B1:B10="$")) which displays 13
--
Gary''s Student - gsnu2007i








- Show quoted text -