SOLVED

# Excel Countif function for comma separated data in cells

Copper Contributor

# Excel Countif function for comma separated data in cells

I have a string of names, separated by commas, in cells within a column.  Each string may be different but a specific name may appear in multiple cells.  How can I use Countif() to count specific names.  If not is there another function I could use?

Note that this is not a .csv file, but a comma separated string within cells of an .xlsx file.

Note also I am using the on-line version of Excel in Office 365 and know may be significant limitations to available functions.  Downloading the file and using Excel on my own desktop is not an option.

Thanks everyone,

GAJett

6 Replies
best response confirmed by GAJett (Copper Contributor)
Solution

# Re: Excel Countif function for comma separated data in cells

@GAJett Some samples help visitors to understand question correctly. I assume you can use COUNTIFS() with wild card, something like.

``=COUNTIFS(A:A,"*harun24hr*")``

Replace harun24hr with your desired name.  If you want to cell reference then use-

`=COUNTIFS(A1:A8,"*" & B1 &"*")`

# Re: Excel Countif function for comma separated data in cells

Thanks Harun24HR,
This is actually more flexible, and more simple, than I was expecting! It will pull any substring out of any longer string. Commas not needed except for my own human readability.

Should be perfect for my needs.
Thanks again,
GAJett

# Re: Excel Countif function for comma separated data in cells

 =COUNTIFS(A:A,"*harun24hr*")

In your example for counting text values in a comma delimited string using wildcards, is there a way to prevent miscounts due to wildcards.  For example,  I want to count the occurrences of  "AP" across multiple cells in row 4   =COUNTIFS(H4:S4,"*AP*")

This works fine until I have a cell which contains  "When applied do this...."   I don't see any way to be specific.   My data looks something like this...    Regardless of date, I want to count by row how many AP, V, FX, R:FX, R:AP  are on each row.  So, in Amber AP column I should see  1, FX column I should see 1,  V column I should see 3;   For Sapphire AP column 3, FX column 1, V column 1

Any ideas?  I've tried SUMPRODUCT, FIND, etc....   THANKS!

 25-Apr 26-Apr 27-Apr AP FX V Ref Desc1 Desc2 Ref Desc1 Desc2 Ref Desc1 Desc2 Amber AP,V,R:FX When applied V discount discount V comp fx discount Sapphire V,FX,R:AP Limit to apply R:AP,AP when applied R:AP,AP returns Jade FX no limit FX,V sized FX,V visited Ruby V discount discount AP,FX,R:FX non app when applied AP,FX,R:FX

# Re: Excel Countif function for comma separated data in cells

@ttdur3120 Then you could try-

``=SUM(--(TEXTSPLIT(TEXTJOIN(",",TRUE,\$B3:\$J3),,{",",":"})=K\$1))``

Download the attached excel sample file. Please note: TEXTSPLIT() is only available to Microsoft-365.

# Re: Excel Countif function for comma separated data in cells

@Harun24HR Hi, I have a large data of colums and when I do the formula it shows #calc!. What do I do if I have to countif a large comma seperated data? Please help

# Re: Excel Countif function for comma separated data in cells

Hi , can you please me here ,

I want to get  the count +1 for each match out of the numbers from E TO P.

The target list is in Sheet1-

so for 1st row, it should give 7

Thanks in Advance .

Ankur1721

1 best response

Accepted Solutions
best response confirmed by GAJett (Copper Contributor)
Solution

# Re: Excel Countif function for comma separated data in cells

@GAJett Some samples help visitors to understand question correctly. I assume you can use COUNTIFS() with wild card, something like.

``=COUNTIFS(A:A,"*harun24hr*")``

Replace harun24hr with your desired name.  If you want to cell reference then use-

`=COUNTIFS(A1:A8,"*" & B1 &"*")`