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!

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, elsewhe

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


"Stav19" wrote:

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!

thanks for that, I will try that!

On May 30, 7:01*pm, Gary''s Student
wrote:
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, elsewhe

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



"Stav19" wrote:
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!- Hide quoted text -

- Show quoted text -