# Count 1 & sum in number series

#### skr971

##### New Member
Hi All,

i need a formula for count 1 in number series like :

 Column A 1121 Ans . 8 21 11 1431

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

i need a formula which is sum 1 in given number series

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### Michael M

##### Well-known Member
Try

Excel 2010
AB
1Column A
211213
3211
4112
514312
Sheet1
Cell Formulas
RangeFormula
B2=LEN(A2)-LEN(SUBSTITUTE(A2,1,""))
B3=LEN(A3)-LEN(SUBSTITUTE(A3,1,""))
B4=LEN(A4)-LEN(SUBSTITUTE(A4,1,""))
B5=LEN(A5)-LEN(SUBSTITUTE(A5,1,""))

#### Teeroy

##### Well-known Member
Try

Excel 2010
AB
1Column A
211213
3211
4112
514312

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
B2=LEN(A2)-LEN(SUBSTITUTE(A2,1,""))
B3=LEN(A3)-LEN(SUBSTITUTE(A3,1,""))
B4=LEN(A4)-LEN(SUBSTITUTE(A4,1,""))
B5=LEN(A5)-LEN(SUBSTITUTE(A5,1,""))

<tbody>
</tbody>

<tbody>
</tbody>

Nice idea Michael. You could extend it to a single array formula with

=SUM(LEN(A2:A6))-SUM(LEN(SUBSTITUTE(A2:A6,1,"")))

@skr971, confirm with CTRL + Shift + Enter (NOT just Enter)

#### skr971

##### New Member
Thanks Michael for your suport but i need total sum(like answer 8) not every cell result

#### Michael M

##### Well-known Member

@Teeroy
Nice back at ya'.... ...I didn't read the SUM bit.... #### skr971

##### New Member
Hi Teeroy,

=SUM(LEN(A2:A6))-SUM(LEN(SUBSTITUTE(A2:A6,1,"")))

it's not working, i need sum of number 1(answer 8)

#### azumi

##### Well-known Member

=SUM(LEN(A2:A5)-LEN(SUBSTITUTE(A2:A5,1,"")))

Array Formula, when ENTER you need to press CTRl-SHIFT-ENTER button together

Thanks

#### Michael M

##### Well-known Member
Did you enter it with CTRL + SHIFT + ENTER......not just Enter !!

Last edited:

#### skr971

##### New Member
Thanks Michael..it's Working fine Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,164,601
Messages
5,838,300
Members
430,537
Latest member
Antonio11 ### 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