You can make a difference in the Apple Support Community!
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
In Excel there is a function called Sum numbers in a cell that allows you to add up all the individual numbers within a cell.
In A1 I have a date that I want to be added up like 1+9+7+6 = 23
At the moment the only way I can do this is with single numbers per cell like Row 3, but this is a very clumsy way to work.
MacBook Pro 13″, macOS 13.0
If they're all 4-digit "dates" then you can do something like this:
=MID(A1,1,1)+MID(A1,2,1)+MID(A1,3,1)+MID(A1,4,1)
Use ; instead of , in the formula if your region uses , as a decimal separator.
SG
If they're all 4-digit "dates" then you can do something like this:
=MID(A1,1,1)+MID(A1,2,1)+MID(A1,3,1)+MID(A1,4,1)
Use ; instead of , in the formula if your region uses , as a decimal separator.
SG
Here is a way that will let you sum them regardless of the number of digits, except there are none. You can put IFERROR around it with the answer of 0 to catch that possibility.
=COUNTIF(REGEX.EXTRACT(A1,"\d",0),"1")+2×COUNTIF(REGEX.EXTRACT(A1,"\d",0),"2")+3×COUNTIF(REGEX.EXTRACT(A1,"\d",0),"3")+4×COUNTIF(REGEX.EXTRACT(A1,"\d",0),"4")+5×COUNTIF(REGEX.EXTRACT(A1,"\d",0),"5")+6×COUNTIF(REGEX.EXTRACT(A1,"\d",0),"6")+7×COUNTIF(REGEX.EXTRACT(A1,"\d",0),"7")+8×COUNTIF(REGEX.EXTRACT(A1,"\d",0),"8")+9×COUNTIF(REGEX.EXTRACT(A1,"\d",0),"9")
If you don't see the entire formula above, click and hold on the "=" and drag down until the entire thing (even the stuff outside of the window) is eelected. Then copy/past to your table.
REGEX.EXTRACT output is text. I wish we had a SUM function that would sum text "numbers" but we don't. That would have made it a lot easier and shorter.
Thanks SG,
That's amazing!
What if I don't know how many characters there will be? So in the example it was just a year, but what if I have a longer number and I'm not sure how many numbers there are? Is there a way to automate this process?
I haven't found a neat way to automate it but if you think you may have, say, up to 7 digits, then you can do something like this:
=MID(A1,1,1)+MID(A1,2,1)+MID(A1,3,1)+MID(A1,4,1)+IF(MID(A1,5,1)="",0,MID(A1,5,1))+IF(MID(A1,6,1)="",0,MID(A1,6,1))+IF(MID(A1,7,1)="",0,MID(A1,7,1))
Repeated here in case the forum mangled it:
=MID(A1,1,1)+MID(A1,2,1)+MID(A1,3,1)+MID(A1,4,1)+IF(MID(A1,5,1)="",0,MID(A1,5,1))+IF(MID(A1,6,1)="",0,MID(A1,6,1))+IF(MID(A1,7,1)="",0,MID(A1,7,1))
Just extend that if you think you might have even more digits. It will still work with fewer-digit numbers too.
SG
Or:
=COUNTMATCHES(A2,1)×1+COUNTMATCHES(A2,2)×2+COUNTMATCHES(A2,3)×3+COUNTMATCHES(A2,4)×4+COUNTMATCHES(A2,5)×5+COUNTMATCHES(A2,6)×6+COUNTMATCHES(A2,7)×7+COUNTMATCHES(A2,8)×8+COUNTMATCHES(A2,9)×9
SG
Even better.
Wow! Amazing! Thank you!
Sum numbers in a cell