Forum Discussion

GAJett's avatar
GAJett
Copper Contributor
Aug 21, 2022

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

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

     

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    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 &"*")

     

    • GAJett's avatar
      GAJett
      Copper Contributor
      Aug 22, 2022

      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

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

         

         

    • Ankur1721's avatar
      Ankur1721
      Copper Contributor
      May 14, 2024

      Harun24HR 

       

      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

       

       

Resources