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

1. 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. 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. 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.

4. 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. 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)))

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

Originally Posted by sourabhg98
Check attached.
----------

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

Originally Posted by moexcelnew
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.

8. 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. Re: Using COUNTIF for values in a table delineated by commas(",") i.e "1,2,3,4"

Thank you everyone!! it worked!!

10. 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.

Users Browsing this Thread

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

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