To numbered as per criteria

[punit83]

Can there be any formula to give number as per count and as per criteria ?

Queries.xlsx
	A	B	C	D	E
1	Data		Result
2	3.00-3.99		Range	Total Count	Numbering
3	4.00-4.99		100-1.10	2	1
4	2.00-2.99		100-1.10		2
5	1.00-1.49		1.11-1.20	3	1
6	0.50-0.69		1.11-1.20		1
7	1.50-1.99		1.11-1.20		2
8	1.50-1.99		1.21-1.30	4	1
9	3.00-3.99		1.21-1.30		1
10	0.40-0.49		1.21-1.30		2
11	0.70-0.89		1.21-1.30		2
12	0.90-0.99		1.30-1.40	6	1
13	1.00-1.49		1.30-1.40		1
14	2.00-2.99		1.30-1.40		1
15	1.50-1.99		1.30-1.40		2
16	1.00-1.49		1.30-1.40		2
17	0.90-0.99		1.30-1.40		2
18	0.50-0.69
19	0.30-0.39
20	0.40-0.49
21	1.00-1.49
22	0.70-0.89
23	1.50-1.99
Sheet1 (3)

Cell Formulas
Range	Formula
D3,D12,D8,D5	D3	=COUNTIF(C3:C19,C3)

Thanks in advance.

 

[punit83]

Here are a couple of simpler options that I think still do what you want

21 08 26.xlsm
	C	D	E
1	Result
2	Range	Total Count	Numbering
3	100-1.10	2	1
4	100-1.10		2
5	1.11-1.20	3	1
6	1.11-1.20		1
7	1.11-1.20		2
8	1.21-1.30	4	1
9	1.21-1.30		1
10	1.21-1.30		2
11	1.21-1.30		2
12	1.30-1.40	6	1
13	1.30-1.40		1
14	1.30-1.40		1
15	1.30-1.40		2
16	1.30-1.40		2
17	1.30-1.40		2
Count - Number

Cell Formulas
Range	Formula
D3:D17	D3	=IF(C3=C2,"",COUNTIF($C$3:$C$17,C3))
E3:E17	E3	=2-(COUNTIF(C$3:C3,C3)<LOOKUP(9^9,D$3:D3)/2+1)

Hello,

First of all so sorry for late reply as didn't got time to work on it as too much busy with work now got some free time and 1st i worked with this formula.

Thank you so much it worked for me.
just want to ask you that is there any option to make 3 cuts ?

Once again thank you.

 

[Peter_SSs]

just want to ask you that is there any option to make 3 cuts ?

What exactly do you mean by that?

Sample data, expected results and more explanation might help.

 

[punit83]

What exactly do you mean by that?

Sample data, expected results and more explanation might help.

Hello,

Above formula was used if i want to number in 2 parts or 2 cuts but now i need formula in which i can number in 3 parts or 3 cuts.
attaching sample.

Thank you

Data		Result
3.00-3.99		Range	Total Count	Numbering
4.00-4.99		100-1.10	2	1
2.00-2.99		100-1.10		2
1.00-1.49		1.11-1.20	3	1
0.50-0.69		1.11-1.20		2
1.50-1.99		1.11-1.20		3
1.50-1.99		1.21-1.30	4	1
3.00-3.99		1.21-1.30		1
0.40-0.49		1.21-1.30		2
0.70-0.89		1.21-1.30		3
0.90-0.99		1.30-1.40	6	1
1.00-1.49		1.30-1.40		1
2.00-2.99		1.30-1.40		2
1.50-1.99		1.30-1.40		2
1.00-1.49		1.30-1.40		3
0.90-0.99		1.30-1.40		3
0.50-0.69
0.30-0.39
0.40-0.49
1.00-1.49
0.70-0.89
1.50-1.99

 

[Peter_SSs]

Thanks for the sample. See if this does what you want.

22 02 14.xlsm
	C	D	E
1	Result
2	Range	Total Count	Numbering
3	100-1.10	2	1
4	100-1.10	2	2
5	1.11-1.20	3	1
6	1.11-1.20	3	2
7	1.11-1.20	3	3
8	1.21-1.30	4	1
9	1.21-1.30	4	1
10	1.21-1.30	4	2
11	1.21-1.30	4	3
12	1.30-1.40	6	1
13	1.30-1.40	6	1
14	1.30-1.40	6	2
15	1.30-1.40	6	2
16	1.30-1.40	6	3
17	1.30-1.40	6	3
Cut

Cell Formulas
Range	Formula
D3:D17	D3	=COUNTIF(C$3:C$17,C3)
E3:E17	E3	=IF(D3<>D2,1,IF(D3=2,2,IF(COUNTIF(D$3:D3,D3)-1<D3/3,1,IF(COUNTIF(D$3:D3,D3)-1<D3/3*2,2,3))))