 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / April 2007Criteria 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 Could it be that the ranges don't match: 1:30 in column A vs 1:75 in column C? > XP Pro/Office 2003 PRO > [quoted text clipped - 10 lines] > TIA > BAC  SignatureDave Peterson 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 > Could it be that the ranges don't match: 1:30 in column A vs 1:75 in column C? > [quoted text clipped - 12 lines] > > TIA > > BAC 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 SignatureRegards, Peo Sjoblom > Thanx Dave, but > [quoted text clipped - 25 lines] >> > TIA >> > 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 > 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 [quoted text clipped - 30 lines] > >> > TIA > >> > BAC I am sure it is the numbers, Excel had 15 digits of precision, any numbers after that will be truncated SignatureRegards, Peo Sjoblom > oops->Sorry, you're right..the formula should have been: > [quoted text clipped - 54 lines] >> >> > TIA >> >> > BAC 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: > [quoted text clipped - 48 lines] >>>>> TIA >>>>> BAC 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 > Could it be that the ranges don't match: 1:30 in column A vs 1:75 in > column C? [quoted text clipped - 15 lines] >> TIA >> BAC 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 > [quoted text clipped - 33 lines] >>> TIA >>> BAC 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?). [quoted text clipped - 44 lines] > >> > >> Dave Peterson SignatureDave Peterson 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 >I would never even thought to have used this syntax for the 3rd argument >(or [quoted text clipped - 52 lines] >> >> >> >> Dave Peterson You "formula" people give me a headache! <vbg> > Ah, what perfect timing! > [quoted text clipped - 68 lines] > > > > Dave Peterson SignatureDave Peterson 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) > XP Pro/Office 2003 PRO > [quoted text clipped - 11 lines] > TIA > BAC |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.