+ Reply to Thread
Results 1 to 10 of 10

Using COUNTIF for values in a table delineated by commas(",") i.e "1,2,3,4"

  1. #1
    Registered User
    Join Date
    01-28-2016
    Location
    Toronto
    MS-Off Ver
    2002
    Posts
    3

    Using COUNTIF for values in a table delineated by commas(",") i.e "1,2,3,4"

    I've been trying to use the COUNTIF function to help me with a column such as:
    1,2,3
    2,3,4
    43,53,24
    1,4,5
    and for some reason, I can't get the correct values when I use that function.

    Primarily, I want to add up, how many rows have the value "1", how many have the value "2" and so forth. Thanks.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,965

    Re: Using COUNTIF for values in a table delineated by commas(",") i.e "1,2,3,4"

    Do you mean number 2 or digits 2 i.e number of 2s in 2,12,23 =3 ?

  3. #3
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,691

    Re: Using COUNTIF for values in a table delineated by commas(",") i.e "1,2,3,4"

    I think you probably want to count the numbers not digits.
    Check attached.
    Attached Files Attached Files
    Happy to Help

    How to upload excel workbooks at this forum - http://www.excelforum.com/the-water-...his-forum.html

    "I don't get things easily, so please be precise and elaborate"

    If someone's post has helped you, thank by clicking on "Add Reputation" below the post.
    If your query is resolved please mark the thread as "Solved" from the "Thread Tools" above.

    Sourabh

  4. #4
    Registered User
    Join Date
    01-28-2016
    Location
    Toronto
    MS-Off Ver
    2002
    Posts
    3

    Re: Using COUNTIF for values in a table delineated by commas(",") i.e "1,2,3,4"

    Thanks for the reply however it did not work on my data set.
    For EG:
    1,2
    3,4
    4,5
    4,6
    4,7
    8
    4,9
    4,10
    11
    12
    4,13
    14
    11,15
    16
    1,17,18
    19
    18,20
    32,1,4
    23,5,1

    If I was to look for the occurrence of the number "1" in that set, I wanted a function to return:
    4
    since there are 4 rows that count the number "1"

    Thanks!

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,965

    Re: Using COUNTIF for values in a table delineated by commas(",") i.e "1,2,3,4"

    The COUNTIF function previously supplied works O.K. provided you use the helper column.

    An alternative ....

    Using the helper column from the previous posting:

    data in A2 onwards

    Enter this column B2 and copy down

    =","&A2&","

    in C2 search value e.g 1

    in D2

    =SUMPRODUCT(--ISNUMBER(SEARCH("*,"&C2&",*",$B$2:$B$20)))
    Last edited by JohnTopley; 01-28-2016 at 05:10 PM.

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Using COUNTIF for values in a table delineated by commas(",") i.e "1,2,3,4"

    Quote Originally Posted by sourabhg98 View Post
    Check attached.
    ----------
    https://www.excelforum.com/showthread.php?t=1040120
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  7. #7
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,691

    Re: Using COUNTIF for values in a table delineated by commas(",") i.e "1,2,3,4"

    Quote Originally Posted by moexcelnew View Post
    Thanks for the reply however it did not work on my data set.
    Why?? It is very well working.
    I guess you forgot to extend the range of the formula according to your data.
    Check attached.
    The result is "4" in this case as you desired.
    Attached Files Attached Files

  8. #8
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,654

    Re: Using COUNTIF for values in a table delineated by commas(",") i.e "1,2,3,4"

    Assuming data is in A2:A20

    C2 is given value

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    01-28-2016
    Location
    Toronto
    MS-Off Ver
    2002
    Posts
    3

    Re: Using COUNTIF for values in a table delineated by commas(",") i.e "1,2,3,4"

    Thank you everyone!! it worked!!

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,965

    Re: Using COUNTIF for values in a table delineated by commas(",") i.e "1,2,3,4"

    Can you please mark thread as SOLVED ("Thread Tools" at top of first post). Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 35
    Last Post: 01-13-2016, 02:16 AM
  2. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  3. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  4. Replies: 2
    Last Post: 06-06-2013, 12:45 PM
  5. Replies: 0
    Last Post: 01-09-2013, 06:52 PM
  6. [SOLVED] How to Count number of "Error" and "OK" after the word "Instrument" found in table row
    By eltonlaw in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-17-2012, 06:26 AM
  7. Replies: 3
    Last Post: 02-16-2011, 02:55 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1