Forum Discussion
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 &"*")
=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 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.
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
