Sum up the digits within a range of cells

[freemaniam]

I am looking for a formula to sum up all the digit(s) within a range of cells, e.g.

Please Login or Register  to view this content.

The formula will return 47 (8+2+1+1+5+8+3+9+1+4+1+4).

I tried MID function but it is not working properly as the value within the cell could be either single digit or double digits.

Many thanks in advance.

[martindwilson]

=--left(a1)+if(mid(a1,2,1)="",0,--mid(a1,2,1))
ah i notice they are not in a1,a2,a3......,bother
back in a mo! no thats ok just drag across
better still
=IF(ISBLANK(a1),0,--LEFT(a1)+IF(MID(a1,2,1)="",0,--MID(a1,2,1)))

[Richard Schollar]

Hi

If they are only max 2 digit numbers then perhaps:

=SUMPRODUCT(SUM(MID(TEXT($A$1:$H$1,"00"),{1;2},1)+0))

Confirmed with Ctrl+Shift+Enter

Richard

[JBeaucaire]

Well, I can't figure out how to do it in a ROW like you suggested, but if the digits were in a column, this formula would do it:

Please Login or Register  to view this content.

This is an array formula, so you confirm it by pressing CTRL-SHIFT-ENTER, and your sample total should return 47. If you just press ENTER, it will return 3, which is wrong. Confirm the array for 47.

Adjust your column range to fit, blank cells are ignored.

[daddylonglegs]

=SUMPRODUCT(SUM(MID(TEXT($A$1:$H$1,"00"),{1;2},1)+0))

Should be able to use

=SUMPRODUCT(MID(A1:H1&"00",{1;2},1)+0)

Is this total, 47 or whatever, your end result or are you doing further calculations?

[Ron Coderre]

Another suggestion

Please Login or Register  to view this content.

[freemaniam]

Should be able to use

=SUMPRODUCT(MID(A1:H1&"00",{1;2},1)+0)

Is this total, 47 or whatever, your end result or are you doing further calculations?

Thanks all! Appreciate your help very much. Actually I will do further calculation.

I am working on a check digit verification formula. Your solutions here solved a big part of the task. Many thanks again.

I noticed some of the thread titles began with "[Solved]", is it one of the protocols here? That the thread starter should put "[Solved]" if they get the answer?

[NBVC]

I noticed some of the thread titles began with "[Solved]", is it one of the protocols here? That the thread starter should put "[Solved]" if they get the answer?

Yes.. that is the protocol

Go to your first post in this thread and click Edit

Click Go Advanced and select [Solved] from the Title dropdown

Click Save Changes.

[fifthsunset]

I know this thread is years old, but I just want to say thank you so much for this. You've saved me literally days of manual counting. Thank you!