 |
 | 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 / September 2007Countif cell contians a specific number (within a list)
Gurus, Hope you can help me out once again. You guys/gals ROCK! I have a worksheet that contains survey results and in one column there are the results of a ranking exercise. The survey respondant enters a list of numbers that correspond to the 15 most critical items from a list of 50 items. Each item is designated by a number between 1 - 50. The entry looks like 1,4,6,18,19,23,44,50... On another worksheet I need to summarize how many times each of the 50 items makes it into a top 15 response. In other words I need to be able to count how many times 1 appears in column D (not 10,11,12,21,31,41...ect.). My struggle here is coming up with a way to count if the range contains a specific value, and then how to exclude non-matches like 10,11,12... etc. Any help you can provide would be AWESOME. Thanks, Scott I think if you use Data-->Text to column option and separate on 'comma (,)', your current data will be split across the columns and the you should be easily able to do this. Hope this helps!!  SignaturePranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER!! > Gurus, > [quoted text clipped - 21 lines] > > Scott I don't have that option. > I think if you use Data-->Text to column option and separate on 'comma (,)', > your current data will be split across the columns and the you should be [quoted text clipped - 27 lines] > > > > Scott Would you care to tell us why you don't have that option? SignatureDavid Biddulph >I don't have that option. > [quoted text clipped - 4 lines] >> >> Hope this helps!! >> > Gurus, >> > [quoted text clipped - 27 lines] >> > >> > Scott If the lists of 15 critical items are actually contained in one cell: Example: A1:A6 contains: 1,4,6,18,19,23,44,50 1,4,6,18,19,23,44,50 4,6,18,19,23,44,50 6,18,19,23,44,50 1,4,6,18,19,23,44,50 1,4,6,19,23,44 Then try this: D1:D50 contains the series 1 through 50 This formula returns the count of D1's value in A1:A6 E1: =SUMPRODUCT(--((SEARCH(","&D1&",",","&$A$1:$A$6&","&D1&",")-LEN($A$1:$A$6))<0)) Copy that formula down through E50 Using the example: 1 occurs 4 times 2 occurs 0 times 3 occurs 0 times 4 occurs 5 times etc Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) > Gurus, > [quoted text clipped - 24 lines] > > Scott Ron, This is great, but there is one small gap. The last number in the list isn't being counted. For example ",50" isn't showing up. Can you help please sir? Thanks, Scott > If the lists of 15 critical items are actually contained in one cell: > [quoted text clipped - 57 lines] > > > > Scott Using this fomula in E1 (and copied down through E50): =SUMPRODUCT(--((SEARCH(","&D1&",",","&$A$1:$A$6&","&D1&",")-LEN($A$1:$A$6))<0)) and these values in A1:A6: 1,4,6,18,19,23,44,50 1,4,6,18,19,23,44,50 ,4,6,18,19,23,44,50 6,18,19,23,44,50 1,4,6,18,19,23,44,50 1,4,6,19,23,44 The count returned for 50 (in cell E50) is 5, the correct count. Are you using the same formula? Am I missing somethng? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) > Ron, > [quoted text clipped - 71 lines] >> > >> > Scott Strange... it works for 50, but if I add ",3" to the end of each of those lists it doen't count the 3's. ??? > Using this fomula in E1 (and copied down through E50): > =SUMPRODUCT(--((SEARCH(","&D1&",",","&$A$1:$A$6&","&D1&",")-LEN($A$1:$A$6))<0)) [quoted text clipped - 94 lines] > >> > > >> > Scott I'm still confused....I'm getting a count for 3's when they're appended to the list. In fact if I clear the other strings and only have this in cell A1: 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50 Every formula returns a count of 1. Do you have any spaces interspersed in the string of values? I'm assuming the list is computer generated, so I'm not testing for stray spaces or other characters. Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) > Strange... it works for 50, but if I add ",3" to the end of each of those > lists it doen't count the 3's. [quoted text clipped - 105 lines] >> >> > >> >> > Scott If you move the 3 to the end of the list like this: 1,2,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,3 If I put another comma after the 3 it counts. > I'm still confused....I'm getting a count for 3's when they're appended to > the list. [quoted text clipped - 124 lines] > >> >> > > >> >> > Scott Something's still wrong....Wait a minute!....I was in the middle of a long post, when it occurred to me.... If the match is the last item in the list...AND it's a single digit number...AND the matched position is EQUAL to the length of the string... the match_position less: the length equals zero! The end of my formula should end with <=0 (instead of: <0 ) Now it works with this: E1: =SUMPRODUCT(--((SEARCH(","&D1&",",","&$A$1:$A$6&","&D1&",")-LEN($A$1:$A$6))<=0)) That was a challenge....thanks for catching the error. -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) > If you move the 3 to the end of the list like this: > 1,2,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,3 [quoted text clipped - 138 lines] >> >> >> > >> >> >> > Scott YOU ROCK DUDE! Thanks for the kind words....and thanks for helping me root out the problem! Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) > YOU ROCK DUDE! One way (assuming only one entry per cell): With the desired value in A1, and the list in Data!D:D: =COUNTIF(DATA!D:D,A1&",*") + COUNTIF(DATA!D:D,"*," & A1 & ",*") + COUNTIF(DATA!D:D, "*," & A1) + COUNTIF(DATA!D:D,A1) > Gurus, > [quoted text clipped - 21 lines] > > Scott That's clever SignatureRegards, Peo Sjoblom > One way (assuming only one entry per cell): > [quoted text clipped - 32 lines] >> >> Scott AWESOME! Thanks to all who responded. I'm not sure I understand what you want but have you looked in the help index for COUNTIF SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Gurus, > [quoted text clipped - 24 lines] > > Scott |
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
|
©2008 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.