excelexcel-formulacountanalytics

How to count text in all cells in a column in Excel


enter image description here

Hello friends, I would need your help as I want to count the text in a cell. Data is based in excel. I have a coulmn, each cell in that column contains multiple categories. And I need to count the existance of those categories in the column. Please see the attached screenshot for sample Data and expected output. Thanks! :)

Please see the attached screenshot for sample Data and expected output. Thanks! :)


Solution

  • Assuming there is no Excel Constraints then you could try this following formula.

    enter image description here


    • Formula used in cell C1

    =LET(
         a,A2:A8,
         b,TOCOL(TEXTBEFORE(
           TEXTAFTER(","&a,",",SEQUENCE(,MAX(LEN(a))))&",",","),3),
         c,UNIQUE(b),
         d,HSTACK(c,MMULT(N(c=TOROW(b)),SEQUENCE(ROWS(b),,,0))),
         VSTACK({"Category","Count"},d))