Criteria length limit in SUMIF()

[BAC]

XP Pro/Office 2003 PRO

Is there a limit on the number of characters that can be used in the
criteria section of a SUMIF()/COUNTIF() function?

Columns A & B consists of strings with a length of 30 to 50 characters.

I get incorrect results when I try to SUMIF($A$1:$A$30, "=" & B2,
$C$1:$C$75) (This formula is copied down so B2 becomes B3;B4;B5...). Is this
a SUMIF limit problem or do I need to look elsewhere for the weird results
I'm getting?


TIA
BAC

 

[Dave Peterson]

Could it be that the ranges don't match: 1:30 in column A vs 1:75 in column C?

 

[BAC]

Thanx Dave, but

No, strings in column A may recur multiple times in Col C.

This is "typical" use for SUMIF() i.e. to combine multiple occurences into
1 -> no?

BAC

 

[Ron Coderre]

Is it possible that there are trailing spaces (or other invisbile
characters) in the Col_A values, making them appear to be matches when, in
fact, they are not?
Test by copying the Col_B value into a Col_A cell that you believe should be
a match, but isn't being picked up by the formula.

Does that help?

Regards,

Ron
Microsoft MVP (Excel)

 

[Peo Sjoblom]

It is certainly not typical having a different size of the range you want
have evaluated A1:A30 and the sum range C1:C75, besides it is useless since
you can't get it to sum beyond C30 anyway. Nevertheless I tested with a
criteria string that had 100 characters without any problems

 

[Ron Coderre]

Hi, Dave

I believe the SUMIF function ignores the size of the 2nd argument's range.
Instead, it uses a range that is the same size as the 1st argument's
range...but, begins in the first cell refenced in the 2nd argument. That's
why SUMIF works if you only use a 1-cell reference in the 2nd argument.

Example:
These variations all return the same value
=SUMIF(A1:A10,"DAVE",B1:B10)
=SUMIF(A1:A10,"DAVE",B1:B3)
=SUMIF(A1:A10,"DAVE",B1)

Best Regards,

Ron

 

[Ron Coderre]

Um....all references to the "2nd" argument should be to the "3rd" argument
(more coffee anyone?).

=SUMIF(1st_arg, 2nd_arg, 3rd_arg)

Ron

Hi, Dave

I believe the SUMIF function ignores the size of the 2nd argument's range.
Instead, it uses a range that is the same size as the 1st argument's
range...but, begins in the first cell refenced in the 2nd argument.
That's why SUMIF works if you only use a 1-cell reference in the 2nd
argument.

Example:
These variations all return the same value
=SUMIF(A1:A10,"DAVE",B1:B10)
=SUMIF(A1:A10,"DAVE",B1:B3)
=SUMIF(A1:A10,"DAVE",B1)

Best Regards,

Ron

 

[BAC]

oops->Sorry, you're right..the formula should have been:

SUMIF($A$2:$A$75,"="&B2,$C$2:$C$75) and column B has only 30 rows

Nonetheless, the discussion so far has not answered the original question..

Another individual here has been slowly reducing the size of the test fields
and has found that he finally gets correct results at a length of 15. Fields
of 16 or greater continue to return erroneous results. The fields being
compared are text strings of numbers. that you tested with 100 characters
suggests that my problem is not in the legth of the fields, but somewhere
else.

I'd still like to know if there's a limit tho??

Thanx
BAC

 

[Peo Sjoblom]

I am sure it is the numbers, Excel had 15 digits of precision, any numbers
after that will be truncated

--
Regards,

Peo Sjoblom

oops->Sorry, you're right..the formula should have been:

SUMIF($A$2:$A$75,"="&B2,$C$2:$C$75) and column B has only 30 rows

Nonetheless, the discussion so far has not answered the original
question..

Another individual here has been slowly reducing the size of the test
fields
and has found that he finally gets correct results at a length of 15.
Fields
of 16 or greater continue to return erroneous results. The fields being
compared are text strings of numbers. that you tested with 100 characters
suggests that my problem is not in the legth of the fields, but somewhere
else.

I'd still like to know if there's a limit tho??

Thanx
BAC

 

[Dave Peterson]

I would never even thought to have used this syntax for the 3rd argument (or
even 2nd <bg>).

Um....all references to the "2nd" argument should be to the "3rd" argument
(more coffee anyone?).

=SUMIF(1st_arg, 2nd_arg, 3rd_arg)

Ron

 

[T. Valko]

Ah, what perfect timing!

Here's an example of where this behavior comes into play:

http://tinyurl.com/ypb5km

You'll notice in my formula I use OFFSET. This is so that the mentioned
behavior in SUMIF doesn't "accidentally" include any cells below A1. Using
OFFSET limits the SUMIF(arg1,....arg3) to be the same size.

Biff

 

[Dave Peterson]

You "formula" people give me a headache! <vbg>

 

[T. Valko]

Us "formula" people are in awe of you "VBA" types!

Biff

 

[Dave Peterson]

Ha. It only takes a little bit of perseverance to work with VBA.

You formula people have some sort insight to see the world in a (weird!) way!

Us "formula" people are in awe of you "VBA" types!

Biff

 

[Aladin Akyurek]

Does

=SUMPRODUCT($A$2:$A$75=B2)+0,$C$2:$C$75)

yield results different from those you obtain with:

SUMIF($A$2:$A$75,"="&B2,$C$2:$C$75) ?

oops->Sorry, you're right..the formula should have been:

SUMIF($A$2:$A$75,"="&B2,$C$2:$C$75) and column B has only 30 rows

Nonetheless, the discussion so far has not answered the original question..

Another individual here has been slowly reducing the size of the test fields
and has found that he finally gets correct results at a length of 15. Fields
of 16 or greater continue to return erroneous results. The fields being
compared are text strings of numbers. that you tested with 100 characters
suggests that my problem is not in the legth of the fields, but somewhere
else.

I'd still like to know if there's a limit tho??

Thanx
BAC