Jul 23 2021 05:41 AM
Jul 23 2021 06:39 AM
You can find precise details on how to quickly and precisely come up with a proposed solution in the link below
Welcome to your Excel discussion space!
Please include the following info to help others answer your question
Thank you for your understanding and patience
Wish you a nice day.
Nikolino
I know I don't know anything (Socrates)
Jul 23 2021 07:04 AM
IF you are using Excel 365 and IF your cells/range is called 'input' and IF each cell comprises two numbers separated by a comma, then
= LET(sep, ",",
p, FIND(sep, input),
first, MID(input, 1, p-1),
second, MID(input, p+1, 15),
first+second )
otherwise you need something different!
Jul 23 2021 09:47 PM - edited Jul 23 2021 09:48 PM
@Shailesh1982 Inspired by the contributions of @Peter Bartholomew and @Sergei Baklan , try this in case Peter's LET formula doesn't work for you:
Jul 23 2021 09:58 PM
=SUM(FILTERXML("<y><z>"&SUBSTITUTE(A1,",","</z><z>")&"</z></y>","//z"))
Jul 24 2021 02:36 AM
Here is the simplest formula for the checksu.
For multi-digit numbers you have to expand the formula accordingly.
Example 1: "=MID(A1,1,1)+MID(A1,2,1)+MID(A1,3,1)", use without goosefoot.
Example 2: "=SUMPRODUCT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1) ", use without goosefoot.
Example 3: "{=SUM(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))}" , use without goosefoot.
*3 Array formulas must always be confirmed with the key combination "CTRL + SHIFT + ENTER"
The file with the examples is included.
I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
Jul 24 2021 04:27 AM
As a comment, formula suggested by @Detlef Lewin is more flexible and could count any reasonable number of numbers within cell.
To expand @Peter Bartholomew formula as variant
=LET(input, A1, sep, ",",
seps, LEN(input)-LEN(SUBSTITUTE(input,sep,"")),
k, SEQUENCE(seps+1),
start, IFERROR( FIND("|",SUBSTITUTE(input, sep,"|",k-1))+1,1),
end, IFERROR( FIND("|",SUBSTITUTE(input, sep,"|",k)),LEN(input)+1),
SUM(--TRIM(MID(input, start, end-start)))
)