I have a long list of numbers that have five unique digits at the beginning of the number that signifies a specific product. What is the best way to sum up these products as I have indicated below? Your help is greatly appreciated. I'm using Excel 365.

12345678
12345876
01234565
01234575
23456780
23456874
23456235

12345 = 2


01234 = 2
23456 = 3

Thank you,
Karen

It looks like you want to count your products, not to sum up something there!

Something like this must work for you:
for 1st one, etc.
In case you want to do this for all products in single go, you must have a table of products, and a column for product counts there - something like (on fly):

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)

Attached Files

msofficeforums48913.xlsx (10.1 KB, 7 views)