Please help!! Countif formula doesnn't work with text format
 

Data is as follows:
A1 099
A2 99
A3 099
A4 99

The cells are all formatted as TEXT

In A5 is a formula =COUNTIF(A1:A4, A1)
The result incorrectly calculates to 4 not 2 which is what I would
rather see.
Has anybody experienced this anomoly and how do I force COUNTIF to
evaluate the "literal text cell values" rather than a "numeric
conversion of the cell values" which appears to be happening?


--
shirley_kee
------------------------------------------------------------------------
shirley_kee's Profile: http://www.excelforum.com/member.php...o&userid=30384
View this thread: http://www.excelforum.com/showthread...hreadid=565899

Please help!! Countif formula doesnn't work with text format
 

Hi!

Has anybody experienced this anomoly

Oh yeah!

Try this instead:

=SUMPRODUCT(--(A1:A4=A1))

Biff

"shirley_kee"
wrote in message
...

Data is as follows:
A1 099
A2 99
A3 099
A4 99

The cells are all formatted as TEXT

In A5 is a formula =COUNTIF(A1:A4, A1)
The result incorrectly calculates to 4 not 2 which is what I would
rather see.
Has anybody experienced this anomoly and how do I force COUNTIF to
evaluate the "literal text cell values" rather than a "numeric
conversion of the cell values" which appears to be happening?


--
shirley_kee
------------------------------------------------------------------------
shirley_kee's Profile:
http://www.excelforum.com/member.php...o&userid=30384
View this thread: http://www.excelforum.com/showthread...hreadid=565899

Please help!! Countif formula doesnn't work with text format
 

The formula you gave me doesn't seem to work.

=SUMPRODUCT(--(A1:A4=A1))


--
shirley_kee
------------------------------------------------------------------------
shirley_kee's Profile: http://www.excelforum.com/member.php...o&userid=30384
View this thread: http://www.excelforum.com/showthread...hreadid=565899

Please help!! Countif formula doesnn't work with text format
 

"shirley_kee" wrote....
The formula you gave me doesn't seem to work.
=SUMPRODUCT(--(A1:A4=A1))

Worked for me. Based on your sample it returns 2.

Biff

Please help!! Countif formula doesnn't work with text format
 

It works now. I changed the range to A:A instead of A1:A4.

Thankx a lot


--
shirley_kee
------------------------------------------------------------------------
shirley_kee's Profile: http://www.excelforum.com/member.php...o&userid=30384
View this thread: http://www.excelforum.com/showthread...hreadid=565899

Please help!! Countif formula doesnn't work with text format
 

"shirley_kee" wrote...
It works now. I changed the range to A:A instead of A1:A4.

Well, now that shouldn't work!!!

Sumproduct can't use entire columns as range references, A:A.

Biff