count without countif

[methody]

Hi
I have a row of data with 1's or 0's in adjacent cells - say 10 cells. No more than 2 1's can be beside each other (ie never 111 or 1111)

1011001101

I want to count the number of times there are 2 1's side by side and the number of times there is a single 1 on its own.
In the row above the answer for both would be 2.
I know this could be done by summing adjacent cells and using countif but I want to avoid using the countif formula.

thanks

 

[Eric W]

Try:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K
1		1	0	1	1	0	0	1	1	0	1
2
3	Group size:	1	2	3	4	5
4	Count:	2	2	0	0	0
Sheet8

Cell Formulas
Range	Formula
B4:F4	B4	=SUM(--(FREQUENCY(IF($B$1:$K$1=1,COLUMN($B$1:$K$1)),IF($B$1:$K$1<>1,COLUMN($B$1:$K$1)))=B3))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[methody]

Thank you. That does exactly what I asked for but it still not quite what I am looking for. I should have explained more clearly. The formula cannot include any type of 'if' formula.

It is to do with linear programming and using solver and I need to keep the model linear. Effectively this means that the solution is likely to be a longwinded and cumbersome one but using fairly simple formula. not sure if it is possible.

thanks again

 

[Eric W]

I could rewrite that formula to exclude the IF, but it would still implicitly be there. The main problem is that your data consists of 1's and 0's. This means it is a non-continuous function, so whatever formula you use, it remains non-continuous. About the only option I can suggest if you're using Solver, is to use the Evolutionary method, which handles non-continuous functions. Admittedly, not as well, but better than nothing. You'd need to examine your model and see how to optimize it for the Solver. It seems that's what you're trying to do, but there are limitations.

 

[methody]

Was thinking that it would probably be enough to calculate either the number of 11's or the number of single 1's but don't need both as I could derive the other. Does that help?

 

[Eric W]

Not really. Both the "count single 1" and "count double 1" formulas require require looking at the next cell, which makes them both non-continuous.

 

[methody]

Thanks for your time

 

[Tetra201]

... The formula cannot include any type of 'if' formula...

Maybe this (lightly tested):

Number of 11's =SUMPRODUCT(B1:J1*C1:K1)
Number of single 1's =SUM(B1:K1)-2*SUMPRODUCT(B1:J1*C1:K1)