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