look up multiple values and counts to decide cell value

[dappy]

Hi,

this is what i have in col R

=IF(J129=4,1,IF(COUNTIFS(A:A,A129,B:B,"<"&B129)<D129,"1","3"))

So this works fine if the the value in column J is 4, not a problem. but its the value in column B that's causing an issue and i'm totally stuck

if Col J is 4 then Col R will equal 1
if Col J is not 4 and the second number in b for that corridor is higher then i get 3 which is cool. the problem is if the value in J is 4 and the value in B is higher than the other corridor value in B then in R its 1 and not 3.

Confusing i know but is any egghead out there that can help?

 

[Fluff]

In that case you are going to have to explain exactly what the rules are.

 

[dappy]

So sorry about this.

column D is the amount of Value "1"s that must be in Column R. with J=4 always being one of them followed by the remaining using the lowest first in column B. all others are value 3.

I really hope this helps

 

[dappy]

speaking of Column R

so if Column D=1 then it is only the row with J=4 where R=1, all others are R=3

if column D=2 then its the row with J=4 plus the row with the lowest value in B where R=1, all others are R=3

if column D =3 then its the row with J=4 plus the 2 rows with the lowest values rows B where R=1, all others are R=3

 

[Fluff]

Ok, how about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Corridor	0	Occ	req			$dn	$operation	preferredBcchMark	channel0Type	channel1Type	channel2Type	channel3Type	channel4Type	channel5Type	channel6Type	channel7Type	adminState	Expexted
2	Corridor1	1	3	2			Corridor1_1	update	0	0	0	0	0	0	0	0	0	1	3
3	Corridor1	2	3	2			Corridor1_2	update	1	4	3	0	0	0	0	0	0	1	1
4	Corridor1	3	3	2			Corridor1_3	update	1	0	3	0	0	0	0	0	0	3	3
5	Corridor2	6	2	1			Corridor2_6	update	0	0	0	0	0	0	0	0	0	3	3
6	Corridor2	7	2	1			Corridor2_7	update	0	4	0	0	0	0	0	0	0	1	1
7	Corridor3	9	5	2			Corridor3_9	update	1	0	3	0	0	0	0	0	0	3	3
8	Corridor3	7	5	2			Corridor3_7	update	1	4	3	0	0	0	0	0	0	1	1
9	Corridor3	8	5	2			Corridor3_8	update	0	0	0	0	0	0	0	0	0	3	3
10	Corridor3	6	5	2			Corridor3_6	update	1	0	3	0	0	0	0	0	0	3	3
11	Corridor3	2	5	2			Corridor3_2	update	0	3	0	0	0	0	0	0	0	1	1
12	Corridor4	1	7	3			Corridor4_1	update	1	3	3	0	0	0	0	0	0	1	1
13	Corridor4	2	7	3			Corridor4_2	update	0	3	0	0	0	0	0	0	0	1	1
14	Corridor4	3	7	3			Corridor4_3	update	1	3	3	0	0	0	0	0	0	3	3
15	Corridor4	4	7	3			Corridor4_4	update	0	3	0	0	0	0	0	0	0	3	3
16	Corridor4	5	7	3			Corridor4_5	update	1	3	3	0	0	0	0	0	0	3	3
17	Corridor4	6	7	3			Corridor4_6	update	0	4	0	0	0	0	0	0	0	1	1
18	Corridor4	10	7	3			Corridor4_10	update	0	3	0	0	0	0	0	0	0	3	3
Main

Cell Formulas
Range	Formula
R2:R18	R2	=IF(J2=4,1,IF(COUNTIFS(A:A,A2,B:B,"<"&B2)<D2-1,1,3))

 

[dappy]

I bow to your massive brain, seriously you're amazing. thank you so much, you're a star! that works perfectly.

Thank you again. I'm humbled by how generous you are with your time.

 

[Fluff]

Glad to help & thanks for the feedback.

 

[dappy]

Hello again,

I jumped the gun, i still cant get the results correct all the time in column R. Would you mind having another look for me please? I thought all was well but it seems quite random

