Criteria length limit in SUMIF()

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Could it be that the ranges don't match: 1:30 in column A vs 1:75 in column C?
 
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
 
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)
 
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
 
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
 
Um....all references to the "2nd" argument should be to the "3rd" argument
(more coffee anyone?).

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

Ron


Ron Coderre said:
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
Click to expand...
 
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
 
I am sure it is the numbers, Excel had 15 digits of precision, any numbers
after that will be truncated

--
Regards,

Peo Sjoblom


BAC said:
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
Click to expand...
 
I would never even thought to have used this syntax for the 3rd argument (or
even 2nd <bg>).

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

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

Ron
Click to expand...
 
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
 
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!



T. Valko said:
Us "formula" people are in awe of you "VBA" types!

Biff
Click to expand...
 
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
Click to expand...
 
Back
Top