Help, required single formula, instead of using 3

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,648
Office Version
  1. 2010
Platform
  1. Windows
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>


Book1
ABCDEFGHIJKLM
1
2
3
4
5P3111
61101
72011
81101
91202
101303
11X011
121101
13X011
141101
15X011
16X022
17X033
181101
191202
201303
211404
22X011
231101
241202
251303
261404
27X011
281101
291202
301303
311404
321505
331606
341707
351808
36X011
371101
381202
392011
401101
41X011
422022
431101
44
45
46
Sheet2
Cell Formulas
RangeFormula
E6=IF(C6=1,1,0)
E7=IF(C7=1,1+E6,0)
E8=IF(C8=1,1+E7,0)
G6=IF(C6=1,0,1)
G7=IF(C7=1,0,1+G6)
G8=IF(C8=1,0,1+G7)
K6=IF(E6>0,E6,G6)
K7=IF(E7>0,E7,G7)
K8=IF(E8>0,E8,G8)


Thank you in advance
</SPAN></SPAN>

Regards,
</SPAN>
Kishan
</SPAN></SPAN>
 
Last edited:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Maybe...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
C
[/td][td="bgcolor: #DCE6F1"]
D
[/td][td="bgcolor: #DCE6F1"]
E
[/td][td="bgcolor: #DCE6F1"]
F
[/td][td="bgcolor: #DCE6F1"]
G
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
5
[/td][td]
P3​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7
[/td][td]
2​
[/td][td][/td][td][/td][td][/td][td]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
9
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]
2​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
10
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]
3​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
11
[/td][td]
X​
[/td][td][/td][td][/td][td][/td][td]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
12
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
13
[/td][td]
X​
[/td][td][/td][td][/td][td][/td][td]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
14
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
15
[/td][td]
X​
[/td][td][/td][td][/td][td][/td][td]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
16
[/td][td]
X​
[/td][td][/td][td][/td][td][/td][td]
2​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
17
[/td][td]
X​
[/td][td][/td][td][/td][td][/td][td]
3​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
18
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
19
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]
2​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
20
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]
3​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
21
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]
4​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
22
[/td][td]
X​
[/td][td][/td][td][/td][td][/td][td]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
23
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
24
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]
2​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
25
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]
3​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
26
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]
4​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
27
[/td][td]
X​
[/td][td][/td][td][/td][td][/td][td]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
28
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
29
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]
2​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
30
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]
3​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
31
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]
4​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
32
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]
5​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
33
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]
6​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
34
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]
7​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
35
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]
8​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
36
[/td][td]
X​
[/td][td][/td][td][/td][td][/td][td]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
37
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
38
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]
2​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
39
[/td][td]
2​
[/td][td][/td][td][/td][td][/td][td]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
40
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
41
[/td][td]
X​
[/td][td][/td][td][/td][td][/td][td]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
42
[/td][td]
2​
[/td][td][/td][td][/td][td][/td][td]
2​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
43
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]
1​
[/td][/tr]
[/table]


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.
 
Upvote 0
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>
 
Upvote 0
How about:

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 :mad: results splendid as appeal</SPAN></SPAN>

Kind Regards,
</SPAN>
Kishan :-D
</SPAN></SPAN>
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

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
Back
Top