Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2579470%22%20slang%3D%22en-US%22%3EFormula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2579470%22%20slang%3D%22en-US%22%3EI%20have%20two%20no%20in%20one%20cell%20and%20i%20want%20addition%20of%20that%20two%20no%20in%20another%20cell%20which%20formula%20I%20have%20to%20use%20please%20guide%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2579470%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2579667%22%20slang%3D%22en-US%22%3EBetreff%3A%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2579667%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1110991%22%20target%3D%22_blank%22%3E%40Shailesh1982%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EYou%20can%20find%20precise%20details%20on%20how%20to%20quickly%20and%20precisely%20come%20up%20with%20a%20proposed%20solution%20in%20the%20link%20below%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fwelcome-to-your-excel-discussion-space%2Fm-p%2F2204395%22%20target%3D%22_self%22%3EWelcome%20to%20your%20Excel%20discussion%20space!%3C%2FA%3E%3C%2FP%3E%3CP%3EPlease%20include%20the%20following%20info%20to%20help%20others%20answer%20your%20question%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWish%20you%20a%20nice%20day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2580143%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2580143%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1110991%22%20target%3D%22_blank%22%3E%40Shailesh1982%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIs%20that%20like%20this%3F%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20159px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F298053i8FC20C041E85E582%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2581641%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2581641%22%20slang%3D%22en-US%22%3EYes%3C%2FLINGO-BODY%3E
New Contributor
I have two no in one cell and i want addition of that two no in another cell which formula I have to use please guide
8 Replies

@Shailesh1982 

 

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)

 

@Shailesh1982 

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!

@Shailesh1982 

Is that like this?

image.png

Yes

@Shailesh1982 Inspired by the contributions of @Peter Bartholomew and @Sergei Baklan , try this in case Peter's LET formula doesn't work for you:

Screenshot 2021-07-24 at 06.43.56.png

@Shailesh1982 

=SUM(FILTERXML("<y><z>"&SUBSTITUTE(A1,",","</z><z>")&"</z></y>","//z"))

@Shailesh1982 

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)

@Shailesh1982 

As a comment, formula suggested by @Detlef Lewin is more flexible and could count any reasonable number of numbers within cell.

image.png

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)))
)