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?

 

[dappy]

You're now higher than any of my family in my christmas card list. you're spot on.

Thank you again and such a quick response. perfect.

 

[Fluff]

Glad to help & thanks for the feedback.