find latest date in a column but only if next column contains "Open"

I have a table of data. one of the columns contains dates (or sometimes blank) and what I need to do is find the latest date but only where the cell in the next column contains "Open".

Now I know I can do it using a dumm column on the right, with a formula that says if status column = open then this cell = date colum. this then gives a column with only the relevalt dats in it.

The problem with this appraoch is when new items are added to the bottom of the list, the formula is not going to pick it up because it wont be in the rows in the bottom of the list (even with extend list formats and formulas it simply doesnt work and never has for me, it works for data validation cells but even then only if you specifically go the the bottom and insert a new row which again is useless as 99% of people are just going to start typing in the firts blank row).

So basically I need a way of getting the latest date in column R but only when column S contains "open", WITHOUT having to use a dummy column with formulas in it which wont be picked up in new rows.

I was using MAX(R13:R5001) which basically allows me 5000 rows (more than the list will ever need and so will always pick up all the dates) and itll return the latest date. However it cannot take into account the contents of the S column and so is picking up dates on closed items as well.

thanks

i take it that it cant be done then

shame cos i need a similar solution for counting the number of 3's 2's and 1's next to open items in another column which would probably be the same solution.