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))