Excel Countif function for comma separated data in cells

[GAJett]

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

[#2 · Accepted solution]

@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 &"*")

[#3]

@Harun24HR 

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

[#4]

@Harun24HR 

=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

[#5]

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

[#6]

@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