|
Criteria length limit in SUMIF()
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 |
Answer: Criteria length limit in SUMIF()
There is no specific limit on the number of characters that can be used in the criteria section of a SUMIF()/COUNTIF() function. However, there may be other factors causing the incorrect results you are experiencing.
One possibility is that there may be leading or trailing spaces in the strings in column A or B, which can cause the criteria to not match exactly. You can try using the TRIM() function to remove any extra spaces before or after the strings. Another possibility is that the strings in column A or B may contain special characters or formatting that is not visible, but is affecting the matching criteria. You can try copying the strings to a new column and using the CLEAN() function to remove any non-printable characters. If neither of these solutions work, it may be helpful to provide more information about the specific results you are getting and the data in your worksheet.
|
Criteria length limit in SUMIF()
Could it be that the ranges don't match: 1:30 in column A vs 1:75 in column C?
BAC wrote: 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 |
Criteria length limit in SUMIF()
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 "Dave Peterson" wrote: Could it be that the ranges don't match: 1:30 in column A vs 1:75 in column C? BAC wrote: 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 |
Criteria length limit in SUMIF()
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) "BAC" wrote in message ... 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 |
Criteria length limit in SUMIF()
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 -- Regards, Peo Sjoblom "BAC" wrote in message ... 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 "Dave Peterson" wrote: Could it be that the ranges don't match: 1:30 in column A vs 1:75 in column C? BAC wrote: 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 |
Criteria length limit in SUMIF()
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 "Dave Peterson" wrote in message ... Could it be that the ranges don't match: 1:30 in column A vs 1:75 in column C? BAC wrote: 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 |
Criteria length limit in SUMIF()
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" wrote in message ... 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 "Dave Peterson" wrote in message ... Could it be that the ranges don't match: 1:30 in column A vs 1:75 in column C? BAC wrote: 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 |
Criteria length limit in SUMIF()
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" wrote: 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 -- Regards, Peo Sjoblom "BAC" wrote in message ... 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 "Dave Peterson" wrote: Could it be that the ranges don't match: 1:30 in column A vs 1:75 in column C? BAC wrote: 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 |
Criteria length limit in SUMIF()
I am sure it is the numbers, Excel had 15 digits of precision, any numbers
after that will be truncated -- Regards, Peo Sjoblom "BAC" wrote in message ... 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" wrote: 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 -- Regards, Peo Sjoblom "BAC" wrote in message ... 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 "Dave Peterson" wrote: Could it be that the ranges don't match: 1:30 in column A vs 1:75 in column C? BAC wrote: 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 |
Criteria length limit in SUMIF()
I would never even thought to have used this syntax for the 3rd argument (or
even 2nd <bg). Ron Coderre wrote: 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" wrote in message ... 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 "Dave Peterson" wrote in message ... Could it be that the ranges don't match: 1:30 in column A vs 1:75 in column C? BAC wrote: 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 -- Dave Peterson |
Criteria length limit in SUMIF()
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" wrote in message ... I would never even thought to have used this syntax for the 3rd argument (or even 2nd <bg). Ron Coderre wrote: 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" wrote in message ... 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 "Dave Peterson" wrote in message ... Could it be that the ranges don't match: 1:30 in column A vs 1:75 in column C? BAC wrote: 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 -- Dave Peterson |
Criteria length limit in SUMIF()
You "formula" people give me a headache! <vbg
"T. Valko" wrote: 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" wrote in message ... I would never even thought to have used this syntax for the 3rd argument (or even 2nd <bg). Ron Coderre wrote: 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" wrote in message ... 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 "Dave Peterson" wrote in message ... Could it be that the ranges don't match: 1:30 in column A vs 1:75 in column C? BAC wrote: 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 -- Dave Peterson -- Dave Peterson |
Criteria length limit in SUMIF()
Us "formula" people are in awe of you "VBA" types!
Biff "Dave Peterson" wrote in message ... You "formula" people give me a headache! <vbg "T. Valko" wrote: 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" wrote in message ... I would never even thought to have used this syntax for the 3rd argument (or even 2nd <bg). Ron Coderre wrote: 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" wrote in message ... 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 "Dave Peterson" wrote in message ... Could it be that the ranges don't match: 1:30 in column A vs 1:75 in column C? BAC wrote: 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 -- Dave Peterson -- Dave Peterson |
Criteria length limit in SUMIF()
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" wrote: Us "formula" people are in awe of you "VBA" types! Biff "Dave Peterson" wrote in message ... You "formula" people give me a headache! <vbg "T. Valko" wrote: 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" wrote in message ... I would never even thought to have used this syntax for the 3rd argument (or even 2nd <bg). Ron Coderre wrote: 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" wrote in message ... 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 "Dave Peterson" wrote in message ... Could it be that the ranges don't match: 1:30 in column A vs 1:75 in column C? BAC wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Criteria length limit in SUMIF()
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) ? BAC wrote: 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" wrote: 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 -- Regards, Peo Sjoblom "BAC" wrote in message ... 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 "Dave Peterson" wrote: Could it be that the ranges don't match: 1:30 in column A vs 1:75 in column C? BAC wrote: 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 |
I realise this is a very old post, but to answer the original question:
The criteria bit in a countif/sumif formula can be max 255 characters. Anything above will result in a #VALUE. (Excel 2010 and I`m guessing 2007 also) |
| All times are GMT +1. The time now is 01:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com