# Countif

#### tomacz

##### New Member
Dear All,

I would like to ask you for a help with some dynamic formula. I dont know the solution so that's why I am posting this question and I would be glad for any answer.

I have a spreadsheet that simulates bet on roulette with 50% odds of winning. There is a bunch of ones and zeros that represent win or loss (1-win, 0 loss). I would like to create a formula that would follow this logic bet=10*2^number of preceding losses. Therefore I need to calculate number of losses after last win and before any future gain. So I need to count all zeros between ones and to add 1 to the upcoming win. I am aware of the fact that my explanation might be sort of misleading so please find an example below from which the logic is apparent.

<table style="border-collapse: collapse; width: 128pt;" width="170" border="0" cellpadding="0" cellspacing="0"><col style="width: 24pt;" width="32"> <col style="width: 52pt;" width="69" span="2"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 24pt;" width="32" height="20">Line</td> <td class="xl65" style="width: 52pt;" width="69">Count</td> <td class="xl66" style="width: 52pt;" width="69">Probablity</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">1</td> <td class="xl68">0</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">2</td> <td class="xl68">1</td> <td class="xl67">1.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">3</td> <td class="xl68">0</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">4</td> <td class="xl68">1</td> <td class="xl67">1.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">5</td> <td class="xl68">0</td> <td class="xl67">1.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">6</td> <td class="xl68">0</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">7</td> <td class="xl68">1</td> <td class="xl67">1.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">8</td> <td class="xl68">0</td> <td class="xl67">1.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">9</td> <td class="xl68">0</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">10</td> <td class="xl68">1</td> <td class="xl67">1.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">11</td> <td class="xl68">0</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">12</td> <td class="xl68">1</td> <td class="xl67">1.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">13</td> <td class="xl68">0</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">14</td> <td class="xl68">1</td> <td class="xl67">1.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">15</td> <td class="xl68">0</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">16</td> <td class="xl68">1</td> <td class="xl67">1.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">17</td> <td class="xl68">0</td> <td class="xl67">1.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">18</td> <td class="xl68">0</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">19</td> <td class="xl68">1</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">20</td> <td class="xl68">2</td> <td class="xl67">1.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">21</td> <td class="xl68">0</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">22</td> <td class="xl68">1</td> <td class="xl67">1.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">23</td> <td class="xl68">0</td> <td class="xl67">1.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">24</td> <td class="xl68">0</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">25</td> <td class="xl68">1</td> <td class="xl67">1.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">26</td> <td class="xl68">0</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">27</td> <td class="xl68">1</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">28</td> <td class="xl68">2</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">29</td> <td class="xl68">3</td> <td class="xl67">1.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">30</td> <td class="xl68">0</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">31</td> <td class="xl68">1</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">32</td> <td class="xl68">2</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">33</td> <td class="xl68">3</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">34</td> <td class="xl68">4</td> <td class="xl67">0.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">35</td> <td class="xl68">5</td> <td class="xl67">1.00</td> </tr> </tbody></table>
Thank you very much for your help.

Kind regards,
Tomas

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### Andrew Fergus

##### MrExcel MVP
Hi and welcome to MrExcel

I'm not sure I understand the question 100%, but how about something like this to calculate the number of consecutive losses:
Code:
A        B
1 Win/Loss Losses
2 0        1
3 1        0
4 0        1
5 1        0
6 0        1
7 0        2
8 1        0
9 0        1
10 0        2
11 1        0
12 0        1
13 1        0
Sheet2
[Table-It] version 09 by Erik Van Geit
Code:
RANGE   FORMULA (1st cell)
B2      =(A2=0)*1
B3:B13  =IF(A3=1,0,B2+1)
[Table-It] version 09 by Erik Van Geit

Notice the formula in cell B2 is different to that in cell B3 - B3 is to be copied down.

Andrew

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,522
Messages
5,837,836
Members
430,517
Latest member
chessypack86

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