This problem comes from Matt. He has a list of items that he needs to count, but each cell contains a comma-separated list. This means that for each record (row) there could be zero, one or more items listed.
The formula uses the FIND() function to determine if the text is one of the words in the list. If it’s not, FIND() will return the #VALUE! error. The ISERROR() function converts the #VALUE! errors to TRUE and the non-errors to FALSE. Then the NOT function reverses those TRUEs and FALSEs because we want to count the existence of the word, not the errors. The minus minus (–) converts the boolean values to numbers (TRUE=1 and FALSE=0). Finally, all the 1’s and 0’s are summed up. The breakdown of the formula in E2 is
=SUM(--NOT(ISERROR({1;#VALUE!;10;#VALUE!})))
=SUM(--NOT({FALSE;TRUE;FALSE;TRUE}))
=SUM(--{TRUE;FALSE;TRUE;FALSE})
=SUM({1;0;1;0})
It’s an array formula, so be sure to enter with Control+Shift+Enter. There are some limitations to this formula. For instance if you had the two hobbies “Fishing” and “Fly Fishing” then the Fishing number would be wrong because it would count both of those entries. If you have unique entries, meaning one isn’t contained in another, then it seems to work pretty well.
Hi Dick
some comments :-)
1. Your formula could be shortened to:
=SUM(ISNUMBER(FIND(D2,$B$2:$B$5))
(also as array formula)
2. Instead of the array formula you may also use the non-array Sumproduct alternative:
=SUMPRODUCT(ISNUMBER(FIND(D2,$B$2:$B$5))
3. But in this specific case I think COUNTIF would do as well:
=COUNTIF($B$2:$B$5,”*”&&D2&”*”)
Frank
All
Fabulous stuff! Should mention that FIND function is case sensitive, COUNTIF is not. Thanks for all the good formula concepts!!
Brett
I am the Matt above. Let me just say THANK YOU! It is people like Dick and all those who posted that makes the internet a great place. I had spend hours wracking my brain trying to figure that out. Kudos to you Excel gurus!
Thanks again!
Hi all,
funny, I had exactly the same problem yesterday and came up with a quite similar solution. I should just have waited somewhat longer and have a look here… It was more fun, doing it myself though ;-)
My formula:
=SUM(IF(ISNUMBER(FIND(D2,$B$2:$B$5));1;0)
Interestingly enough, neither Dick’s nor Frank’s first formula do work for me… ??? (Didn’t try Frank’s other two so far.)
What I did not try to do so far is generating the unique list of “hobbies” from the comma-seperated lists themselves. Any good ideas?
Tom
Tom
This is a brute force method. Copy the comma-separated lists into a new workbook. Save as a tab delimited. Close and tell Excel that you know it’s not in excel format and that’s just fine. Re-open file. The tab delimited format should prompt Excel’s text import wizard. Tell it the file is delimited, not fixed width. Tell the wizard there is no text qualifier and the delimiter is a comma. You should get the lists broken into cells, the first and last elements with the double quote inserted by Excel when the file was saved as tab delimited. Use Excel’s find and replace function to clear those out. Repeat find and replace for spaces (” “). In both cases replace with nothing. Cut all values into same column with header in row 1, like “values,” and sort. Now populate a helper column next to it with a formula comparing cell values. If sorted values are in column A, say A2:A30000, formula in B2 is “=A2<>A1.” This will evaluate “True” on the first instance of each value and false for the duplicates, because it’s a sorted list. Copy down to end of values in A. Copy column B and past special, in place, values. Sort on B, you want all the “True” values. In A should be the list of unique values. You may have to re-insert spaces into any multiple-word entry. Not fancy, but faster and more reliable than searching with the eyeballs.
Tom
That formula in B2 is =A1?does not equal”A2. The less than and greater than symbols didn’t show up on my first comment.
Brett
Rather than looking for a specific item in multiple lists, what if you need to count the number of elements in a comma-separated list?
For example, say the list is, “food, water, rocks, fish, oranges”. I’d like a formula that returns “5?.
Any ideas?
Just count the number of commas:
=LEN(A2) – LEN(SUBSTITUTE(A2, “,”, “”)) + 1
Hello Everyone,
I need to pick each value in Column B1 seperated by a comma from a cell and combine it with value in column A1 and write it into a seperate row.
A1 B1
149,46,1025,15348
20049
I need the above text as formatted below
149
146
11025
15348
20040
this was great!
Can’t you just use this:
{=COUNT(FIND(D2,$B$2:$B$5))}
I think this works just as well without all of the changing this into that stuff…
Find it and count it…keep it simple
Is it possible to extract a words that contains only comma from the exel??
Count commas in string (Excel 2002)
=LEN(C5)-LEN(SUBSTITUTE(C5;”,”;””))+1
Help! I have been looking at Q&A, and Excel Web Pages for over four hours and this page seems as close as I am going to get to someone that maybe able to help me. I have down loaded many ten character CVS strings (0000000001)(000000010)etc. into a single Excel column and want to count,vertically,the number of “1? in each character positions.Each string can have from 1 to 9 “1? in the string. I am not interested in an “Array” because I plan to add more strings on the fly.
I have a similar problem, to the initial posting by Matt, where I’m trying to track and chart numerous variables.
Case # Def Name Charges Victim Name Contact
1016-CR04123Doe, John MII, ACA Wilson, AndrewWilson, Sandy
816-CR05349Brown, Brandon MII, ACA, Atmpt Rbry 1 Smith, Devin Boro, Jason
816-CR05350Wilson, Claude Invol Mnsltr Scott, Mark Scott, David
I have my Excel spreadsheet set up recording information such as listed above. I’ve used a drop-down list for selecting and adding the criminal charges. The problem comes in when I attempt to list the total of each charge. For instance, A separate worksheet should list: MII 2
ACA 2
Atmpt Rbry 1 1
Invol Mnsltr 1
From there, I’d like to be able to input these numbers into a bar, pie, or other type of chart.
Thanks!!
Hi all,
I have a somewhat similar issue. I am using a COUNTIF functions to count the cells that contain a value greater than a certain number. However, the number is sometimes listed as “#,” and the formula will not count the number when it is followed by a comma. I have attempted to use a wild card to specify a cell that just contains any number greater than 240, but I don’t think that will work/there are too many issues for it to work. Is there any solution to this problem?
Thanks!
=SUM(--(VALUE(SUBSTITUTE(IF(ISBLANK(B1:B10),0,B1:B10),",",""))>240))
Enter with Ctrl+Shift+Enter. The ISBLANK converts blank cells to zero so the VALUE function will work. Then SUBSTITUTE strips any commas. Then VALUE converts them into real numbers that are compared to 240.
I’m only curious to know if there’s a formula to SUM comma separated string of items and their totals located in the same cell? Pull the item and SUM the total. (I saw someone post this elsewhere with no answer – got me curious). E.G.
A1 reads Apples 5, Oranges 2, Pears 4
A2 reads Pears 3, Apples 3, Oranges 3
SUM each item in new Column/Rows as
Apples 8
Oranges 5
Pears 7
Aside from extracting items and totals separately then Sum all – I can’t see a formula for this. Just curious …. thanks