mr_excel.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	corridor	0	occ	re	dif		$dn	$operation	preferredBcchMark	channel0Type	channel1Type	channel2Type	channel3Type	channel4Type	channel5Type	channel6Type	channel7Type	adminState	Expected
2	Corridor1	1	2	1	1		Corridor1_1	update	1	4	3	0	0	0	0	0	0	1	1
3	Corridor1	2	2	1	1		Corridor1_2	update	0	3	0	0	0	0	0	0	0	3	3
4	Corridor2	5	4	3	1		Corridor2_5	update	1	4	3	0	0	0	0	0	0	1	1
5	Corridor2	6	4	3	1		Corridor2_6	update	0	0	0	0	0	0	0	0	0	1	1
6	Corridor2	10	4	3	1		Corridor2_10	update	0	0	0	0	0	0	0	0	0	3	3
7	Corridor2	9	4	3	1		Corridor2_9	update	1	0	3	0	0	0	0	0	0	3	1
8	Corridor4	1	5	4	1		Corridor4_1	update	1	0	3	0	0	0	0	0	0	1	1
9	Corridor4	2	5	4	1		Corridor4_2	update	0	3	0	0	0	0	0	0	0	1	1
10	Corridor4	5	5	4	1		Corridor4_5	update	1	4	3	0	0	0	0	0	0	1	1
11	Corridor4	6	5	4	1		Corridor4_6	update	0	0	0	0	0	0	0	0	0	3	1
12	Corridor4	10	5	4	1		Corridor4_10	update	0	0	0	0	0	0	0	0	0	3	3
13	Corridor8	11	7	4	3		Corridor8_11	update	1	0	3	0	0	0	0	0	0	3	3
14	Corridor8	1	7	4	3		Corridor8_1	update	1	0	3	0	0	0	0	0	0	1	1
15	Corridor8	2	7	4	3		Corridor8_2	update	0	0	0	0	0	0	0	0	0	1	1
16	Corridor8	10	7	4	3		Corridor8_10	update	0	0	0	0	0	0	0	0	0	3	3
17	Corridor8	9	7	4	3		Corridor8_9	update	1	0	3	0	0	0	0	0	0	3	3
18	Corridor8	3	7	4	3		Corridor8_3	update	1	4	3	0	0	0	0	0	0	1	1
19	Corridor8	4	7	4	3		Corridor8_4	update	0	0	0	0	0	0	0	0	0	3	1
Network DATA+check_test

Cell Formulas
Range	Formula
G2:G19	G2	=A2&"_"&B2
H2:H19	H2	="update"
I2:I19	I2	=IF(Network_DATA!$A2>0,Network_DATA!C2,"")
J18:J19,J15:J16,J9:J12,J2:J6	J2	=IF(Network_DATA!$D2=4,4,IF(Network_DATA!$D2=3,3,IF(Network_DATA!$D2=2,0,IF(Network_DATA!$D2=0,0,(IF(Network_DATA!$D2=9,0))))))
B14:B17,B4:B12	B4	=VALUE(MID(Network_DATA!$A4,SEARCH("TRX-",Network_DATA!$A4)+4,SEARCH("/trx",Network_DATA!$A4)-3))
K2:K19	K2	=IF(Network_DATA!$E2=3,3,0)
C2:C19	C2	=COUNTIF(A:A,A2)
E2:E19	E2	=C2-D2
R2:R19	R2	=IF(J2=4,1,IF(COUNTIFS(A:A,A2,B:B,"<"&B2)<D2-1,1,3))

 

[Fluff]

How about
=IF(J2=4,1,IF(COUNTIFS(A:A,A2,B:B,"<"&B2)<=D2-1,1,3))

 

[dappy]

perfect, almost :D its the issue where the value in Col B with a value of 4 in column J is higher than others for the same corridor. corridor11, 20 and 7 have the issue

