May I know any ways can provide the required result? I tried using pivot table, but found some wordings cannot be showed.

DATA

RESULT

CodePudding user response：

If you have Excel-365 then you may try below formulas.

G2=SORT(UNIQUE(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,A2:E6)&"</s></t>","//s")))

H2=COUNTIF(A2:E6,G2#)

CodePudding user response：

Try This:

Insert a Pivot table and select the option "Add this data to the Data Model"

and then from "Value Field Settings" for the column in the Values area select "Distinct Count"

CodePudding user response：

To get unique list you can use:

=LET(data,$A$2:$E$10000, 
     rows, A1:INDEX(A:A,COUNTA(data),0),
     SORT(
      UNIQUE(
        INDEX(data,
              1 INT((ROW(rows)-1)/COLUMNS(data)),
              MOD(ROW(rows)-1 COLUMNS(data),
              COLUMNS(data)) 1)
       )
     )
   )

and to get count:

=COUNTIF(A2:E10000,H2#)