counting strings of numbers

[sjournot]

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

 

[Aladin Akyurek]

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]

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]

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

ARRAY FORMULA, committed with CTRL+SHIFT+ENTER
(instead of just ENTER)

=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]

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

complete with Ctrl-Shift-Enter

replace with your range

 

[Aladin Akyurek]

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]

It works great, Thanks!