Forum Discussion

GAJett's avatar
GAJett
Copper Contributor
Aug 21, 2022
Solved

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 nam...
excel
Formulas and Functions
office 365
  • Harun24HR's avatar
    Harun24HR
    Aug 22, 2022

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

     

ttdur3120's avatar
ttdur3120
Copper Contributor
May 04, 2023

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  APFXV
 RefDesc1Desc2RefDesc1Desc2RefDesc1Desc2   
AmberAP,V,R:FXWhen applied VdiscountdiscountVcomp fxdiscount   
SapphireV,FX,R:AP Limit to applyR:AP,APwhen applied R:AP,APreturns    
JadeFXno limit FX,V sizedFX,V visited   
RubyVdiscountdiscountAP,FX,R:FXnon appwhen appliedAP,FX,R:FX     
Harun24HR's avatar
Harun24HR
Bronze Contributor
May 04, 2023

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.

 

 

  • pancitpalabok's avatar
    pancitpalabok
    Copper Contributor
    Sep 16, 2023

    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

Resources