# counting strings of numbers

#### sjournot

##### New Member
I am working with a large set of data and I have something like whats shown below. I only want to count the 1s that are in strings, not the single 1 in red
Is there any way to do that? Not all the strings of 1s are the same size.

0
0
1
1
0
1
0
1
1
1

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

##### MrExcel MVP
I am working with a large set of data and I have something like whats shown below. I only want to count the 1s that are in strings, not the single 1 in red
Is there any way to do that? Not all the strings of 1s are the same size.

0
0
1
1
0
1
0
1
1
1

With the data in A1:A10, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A1:A10=1,ROW(A1:A10)),IF(A1:A10<>1,ROW(A1:A10)))>1,1))

#### sjournot

##### New Member
Sorry, I think I mis-spoke. I need to not only count the strings of ones but I need the ones in the strings to be summed up. So that for this example I would get 5 as an answer.
sorry for the confusion.

but thanks for the quick reply!

#### Ron Coderre

##### MrExcel MVP
Not as elegant as I was striving for...but, here's what I came up with

ARRAY FORMULA, committed with CTRL+SHIFT+ENTER
Code:
``````=SUM(FREQUENCY(IF(A1:A10=1,ROW(A1:A10)),IF(A1:A10<>1,ROW(A1:A10)))-
(FREQUENCY(IF(A1:A10=1,ROW(A1:A10)),IF(A1:A10<>1,ROW(A1:A10)))=1))``````
Does that help?

#### nbrcrunch

##### Well-known Member
=SUM(IF(SEARCH("1",A1:A4,1)>0,1,0))-SUM(IF(ISNUMBER(A1:A4),1,0))

complete with Ctrl-Shift-Enter

##### MrExcel MVP
Sorry, I think I mis-spoke. I need to not only count the strings of ones but I need the ones in the strings to be summed up. So that for this example I would get 5 as an answer.
sorry for the confusion.

but thanks for the quick reply!

If you have the free morefunc.xll add-in installed:

Control+shift+enter, not just enter...

=SUM(IF(SETV(FREQUENCY(IF(A1:A10=1,ROW(A1:A10)),IF(A1:A10<>1,ROW(A1:A10))))>1,GETV()))

which would be a tad faster.

Otherwise, see other replies.

#### sjournot

##### New Member
It works great, Thanks!

Replies
1
Views
288
Replies
28
Views
407
Replies
7
Views
265
Replies
5
Views
317
Replies
6
Views
467

1,190,962
Messages
5,983,873
Members
439,868
Latest member

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