Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-25-2022, 07:19 PM
Karen615 Karen615 is offline Sum Unique Digits Windows 7 64bit Sum Unique Digits Office 2010 64bit
Competent Performer
Sum Unique Digits
 
Join Date: Jun 2011
Location: Chicago
Posts: 112
Karen615 is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 04-25-2022, 09:57 PM
ArviLaanemets ArviLaanemets is offline Sum Unique Digits Windows 8 Sum Unique Digits Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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*")
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):
Code:
=COUNTIFS(YourSheet!$A$2:$A$100, $A2 & "*")
Reply With Quote
  #3  
Old 04-26-2022, 05:18 AM
p45cal's Avatar
p45cal p45cal is offline Sum Unique Digits Windows 10 Sum Unique Digits Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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
File Type: xlsx msofficeforums48913.xlsx (10.1 KB, 5 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sum Unique Digits 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
Sum Unique Digits Need help calculating frequency of digits laucn Excel 2 06-08-2015 07:50 AM
Sum Unique Digits Problems merging in last 4 digits of an account higher than 16 digits Glynda Mail Merge 1 04-08-2011 12:17 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:02 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft