Data Search Count

I have a spreadsheet with multiple coulmns of data with rows which equate to each site location.

Basically I am after searching one column which says "Earth Rods Fitted" which is located in Column K which goes from K4:K767

In each the rows its either yes or no answer.

So the search would count number of "no" entries in column K.

But then it would search also on column Y (Y4:Y767) for a range of values of risk assessment.

So in column Y you could have each row with different assessment risk score from 0-300

So my search would need to count coloumn K for no earth rods fitted and then count within this range the number of cells in column Y which have risk score say between 200 to 400.

Column K Coulumn y
Earth Rods Fitted Risk Score
No 350
Yes 55
No 222
No 90

So in above case there is 3 enries with no earth rods in column K and in Column Y we are counting rows which have a value in range of 200-400 which above there is 2 entries. So basically I know there is 3 sites needed doing and my worst to based on risk is the two which are 350 and 222.

I have messed around with COUNTIF functions which I can search column K ok, but doing the range search on y in conjuction with K I am finding hard. Someone mentioned use Vlookup but not sure how to do it.