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.

 

[bobsan42]

do you mean COUNTIFS ?
I am not sure I understand your data and your goal.

 

[punit83]

do you mean COUNTIFS ?
I am not sure I understand your data and your goal.

Means

if 1.11-1.20 entry is 2 time & i want to divide list in 2 parts than
one entry shall be numbered as 1 and second entry shall be numbered as 2

&

if 1.11-1.20 entry is 3 time & i want to divide list in 2 parts than
first 2 entry shall be numbered as 1 and third entry shall be numbered as 2

so i can separate list in 2 parts, 3 parts as per client need.

Most of time we have 3000+ entries in a sheet and we want to send list to client in 2 parts or 3 so i can filter and copy/move selected data to new excel and can send.

Thank you

 

[Phuoc]

Try this in E3:

=IF(LOOKUP(10^10,$D$3:D3)/2>ROW(D3)-LOOKUP(10^10,$D$3:D3,ROW($D$3:D3)),1,2)

 

[punit83]

Try this in E3:

=IF(LOOKUP(10^10,$D$3:D3)/2>ROW(D3)-LOOKUP(10^10,$D$3:D3,ROW($D$3:D3)),1,2)

Perfect. Thank you so much ☻.

 

[punit83]

Try this in E3:

=IF(LOOKUP(10^10,$D$3:D3)/2>ROW(D3)-LOOKUP(10^10,$D$3:D3,ROW($D$3:D3)),1,2)

Please need little additional help in formula

i applied this formula in table and i see the result is changed.
pls guide me in this forumal or suggestion in countif formula that count doesn't repeat more than 1 time.

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	1
5	1.00-1.49		1.11-1.20	3	1
6	0.50-0.69		1.11-1.20	3	1
7	1.50-1.99		1.11-1.20	3	1
8	1.50-1.99		1.21-1.30	4	1
9	3.00-3.99		1.21-1.30	4	1
10	0.40-0.49		1.21-1.30	4	1
11	0.70-0.89		1.21-1.30	4	1
12	0.90-0.99		1.30-1.40	6	1
13	1.00-1.49		1.30-1.40	6	1
14	2.00-2.99		1.30-1.40	6	1
15	1.50-1.99		1.30-1.40	6	1
16	1.00-1.49		1.30-1.40	6	1
17	0.90-0.99		1.30-1.40	6	1
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:D17	D3	=COUNTIF($C$3:$C$19,C3)
E3:E17	E3	=IF(LOOKUP(10^10,$D$3:D3)/2>ROW(D3)-LOOKUP(10^10,$D$3:D3,ROW($D$3:D3)),1,2)

Thank you

 

[Phuoc]

Try this in D3:

=IF(COUNTIF($C$3:C3,C3)=1,COUNTIF($C$3:$C$19,C3),"")

 

[punit83]

Try this in D3:

=IF(COUNTIF($C$3:C3,C3)=1,COUNTIF($C$3:$C$19,C3),"")

Solved Thank you so much ☻
such a little formula and a great solution ♫

 

[Peter_SSs]

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)

 

[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)

Thank you ☻
Will give a try.