+ 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. 01-28-2016, 12:55 PM #1
    moexcelnew
    moexcelnew is offline
    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.
    Reply With QuoteRegister To Reply
  2. 01-28-2016, 01:36 PM #2
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,148

    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 ?
    Reply With QuoteRegister To Reply
  3. 01-28-2016, 01:52 PM #3
    sourabhg98
    sourabhg98 is offline
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    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
    Reply With QuoteRegister To Reply
  4. 01-28-2016, 03:36 PM #4
    moexcelnew
    moexcelnew is offline
    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!
    Reply With QuoteRegister To Reply
  5. 01-28-2016, 04:03 PM #5
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,148

    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.
    Reply With QuoteRegister To Reply
  6. 01-28-2016, 08:46 PM #6
    Tony Valko
    Tony Valko is offline
    Forum Expert 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.
    Reply With QuoteRegister To Reply
  7. 01-28-2016, 11:46 PM #7
    sourabhg98
    sourabhg98 is offline
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    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
    Reply With QuoteRegister To Reply
  8. 01-29-2016, 01:40 AM #8
    bebo021999
    bebo021999 is online now
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,459

    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.
    Quang PT
    Reply With QuoteRegister To Reply
  9. 01-30-2016, 02:54 PM #9
    moexcelnew
    moexcelnew is offline
    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!!
    Reply With QuoteRegister To Reply
  10. 01-30-2016, 03:07 PM #10
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,148

    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 With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. "Countif" with "left" error, "Sumproduct" picking up not qualifying rows
    By drgkt in forum Excel General
    Replies: 35
    Last Post: 01-13-2016, 02:16 AM
  2. Don't know the Forumlas with problem either using "vlookup" "if" "and" or "index" "match"
    By Jokeman in forum Excel Formulas & Functions
    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. Pivot Table - How to "add a data column" "of the same field" "across multiple sheets"
    By virsilens in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 06-06-2013, 12:45 PM
  5. Seperating "colour" and "size" values into seperate columns from the "product name"
    By sy_lavery in forum Excel General
    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. Excel 2007 : Match Values and Return "Present" "Absent" "Per Diem" or remain blank
    By DanBraden in forum Excel General
    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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1