Counting Comma Separated string in cells in a Column

Here is how I would approach this (without more information):

add additional columns to your table (the ones titled "WP01", "WP02", etc). enter the names as shown

The first row is a header row.

Also... add one extra row at the end and make it a footer row

D2=(LEN($C2)−LEN(SUBSTITUTE($C2, D$1, "")))÷LEN(D$1)

this is shorthand for... select cell D2, then type e(or copy and paste from here) the formula:

=(LEN($C2)−LEN(SUBSTITUTE($C2, D$1, "")))÷LEN(D$1)

select cell D2, copy

select cells D2 the the end of column R, paste

in the footer row (row 15 of this example) iff the following formula:

D15=sum(D)

select cell D15, copy

select cells D15 thru R15, paste

I could never locate 22 occurrences of WP05 (even when I search your post on this web page):

Hi Pasel,

Here is one approach. Insert another table ('FIND start position') and use the FIND function to find the starting position of a WorkPack in each row of the Data table.

Wrap this in IFERROR to return zero if a WorkPack does not exist in that row of the Data table.

Formula in B2 (and Fill Right and Fill Down)

=IFERROR(FIND(B$1,Data::$C2),0)

The table on the right ('FIND start position') has a Header Row and a Footer Row.

Formula in Footer Cell B16 (and Fill Right)

=COUNTIF(B,">0")

Regards,

Ian.

Also,

you could, for convenience, add a second header row and perform the sums at the very top like this:

Here is mine.

This has some limitation, but it is simple and should work for this case.

The key is the use of wildcard character for "COUNTIF" function.

For example, "*WP05*" will match for all cells containing "WP05" as its part.

So, my formula will be

= COUNTIF( search-area, "*" & search-string & "*" )

Hi To_Mi,

Good reply! 🙂

Regards,

Ian.

Nice,

I never tried the wild card because it is not mentioned in the help. I wonder if the wild card is available in most of the countx and sums functions?

Hi Wayne,

Yes, it is mentioned.

http://help.apple.com/functions/mac/5.0/#/ffa5b9f72f

"The wildcards you can use are ? (question mark), * (asterisk), and ~ (tilde). To find more information about wildcards, open the Help menu in your app and enter “wildcard” in the search field."

According to the help "Functions that accept conditions and wildcards as arguments", wildcards can be used for following functions. ( checked with Numbers for Mac ver. 3.6.2 )

• AVERAGEIF
• AVERAGEIFS
• COUNTIF
• COUNTIFS
• SUMIF
• SUMIFS
• SEARCH

From search on "wild"....

SG

Hi Perry,

Question: Is it possible to add a condition using this wildcard approach - For example, could it be adapted to count all "FR Test" that are applicable to "WP05"?

Counting cells that matches multiple conditions can be done with COUNTIFS function.

http://help.apple.com/functions/mac/5.0/#/ffa5a400fc

For the mentioned case, the formula will be :

= COUNTIFS( document-group-column, "FR Test", workpack-column, "*WP05*" )

note: For the column B of the table below, the formula is the same as my previous post.

Much cleaner than Excel!

I believe the same formula should work on Excel too. ( not tested as I don't have Excel )

https://support.office.com/en-us/article/COUNTIF-function-e0de10c6-f885-4e71-abb 4-1f464816df34

https://support.office.com/en-us/article/COUNTIFS-function-dda3dc6e-f74e-4aee-88 bc-aa8c2a866842

Hi Wayne,

Sorry, I forgot to list the functions below. They can also use wildcard if exact much is specified with the optional argument.

• HLOOKUP
• MATCH
• VLOOKUP

Link to the page in "Formulas and Functions Help"

http://help.apple.com/functions/mac/5.0/#/ffac131919b - Functions that accept conditions and wildcards as arguments

Note:

FIND is shown in the SG's search result, but the page says.

The search is case sensitive and spaces are counted. Wildcards are not allowed. To use wildcards or to ignore case in your search, use the SEARCH function.