Limitations on Sumif Calculation

[TaraD]

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.

 

[Glenn]

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.

 

[T. Valko]

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)

 

[T. Valko]

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.