Try the following, this doesn't needs LAMBDA() helper functions,
• Formula used in cell C2
=LET(
a,MID(UNIQUE(A2:A8),3,3),
b,UNIQUE(a),
c,TOROW(a),
SORT(HSTACK(b,MMULT(N(b=c),SEQUENCE(COLUMNS(c),,,0)))))
如果您需要标头,
=LET(
a,MID(UNIQUE(A2:A8),3,3),
b,UNIQUE(a),
c,TOROW(a),
VSTACK({"Type of Ref.","Count"},
SORT(HSTACK(b,MMULT(N(b=c),SEQUENCE(COLUMNS(c),,,0))))))