Excel Count Formula

[aqeelnokia99]

Hi All i want to count every number 2 as 1,2,3,4,5....

Qty	Result I want
2​	1​
2​	1​
1​
2​	2​
2​	2​
2​	3​
2​	3​
2​	4​
2​	4​

 

[6StringJazzer]

Please explain how you get "Result I want". I have no idea how you got those numbers.

 

[Joe4]

If I understand you correctly, assuming your data begins in cell A1, enter this formula on row 1 and copy down for all rows:

=IF(A1=2,ROUNDUP(COUNTIF(A$1:A1,2)/2,0),"")

this seems to return the exact expected results you showed in your original post.

 

[Fluff]

Maybe

++Fluff.xlsm
	A	B
1	Qty	Result I want
2	2	1
3	2	1
4	1
5	2	2
6	2	2
7
8
9	2	3
10	2	3
11	2	4
12	2	4
13
Sheet5

Cell Formulas
Range	Formula
B2:B12	B2	=IF(A2=2,INT((COUNTIFS(A$2:A2,2)-1)/2)+1,"")

 

[aqeelnokia99]

its working Thanks You very very much Friend

If I understand you correctly, assuming your data begins in cell A1, enter this formula on row 1 and copy down for all rows:

=IF(A1=2,ROUNDUP(COUNTIF(A$1:A1,2)/2,0),"")

this seems to return the exact expected results you showed in your original post.

 

[aqeelnokia99]

Thanks Alot

Maybe

++Fluff.xlsm
	A	B
1	Qty	Result I want
2	2	1
3	2	1
4	1
5	2	2
6	2	2
7
8
9	2	3
10	2	3
11	2	4
12	2	4
13
Sheet5

Cell Formulas
Range	Formula
B2:B12	B2	=IF(A2=2,INT((COUNTIFS(A$2:A2,2)-1)/2)+1,"")

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Joe4]

You are welcome.
Glad we were able to help!

 

[6StringJazzer]

Could one of you explain what the original question was? I am impressed with your solutions but I still don't understand what you solved.

 

[Joe4]

It appears to me that they want to count the couplets of "2".
So, the first two "2"s return 1, the second two "2"s return 2, etc.

I gleaned that more from the example that the wording of the question (which I found a bit confusing too).
Admittedly, it was an educated "guess", but it appeared I guessed right!