count without countif

methody

Well-known Member
Joined
Jun 17, 2002
Messages
843
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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,639
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
Joined
Jun 17, 2002
Messages
843
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
Joined
Aug 18, 2015
Messages
10,639
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
Joined
Jun 17, 2002
Messages
843

ADVERTISEMENT

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
Joined
Aug 18, 2015
Messages
10,639
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
Joined
Oct 14, 2016
Messages
3,639
... 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)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,133
Messages
5,599,912
Members
414,347
Latest member
tbanack

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top