# count without countif

#### methody

##### Well-known Member
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

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### Eric W

##### MrExcel MVP
Try:

Book1 (version 1).xlsb
ABCDEFGHIJK
11011001101
2
3Group size:12345
4Count:22000
Sheet8
Cell Formulas
RangeFormula
B4:F4B4=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

##### Well-known Member
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

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

##### Well-known Member

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

##### MrExcel MVP
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.

#### Tetra201

##### MrExcel MVP
... 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)

Replies
11
Views
207
Replies
5
Views
70
Replies
7
Views
36
Replies
4
Views
60
Replies
1
Views
435

1,127,328
Messages
5,624,036
Members
416,007
Latest member
csf

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