#1
|
|||
|
|||
Sum Unique Digits
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 |
#2
|
|||
|
|||
It looks like you want to count your products, not to sum up something there!
Something like this must work for you: Code:
=COUNTIFS($A$2:$A$100, "12345*") 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): Code:
=COUNTIFS(YourSheet!$A$2:$A$100, $A2 & "*") |
#3
|
||||
|
||||
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) |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Find last three digits after . ( including zero) | LearnerExcel | Excel | 4 | 02-08-2018 10:49 AM |
Entering a number of 19 digits | Jo Freeman | Excel | 11 | 11-08-2017 06:20 AM |
Having number's digits together | mohsen.amiri | Word | 0 | 06-23-2017 01:20 AM |
Need help calculating frequency of digits | laucn | Excel | 2 | 06-08-2015 07:50 AM |
Problems merging in last 4 digits of an account higher than 16 digits | Glynda | Mail Merge | 1 | 04-08-2011 12:17 AM |