Sum Total Value of a Single Cell

[JulianvO]

Greetings

Cell - A1 To contain formula

Cell - A2 To Display result of value entered into Cell A3

Cell - A3 Cell in which a numeric value is entered.

Formula must be-able to the sum total of the value entered into A3 and display the result in A2.

Example of Value entered into A3 1024 (No commas. spaces or plus signs. Just the number)

Sum total to be displayed in A2 must be 7 (1+0+2+4 = 7)

Example of Value entered into A3 123456789 (No commas. spaces or plus signs. Just the number)

Sum total to be displayed in A2 must be 45 (1+2+3+4+5+6+7+8+9=45)

Thanks

 

[Fluff]

Try confirming the formula with Ctrl Shift Enter, rather than just Enter.

 

[KRice]

Ahhh…good point!

 

[JulianvO]

Book1
	A	B
1	=SUM(0+MID(A3,ROW($A$1:INDEX($A:$A,LEN(A3))),1))
2
3	1024
4
5
6
7
8
9
10
11
12
13
14
Sheet1

As requested.

Thanks for the patience.

 

[KRice]

I don't see any issues, but have you tried the suggestion from @Fluff? Click on the formula in the formula bar, hit F2 for edit mode, then reconfirm the formula by hitting Control-Shift-Enter at the same time. That re-enters the formula as an array formula and you should see curly brackets surround the formula in the formula bar. Since you have an earlier version of Excel, most array formulas need to be entered with Ctrl-Shift-Enter.

 

[JulianvO]

Greetings KRice

Thank you for explaining the steps. I do not know Excel very well.

I have followed the steps. However, vell A3 has a blue border, A3,ROW is highlighted in the formula, with the following tip underneath :MID(text; start_num; num_chars)

A pop-up msgbox appears stating that Formula Wizard under the Formula tab must be used - this function is not available.

Clicking on the fx to the left of the formula shows the Function Arguments dialog. Mid - Text (formula inserted here) = Invalid

I have tried entering a number in Start_num, this just adds the number to the end of the formula within the existing parenthesis.

To be honest I am completely stumped.

Maybe a module would work?

Many thanks for the patience

 

[FormR]

Hi, it looks like your regional settings use a semi-colon as the list separator - in which case you can try this for my suggestion (post#4) which is non volatile, doesn't require committing with CTRL+SHIFT+ENTER for older versions of Excel and if your entering "real" numbers in A3 then 15 digits is as much as Excel will handle before losing precision.

=SUM(MID(A3&REPT(0;15);{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15};1)+0)

Or this twist on the post#3, that can also be entered normally on older versions of Excel and is robust against row insertions.

=SUMPRODUCT(0+MID(A3;ROW(INDEX($A:$A,1):INDEX($A:$A;LEN(A3)));1))

 

[JulianvO]

Greetings FormR

Thanks a lot. The SUMPRODUCT formula works great.

Thanks to all who assisted.

 

[KRice]

You're welcome...we're happy to help. Good eyes @FormR...to see the list separator. I had to look again before I noticed the issue was likely due to regional settings. One thing that is apparently resolved based on the response from @JulianvO, but I think one more comma needs to be converted to a semicolon in the first INDEX function:

=SUMPRODUCT(0+MID(A3;ROW(INDEX($A:$A;1):INDEX($A:$A;LEN(A3)));1))

 

[FormR]

Good spot