I am wondering if anyone has ever encountered a limitation on the number of
characters that are joined (concatenated) in a sumif calculation. For
example, my concatenation has a total of 20 characters (10 digit GL account
and 10 digit cost center). When doing my sumif, I am pointing to the range
that contains those concatenated cells and comparing it to the criteria
established (again a concatenation of 20 characters) to sum the amount for
all of those cells that meet that criteria. What I am getting is the same
value repetitively, because it looks like Excel is cutting off the last digit
of the GL account (not good!). We are using Excel 2007. Any help would be
greatly appreciated.
--
TD1965

TaraD wrote:
I am wondering if anyone has ever encountered a limitation on the number of
characters that are joined (concatenated) in a sumif calculation. For
example, my concatenation has a total of 20 characters (10 digit GL account
and 10 digit cost center). When doing my sumif, I am pointing to the range
that contains those concatenated cells and comparing it to the criteria
established (again a concatenation of 20 characters) to sum the amount for
all of those cells that meet that criteria. What I am getting is the same
value repetitively, because it looks like Excel is cutting off the last digit
of the GL account (not good!). We are using Excel 2007. Any help would be
greatly appreciated.

Show an example of two lines of data that are being summarized but shouldn't be,
and also your current formulas.

a concatenation of 20 characters
it looks like Excel is cutting off the last digit
of the GL account (not good!)

Both SUMIF and COUNTIF will evaluate numeric numbers and text numbers as
being equal.

If you have a concatenated string of digits that is 20 characters long then
what's happening is SUMIF is only evaluating the first 15 digits of the
string. For example:

12345678901234500001...10
12345678901234500002...10
12345678901234500003...10
12345678901234500004...10
12345678901234500005...10

=SUMIF(A1:A5,A1,B1:B5)

The correct result should be 10 but the formula returns 50.

Concatenation coerces the string of digits to be a TEXT string (even though
it may look like a number).Since SUMIF evaluates text numbers and numeric
numbers as being equal, Excel "thinks" you entered a number as the criteria
but Excel only recognizes numbers to 15 digits so the formula is evaluating
the criteria as 123456789012345.

Solution:

Use SUMPRODUCT instead of SUMIF.

=SUMPRODUCT(--(A1:A5=A1),B1:B5)

--
Biff
Microsoft Excel MVP


"TaraD" wrote in message
...
I am wondering if anyone has ever encountered a limitation on the number of
characters that are joined (concatenated) in a sumif calculation. For
example, my concatenation has a total of 20 characters (10 digit GL
account
and 10 digit cost center). When doing my sumif, I am pointing to the
range
that contains those concatenated cells and comparing it to the criteria
established (again a concatenation of 20 characters) to sum the amount for
all of those cells that meet that criteria. What I am getting is the same
value repetitively, because it looks like Excel is cutting off the last
digit
of the GL account (not good!). We are using Excel 2007. Any help would
be
greatly appreciated.
--
TD1965

Clarafication:

so the formula is evaluating the criteria as 123456789012345.

Should be:

....so the formula is evaluating the criteria and the criteria range as
123456789012345.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
a concatenation of 20 characters
it looks like Excel is cutting off the last digit
of the GL account (not good!)

Both SUMIF and COUNTIF will evaluate numeric numbers and text numbers as
being equal.

If you have a concatenated string of digits that is 20 characters long
then what's happening is SUMIF is only evaluating the first 15 digits of
the string. For example:

12345678901234500001...10
12345678901234500002...10
12345678901234500003...10
12345678901234500004...10
12345678901234500005...10

=SUMIF(A1:A5,A1,B1:B5)

The correct result should be 10 but the formula returns 50.

Concatenation coerces the string of digits to be a TEXT string (even
though it may look like a number).Since SUMIF evaluates text numbers and
numeric numbers as being equal, Excel "thinks" you entered a number as the
criteria but Excel only recognizes numbers to 15 digits so the formula is
evaluating the criteria as 123456789012345.

Solution:

Use SUMPRODUCT instead of SUMIF.

=SUMPRODUCT(--(A1:A5=A1),B1:B5)

--
Biff
Microsoft Excel MVP


"TaraD" wrote in message
...
I am wondering if anyone has ever encountered a limitation on the number
of
characters that are joined (concatenated) in a sumif calculation. For
example, my concatenation has a total of 20 characters (10 digit GL
account
and 10 digit cost center). When doing my sumif, I am pointing to the
range
that contains those concatenated cells and comparing it to the criteria
established (again a concatenation of 20 characters) to sum the amount
for
all of those cells that meet that criteria. What I am getting is the
same
value repetitively, because it looks like Excel is cutting off the last
digit
of the GL account (not good!). We are using Excel 2007. Any help would
be
greatly appreciated.
--
TD1965