Since you have O365, in the attached I've put your list in cell A1:A7. Several offerings:
1. In cell C1
=UNIQUE(LEFT(A1:A7,5))
and in cell D1:
=COUNTIF(A1:A7,C1# & "*")
2. In cell F1:
=LET(myList,A1:A7,CHOOSE({1,2},UNIQUE(LEFT(myList, 5)),COUNTIF(myList,UNIQUE(LEFT(myList,5)) & "*")))
3. A LAMBDA version:
=LAMBDA(myList,CHOOSE({1,2},UNIQUE(LEFT(myList,5)) ,COUNTIF(myList,UNIQUE(LEFT(myList,5))&"*")))(A1:A 7)
4. Using 3 above created a named formula ProductCounts in Name Manager which leaves us the formula in cell L1:
=ProductCounts(A1:A7)
|