# Counting digits within a row of numbers

#### hotrod

##### Board Regular
Formula require please to be able to add all the numbers in a row.
eg 2, 8, 27, 30, 42, 48, 61 = 47 (how do I get the 47).

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Try

=SUMPRODUCT(--(LEN(1:1)))

=SUMPRODUCT(--(LEN(1:1))) - gives me 12, which is 4+7 (2 countings).
I only need 1 counting, the 47.
Any other suggestion/s?

Try this UDF:-

Public Function Numbers_In_Range(Data_Range As Range) As Long
Dim r1 As Long
Dim l1 As Long
Dim rth_Member As String
r1 = 0
Numbers_In_Range = 0
For r1 = 1 To Data_Range.Columns.Count
rth_Member = CStr(Data_Range(1, r1))

For l1 = 1 To Len(rth_Member)
Numbers_In_Range = Numbers_In_Range + Mid(rth_Member, l1, 1)
Next l1
Next r1
End Function

It needs to be inserted in a Module

and can be called by Numbers_in_Range(A1:A10)

- assuming your digits are in the range A1:10. Thanks

Kaps

hotrod

How you get 47?

And since when did 4+7= 12?

I always thought that the answer to that would be 11.

2 + 8 + (2+7) + (3+ 0) + (4+2)+ (4+8) + (6+1)

=47

(and Norie is right 4+7 = 11)

Kaps

Hi

For ex., for numbers up to 10 digits, in I1:

=SUMPRODUCT(MOD(INT(A1:G1/10^(ROW(INDIRECT("1:10"))-1)),10))

Copy down

pgc - exactly what I needed.
xld - something I did not think of and using in my analysis.
Kap - your suggestions will be added to my reference library for future reference.
Thank you all for your help, greatly appreciated.

Replies
2
Views
172
Replies
15
Views
274
Replies
4
Views
386
Replies
3
Views
235
Replies
1
Views
496

1,221,525
Messages
6,160,328
Members
451,637
Latest member
hvp2262

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back