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]

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

 

[dappy]

Thanks again for replying,

problem is that we may have 3 or 4 corridor1's or corridor2's.

If there are 4 corridor1's and column c is showing, say, 2 then 2 of the column R's should show value 3, and the other showing value 1. this works fine unless the value in column B with a "4" in column J is higher than the others for corridor1.

 

[Fluff]

Can you please post some sample data, including expected results, using the XL2BB add-in.

 

[dappy]

Of course

TRX_Reduction_Test_06072020.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Corridor	cam	Occ	req			$dn	$operation	preferredBcchMark	channel0Type	channel1Type	channel2Type	channel3Type	channel4Type	channel5Type	channel6Type	channel7Type	adminState	Expexted
2	Corridor1	1	3	1			Corridor1_1	update	0	0	0	0	0	0	0	0	0	1	3
3	Corridor1	2	3	1			Corridor1_2	update	1	4	3	0	0	0	0	0	0	1	1
4	Corridor1	3	3	1			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	1	3
6	Corridor2	7	2	1			Corridor2_7	update	0	4	0	0	0	0	0	0	0	1	1
7	Corridor3	9	4	2			Corridor3_9	update	1	0	3	0	0	0	0	0	0	3	3
8	Corridor3	7	4	2			Corridor3_7	update	1	4	3	0	0	0	0	0	0	1	1
9	Corridor3	8	4	2			Corridor3_8	update	0	0	0	0	0	0	0	0	0	3	3
10	Corridor3	6	4	2			Corridor3_6	update	1	0	3	0	0	0	0	0	0	1	1
Network DATA+check_test

Cell Formulas
Range	Formula
G2:G10	G2	=A2&"_"&B2
H2:H10	H2	="update"
I2:I10	I2	=IF(Network_DATA!$A84>0,Network_DATA!C84,"")
J8:J9,J2:J3	J2	=IF(Network_DATA!$D84=4,4,IF(Network_DATA!$D84=3,3,IF(Network_DATA!$D84=2,0,IF(Network_DATA!$D84=0,0,(IF(Network_DATA!$D84=9,0))))))
B7:B9,B5,B2:B3	B2	=VALUE(MID(Network_DATA!$A84,SEARCH("TRX-",Network_DATA!$A84)+4,SEARCH("/trx",Network_DATA!$A84)-3))
C2:C10	C2	=COUNTIF(A:A,A2)
K2:K10	K2	=IF(Network_DATA!$E84=3,3,0)
R2:R10	R2	=IF(J2=4,1,IF(COUNTIFS(A:A,A2,B:B,"<"&B2)<D2,"1","3"))

 

[dappy]

its only column R that i have issues with

 

[Fluff]

Sorry, but I'm now totally lost, why would you expect 3 in rows 2 & 5?
J is not 4 & there are no lower numbers in B for that corridor.

 

[dappy]

I apologise, clearly i cant describe what i need.

Its based on the column D. if Column D = 1, then all but the row with 4 in Column J will be 3. its a count of what needs to be removed. 3 being remove, 1 being leave.

 

[Fluff]

Maybe
=IF(J2=4,1,IF(OR(D2=1,COUNTIFS(A:A,A2,B:B,">"&B2)<D2),3,1))

 

[dappy]

Close to perfect but, seems doesnt work for all instances.

TRX_Reduction_Test_06072020.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Corridor	cam	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	1	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	1	3
15	Corridor4	4	7	3			Corridor4_4	update	0	3	0	0	0	0	0	0	0	1	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
Network DATA+check_test

Cell Formulas
Range	Formula
B7:B9,B5,B2:B3	B2	=VALUE(MID(Network_DATA!$A84,SEARCH("TRX-",Network_DATA!$A84)+4,SEARCH("/trx",Network_DATA!$A84)-3))
J8:J9,J2:J3	J2	=IF(Network_DATA!$D84=4,4,IF(Network_DATA!$D84=3,3,IF(Network_DATA!$D84=2,0,IF(Network_DATA!$D84=0,0,(IF(Network_DATA!$D84=9,0))))))
B11,B16:B18	B11	=VALUE(MID(Network_DATA!$A107,SEARCH("TRX-",Network_DATA!$A107)+4,SEARCH("/trx",Network_DATA!$A107)-3))
J11,J18,J13	J11	=IF(Network_DATA!$D107=4,4,IF(Network_DATA!$D107=3,3,IF(Network_DATA!$D107=2,0,IF(Network_DATA!$D107=0,0,(IF(Network_DATA!$D107=9,0))))))
C2:C18	C2	=COUNTIF(A:A,A2)
G2:G18	G2	=A2&"_"&B2
R2:R18	R2	=IF(J2=4,1,IF(OR(D2=1,COUNTIFS(A:A,A2,B:B,">"&B2)<D2),3,1))