# 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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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,""))

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)

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

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

Hi Teeroy,

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

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

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

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

Thanks

Did you enter it with CTRL + SHIFT + ENTER......not just Enter !!

Last edited:
Thanks Michael..it's Working fine

Replies
12
Views
491
Replies
3
Views
103
Replies
15
Views
369
Replies
2
Views
432
Replies
26
Views
780

1,217,386
Messages
6,136,283
Members
450,002
Latest member
bybynhoc

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