countif / sumif function error

If the criterion for one of these functions is a string of 15+ of numerical
characters only - formatted as text, it will pick up as a match any string of
numerical characters only - formatted as text in the same length as the
criteria and the first 15 characters match the first 15 characters of the
criteria, even though the numerical characters past the 15’th character does
not match to the criteria, (the reason is probably because these functions
are considering a value string – formatted as text as a numerical value, and
since excel does not keep the actual #’s after 15 numerical values it just
keeps the 0’s so these functions ignore the values after the 15 character
because they assume them to be 0’s only, they just check the length to be the
same after the 15’th character.)

Example:

A1: '1234567890123456
A2: '1234567890123457
B1: =Countif(A1:A2,A1) the result is 2




----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...heet.functions

Try this

=SUMPRODUCT(--(" "&A1:A2=" "&A1))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Mark J." <Mark J.@discussions.microsoft.com> wrote in message
news:CADBB9C3-12E0-4814-9E3E-081FAE34074A@microsoft.com...
> If the criterion for one of these functions is a string of 15+ of
numerical
> characters only - formatted as text, it will pick up as a match any string
of
> numerical characters only - formatted as text in the same length as the
> criteria and the first 15 characters match the first 15 characters of the
> criteria, even though the numerical characters past the 15'th character
does
> not match to the criteria, (the reason is probably because these functions
> are considering a value string - formatted as text as a numerical value,
and
> since excel does not keep the actual #'s after 15 numerical values it just
> keeps the 0's so these functions ignore the values after the 15 character
> because they assume them to be 0's only, they just check the length to be
the
> same after the 15'th character.)
>
> Example:
>
> A1: '1234567890123456
> A2: '1234567890123457
> B1: =Countif(A1:A2,A1) the result is 2
>
>
>
>
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the
> suggestions with the most votes. To vote for this suggestion, click the "I
> Agree" button in the message pane. If you do not see the button, follow
this
> link to open the suggestion in the Microsoft Web-based Newsreader and then
> click "I Agree" in the message pane.
>
>
http://www.microsoft.com/office/comm...heet.functions

Thanks.
I like your creativity.
By the way it seems that the “ “ (space) in the formula is not necessary
since most of the functions (except for the countif/sumif) do treat a value
formatted as text as text.
Also the same would work with =SUM(--(A1:A2=A1)) if entered as an array.
A simple work around would be to add another column and attach a dummy
character to the end of each cell then the countif/sumif functions would
treat it as text.

My comment was regarding the countif/sumif functions, I don’t mind if it
treats short values formatted as text as a number (which is sometimes handy),
but if it’s more then 15 characters and formatted as text then it should not
ignore the rest.


"Bob Phillips" wrote:

> Try this
>
> =SUMPRODUCT(--(" "&A1:A2=" "&A1))
>
> --
> HTH
>
> Bob Phillips
>
> (remove xxx from email address if mailing direct)
>
> "Mark J." <Mark J.@discussions.microsoft.com> wrote in message
> news:CADBB9C3-12E0-4814-9E3E-081FAE34074A@microsoft.com...
> > If the criterion for one of these functions is a string of 15+ of
> numerical
> > characters only - formatted as text, it will pick up as a match any string
> of
> > numerical characters only - formatted as text in the same length as the
> > criteria and the first 15 characters match the first 15 characters of the
> > criteria, even though the numerical characters past the 15'th character
> does
> > not match to the criteria, (the reason is probably because these functions
> > are considering a value string - formatted as text as a numerical value,
> and
> > since excel does not keep the actual #'s after 15 numerical values it just
> > keeps the 0's so these functions ignore the values after the 15 character
> > because they assume them to be 0's only, they just check the length to be
> the
> > same after the 15'th character.)
> >
> > Example:
> >
> > A1: '1234567890123456
> > A2: '1234567890123457
> > B1: =Countif(A1:A2,A1) the result is 2
> >
> >
> >
> >
> > ----------------
> > This post is a suggestion for Microsoft, and Microsoft responds to the
> > suggestions with the most votes. To vote for this suggestion, click the "I
> > Agree" button in the message pane. If you do not see the button, follow
> this
> > link to open the suggestion in the Microsoft Web-based Newsreader and then
> > click "I Agree" in the message pane.
> >
> >
> http://www.microsoft.com/office/comm...heet.functions
>
>
>

My whole rationale in the response was getting around the fact that COUNTIF
and SUMIF treats short values formatted as text as a number, due to the
number precision, as that is a problem, bug, whatever you want to call it.

I do agree though that the space is redundant, by using a direct comparison
of =, the text attribute does not seem to get overridden. But why use an
array formula, when you can use a non-array

=SUMPRODUCT(--(A1:A2=A1))



--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Mark J." <MarkJ@discussions.microsoft.com> wrote in message
news:4628CA11-49ED-41E1-BECF-88687A455F91@microsoft.com...
> Thanks.
> I like your creativity.
> By the way it seems that the " " (space) in the formula is not necessary
> since most of the functions (except for the countif/sumif) do treat a
value
> formatted as text as text.
> Also the same would work with =SUM(--(A1:A2=A1)) if entered as an array.
> A simple work around would be to add another column and attach a dummy
> character to the end of each cell then the countif/sumif functions would
> treat it as text.
>
> My comment was regarding the countif/sumif functions, I don't mind if it
> treats short values formatted as text as a number (which is sometimes
handy),
> but if it's more then 15 characters and formatted as text then it should
not
> ignore the rest.
>
>
> "Bob Phillips" wrote:
>
> > Try this
> >
> > =SUMPRODUCT(--(" "&A1:A2=" "&A1))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove xxx from email address if mailing direct)
> >
> > "Mark J." <Mark J.@discussions.microsoft.com> wrote in message
> > news:CADBB9C3-12E0-4814-9E3E-081FAE34074A@microsoft.com...
> > > If the criterion for one of these functions is a string of 15+ of
> > numerical
> > > characters only - formatted as text, it will pick up as a match any
string
> > of
> > > numerical characters only - formatted as text in the same length as
the
> > > criteria and the first 15 characters match the first 15 characters of
the
> > > criteria, even though the numerical characters past the 15'th
character
> > does
> > > not match to the criteria, (the reason is probably because these
functions
> > > are considering a value string - formatted as text as a numerical
value,
> > and
> > > since excel does not keep the actual #'s after 15 numerical values it
just
> > > keeps the 0's so these functions ignore the values after the 15
character
> > > because they assume them to be 0's only, they just check the length to
be
> > the
> > > same after the 15'th character.)
> > >
> > > Example:
> > >
> > > A1: '1234567890123456
> > > A2: '1234567890123457
> > > B1: =Countif(A1:A2,A1) the result is 2
> > >
> > >
> > >
> > >
> > > ----------------
> > > This post is a suggestion for Microsoft, and Microsoft responds to the
> > > suggestions with the most votes. To vote for this suggestion, click
the "I
> > > Agree" button in the message pane. If you do not see the button,
follow
> > this
> > > link to open the suggestion in the Microsoft Web-based Newsreader and
then
> > > click "I Agree" in the message pane.
> > >
> > >
> >
http://www.microsoft.com/office/comm...heet.functions
> >
> >
> >