# Help, required single formula, instead of using 3

#### Kishan

##### Well-known Member
Using Excel 2000</SPAN></SPAN>
Hi,
</SPAN></SPAN>

To get final result in the column K, I am using 2 more formula in the column E & G, please can someone make a single formula in the column K.
</SPAN></SPAN>

Using example....
</SPAN></SPAN>

</SPAN></SPAN>

Regards,
</SPAN>
Kishan
</SPAN></SPAN>

Last edited:

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### Marcelo Branco

##### MrExcel MVP
Maybe...

 C​ D​ E​ F​ G​ 5​ P3​ 6​ 1​ 1​ 7​ 2​ 1​ 8​ 1​ 1​ 9​ 1​ 2​ 10​ 1​ 3​ 11​ X​ 1​ 12​ 1​ 1​ 13​ X​ 1​ 14​ 1​ 1​ 15​ X​ 1​ 16​ X​ 2​ 17​ X​ 3​ 18​ 1​ 1​ 19​ 1​ 2​ 20​ 1​ 3​ 21​ 1​ 4​ 22​ X​ 1​ 23​ 1​ 1​ 24​ 1​ 2​ 25​ 1​ 3​ 26​ 1​ 4​ 27​ X​ 1​ 28​ 1​ 1​ 29​ 1​ 2​ 30​ 1​ 3​ 31​ 1​ 4​ 32​ 1​ 5​ 33​ 1​ 6​ 34​ 1​ 7​ 35​ 1​ 8​ 36​ X​ 1​ 37​ 1​ 1​ 38​ 1​ 2​ 39​ 2​ 1​ 40​ 1​ 1​ 41​ X​ 1​ 42​ 2​ 2​ 43​ 1​ 1​

Formula in G6 copied down
=IF(C6=1,COUNTIF(C6:INDEX(C\$6:C6,IF(COUNTIF(C\$6:C6,"<>1")=0,1,LOOKUP(2,1/(C\$6:C6<>1),ROW(C\$6:C6)-ROW(C\$6)+1))),1),COUNTIF(C6:INDEX(C\$6:C6,IF(COUNTIF(C\$6:C6,1)=0,1,LOOKUP(2,1/(C\$6:C6=1),ROW(C\$6:C6)-ROW(C\$6)+1))),"<>1"))

Hope this helps

M.

#### Finalfight40

##### Active Member
Ignore this, found a problem with it.

Last edited:

#### Eric W

##### MrExcel MVP

K6: =IF(C6=1,IF(C5=1,K5+1,1),IF(C5<>1,K5+1,1))

#### Kishan

##### Well-known Member
Maybe...

Formula in G6 copied down
=IF(C6=1,COUNTIF(C6:INDEX(C\$6:C6,IF(COUNTIF(C\$6:C6,"<>1")=0,1,LOOKUP(2,1/(C\$6:C6<>1),ROW(C\$6:C6)-ROW(C\$6)+1))),1),COUNTIF(C6:INDEX(C\$6:C6,IF(COUNTIF(C\$6:C6,1)=0,1,LOOKUP(2,1/(C\$6:C6=1),ROW(C\$6:C6)-ROW(C\$6)+1))),"<>1"))

Hope this helps

M.
Marcelo Branco, thank you for your help the formula worked fine.</SPAN></SPAN>

Kind Regards
</SPAN></SPAN>
Kishan
</SPAN></SPAN>

#### Kishan

##### Well-known Member

K6: =IF(C6=1,IF(C5=1,K5+1,1),IF(C5<>1,K5+1,1))
Eric W, thanks for a formula, the formula look to me tiny and magical results splendid as appeal</SPAN></SPAN>

Kind Regards,
</SPAN>
Kishan
</SPAN></SPAN>