mr_excel.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	corridor	0	occ	re	dif		$dn	$operation	preferredBcchMark	channel0Type	channel1Type	channel2Type	channel3Type	channel4Type	channel5Type	channel6Type	channel7Type	adminState	Expected
2	Corridor1	1	2	1	1		Corridor1_1	update	1	4	3	0	0	0	0	0	0	1	1
3	Corridor1	2	2	1	1		Corridor1_2	update	0	3	0	0	0	0	0	0	0	3	3
4	Corridor2	5	4	3	1		Corridor2_5	update	1	4	3	0	0	0	0	0	0	1	1
5	Corridor2	6	4	3	1		Corridor2_6	update	0	0	0	0	0	0	0	0	0	1	1
6	Corridor2	10	4	3	1		Corridor2_10	update	0	0	0	0	0	0	0	0	0	3	3
7	Corridor2	9	4	3	1		Corridor2_9	update	1	0	3	0	0	0	0	0	0	1	1
8	Corridor4	1	5	4	1		Corridor4_1	update	1	0	3	0	0	0	0	0	0	1	1
9	Corridor4	2	5	4	1		Corridor4_2	update	0	3	0	0	0	0	0	0	0	1	1
10	Corridor4	5	5	4	1		Corridor4_5	update	1	4	3	0	0	0	0	0	0	1	1
11	Corridor4	6	5	4	1		Corridor4_6	update	0	0	0	0	0	0	0	0	0	1	1
12	Corridor4	10	5	4	1		Corridor4_10	update	0	0	0	0	0	0	0	0	0	3	3
13	Corridor8	11	7	4	3		Corridor8_11	update	1	0	3	0	0	0	0	0	0	3	3
14	Corridor8	1	7	4	3		Corridor8_1	update	1	0	3	0	0	0	0	0	0	1	1
15	Corridor8	2	7	4	3		Corridor8_2	update	0	0	0	0	0	0	0	0	0	1	1
16	Corridor8	10	7	4	3		Corridor8_10	update	0	0	0	0	0	0	0	0	0	3	3
17	Corridor8	9	7	4	3		Corridor8_9	update	1	0	3	0	0	0	0	0	0	3	3
18	Corridor8	3	7	4	3		Corridor8_3	update	1	4	3	0	0	0	0	0	0	1	1
19	Corridor8	4	7	4	3		Corridor8_4	update	0	0	0	0	0	0	0	0	0	1	1
20	Corridor11	10	2	1	1		Corridor11_10	update	1	4	3	0	0	0	0	0	0	1	1
21	Corridor11	9	2	1	1		Corridor11_9	update	0	3	0	0	0	0	0	0	0	1	3
22	Corridor7	5	5	2	3		Corridor7_5	update	1	0	3	0	0	0	0	0	0	3	3
23	Corridor7	6	5	2	3		Corridor7_6	update	0	4	0	0	0	0	0	0	0	1	1
24	Corridor7	1	5	2	3		Corridor7_1	update	1	0	3	0	0	0	0	0	0	1	1
25	Corridor7	2	5	2	3		Corridor7_2	update	0	0	0	0	0	0	0	0	0	1	3
26	Corridor7	10	5	2	3		Corridor7_10	update	0	0	0	0	0	0	0	0	0	3	3
27	Corridor20	9	4	2	2		Corridor20_9	update	1	0	3	0	0	0	0	0	0	3	3
28	Corridor20	1	4	2	2		Corridor20_1	update	1	0	3	0	0	0	0	0	0	1	1
29	Corridor20	2	4	2	2		Corridor20_2	update	0	0	0	0	0	0	0	0	0	1	3
30	Corridor20	5	4	2	2		Corridor20_5	update	1	4	3	0	0	0	0	0	0	1	1
Network DATA+check_test

Cell Formulas
Range	Formula
G2:G30	G2	=A2&"_"&B2
H2:H30	H2	="update"
I2:I30	I2	=IF(Network_DATA!$A2>0,Network_DATA!C2,"")
J29,J25:J26,J18:J19,J15:J16,J9:J12,J2:J6	J2	=IF(Network_DATA!$D2=4,4,IF(Network_DATA!$D2=3,3,IF(Network_DATA!$D2=2,0,IF(Network_DATA!$D2=0,0,(IF(Network_DATA!$D2=9,0))))))
B22:B30,B14:B17,B4:B12	B4	=VALUE(MID(Network_DATA!$A4,SEARCH("TRX-",Network_DATA!$A4)+4,SEARCH("/trx",Network_DATA!$A4)-3))
C2:C30	C2	=COUNTIF(A:A,A2)
E2:E30	E2	=C2-D2
K2:K30	K2	=IF(Network_DATA!$E2=3,3,0)
R2:R30	R2	=IF(J2=4,1,IF(COUNTIFS(A:A,A2,B:B,"<"&B2)<=D2-1,1,3))

 

[Fluff]

How about
=IF(J2=4,1,IF(COUNTIFS(A:A,A2,B:B,"<"&B2,J:J,"<>4")<D2-1,1,3))