COUNTIF and automatic type conversion problem

Try this instead

=SUMPRODUCT(--($E$1:$E$1000=E2))=1

Note SP doesn't work on whole columns.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"joes" <[email protected]> wrote in message
news:[email protected]...
> Hello
>
> I like to have an unique validation rule for a columen which contains
> id's. I used a therefore simple rule with the build in function
> "COUNTIF"
> http://www.cpearson.com/excel/NoDupEntry.htm
>
> i.e. "=COUNTIF(E:E;E2)=1"
>
> My id's have the format "n.nn" i.e. "1.1", "1.2", "1.3" etc. and the
> cell format is set to "text". The above rule works fine as long as I
> have the following id's
>
> "5.1"
> "5.10"
> "5.100"
>
> The problem which I have now is that EXCEL seems to convert the value
> before the COUNTIF is getting applied. It seems that it converst the
> value into a number and this results not any more into an unique id.
> So any idea's how I can suppress this automatic conversion? To chnage
> the id format is not applciable because it is already used.
>
> Many thanks for any suggestions.
>
> regards
> Mark Egloff
>

Many thanks it works... great.

What I wonder is only why is it working with the "SUMPRODUCT" and not
with the "COUNTIF", does the "SUMPRODUCT" not do any type conversations
or is this beacuse this function does it calculate differently. If
someone could delight me what the calculation steps are above would
help. What means the "--" multiplication with itself?

regards
Mark

Try this instead

=SUMPRODUCT(--($E$1:$E$1000=E2))=1

Note SP doesn't work on whole columns.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"joes" <[email protected]> wrote in message
news:[email protected]...
> Hello
>
> I like to have an unique validation rule for a columen which contains
> id's. I used a therefore simple rule with the build in function
> "COUNTIF"
> http://www.cpearson.com/excel/NoDupEntry.htm
>
> i.e. "=COUNTIF(E:E;E2)=1"
>
> My id's have the format "n.nn" i.e. "1.1", "1.2", "1.3" etc. and the
> cell format is set to "text". The above rule works fine as long as I
> have the following id's
>
> "5.1"
> "5.10"
> "5.100"
>
> The problem which I have now is that EXCEL seems to convert the value
> before the COUNTIF is getting applied. It seems that it converst the
> value into a number and this results not any more into an unique id.
> So any idea's how I can suppress this automatic conversion? To chnage
> the id format is not applciable because it is already used.
>
> Many thanks for any suggestions.
>
> regards
> Mark Egloff
>

Many thanks it works... great.

What I wonder is only why is it working with the "SUMPRODUCT" and not
with the "COUNTIF", does the "SUMPRODUCT" not do any type conversations
or is this beacuse this function does it calculate differently. If
someone could delight me what the calculation steps are above would
help. What means the "--" multiplication with itself?

regards
Mark

Try this instead

=SUMPRODUCT(--($E$1:$E$1000=E2))=1

Note SP doesn't work on whole columns.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"joes" <[email protected]> wrote in message
news:[email protected]...
> Hello
>
> I like to have an unique validation rule for a columen which contains
> id's. I used a therefore simple rule with the build in function
> "COUNTIF"
> http://www.cpearson.com/excel/NoDupEntry.htm
>
> i.e. "=COUNTIF(E:E;E2)=1"
>
> My id's have the format "n.nn" i.e. "1.1", "1.2", "1.3" etc. and the
> cell format is set to "text". The above rule works fine as long as I
> have the following id's
>
> "5.1"
> "5.10"
> "5.100"
>
> The problem which I have now is that EXCEL seems to convert the value
> before the COUNTIF is getting applied. It seems that it converst the
> value into a number and this results not any more into an unique id.
> So any idea's how I can suppress this automatic conversion? To chnage
> the id format is not applciable because it is already used.
>
> Many thanks for any suggestions.
>
> regards
> Mark Egloff
>

Many thanks it works... great.

What I wonder is only why is it working with the "SUMPRODUCT" and not
with the "COUNTIF", does the "SUMPRODUCT" not do any type conversations
or is this beacuse this function does it calculate differently. If
someone could delight me what the calculation steps are above would
help. What means the "--" multiplication with itself?

regards
Mark

Try this instead

=SUMPRODUCT(--($E$1:$E$1000=E2))=1

Note SP doesn't work on whole columns.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"joes" <[email protected]> wrote in message
news:[email protected]...
> Hello
>
> I like to have an unique validation rule for a columen which contains
> id's. I used a therefore simple rule with the build in function
> "COUNTIF"
> http://www.cpearson.com/excel/NoDupEntry.htm
>
> i.e. "=COUNTIF(E:E;E2)=1"
>
> My id's have the format "n.nn" i.e. "1.1", "1.2", "1.3" etc. and the
> cell format is set to "text". The above rule works fine as long as I
> have the following id's
>
> "5.1"
> "5.10"
> "5.100"
>
> The problem which I have now is that EXCEL seems to convert the value
> before the COUNTIF is getting applied. It seems that it converst the
> value into a number and this results not any more into an unique id.
> So any idea's how I can suppress this automatic conversion? To chnage
> the id format is not applciable because it is already used.
>
> Many thanks for any suggestions.
>
> regards
> Mark Egloff
>

Many thanks it works... great.

What I wonder is only why is it working with the "SUMPRODUCT" and not
with the "COUNTIF", does the "SUMPRODUCT" not do any type conversations
or is this beacuse this function does it calculate differently. If
someone could delight me what the calculation steps are above would
help. What means the "--" multiplication with itself?

regards
Mark

Hello

I like to have an unique validation rule for a columen which contains
id's. I used a therefore simple rule with the build in function
"COUNTIF"
http://www.cpearson.com/excel/NoDupEntry.htm

i.e. "=COUNTIF(E:E;E2)=1"

My id's have the format "n.nn" i.e. "1.1", "1.2", "1.3" etc. and the
cell format is set to "text". The above rule works fine as long as I
have the following id's

"5.1"
"5.10"
"5.100"

The problem which I have now is that EXCEL seems to convert the value
before the COUNTIF is getting applied. It seems that it converst the
value into a number and this results not any more into an unique id.
So any idea's how I can suppress this automatic conversion? To chnage
the id format is not applciable because it is already used.

Many thanks for any suggestions.

regards
Mark Egloff

Try this instead

=SUMPRODUCT(--($E$1:$E$1000=E2))=1

Note SP doesn't work on whole columns.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"joes" <[email protected]> wrote in message
news:[email protected]...
> Hello
>
> I like to have an unique validation rule for a columen which contains
> id's. I used a therefore simple rule with the build in function
> "COUNTIF"
> http://www.cpearson.com/excel/NoDupEntry.htm
>
> i.e. "=COUNTIF(E:E;E2)=1"
>
> My id's have the format "n.nn" i.e. "1.1", "1.2", "1.3" etc. and the
> cell format is set to "text". The above rule works fine as long as I
> have the following id's
>
> "5.1"
> "5.10"
> "5.100"
>
> The problem which I have now is that EXCEL seems to convert the value
> before the COUNTIF is getting applied. It seems that it converst the
> value into a number and this results not any more into an unique id.
> So any idea's how I can suppress this automatic conversion? To chnage
> the id format is not applciable because it is already used.
>
> Many thanks for any suggestions.
>
> regards
> Mark Egloff
>

Many thanks it works... great.

What I wonder is only why is it working with the "SUMPRODUCT" and not
with the "COUNTIF", does the "SUMPRODUCT" not do any type conversations
or is this beacuse this function does it calculate differently. If
someone could delight me what the calculation steps are above would
help. What means the "--" multiplication with itself?

regards
Mark