Count if value is Fail and there are over 10 recorded values within 30 days range

Hi all

If someone could help me that would be great.

I am trying to figure out how to write an IF statement which assesses a specific date and outcome in each cell.

If the outcome value is "Fail" and there has been a record of 10 or more fails within 30 days of that specific date then assign a 1. If it does not meet these conditions then assign a 0.

I have attached a worksheet for reference with what I expect should happen. Any help would be great!

Thank you

Thank you for your quick response

For the most part it works however I notice though at cell B34 where it states "Pass" the formula assigns a 1, this should be a 0.

Thank you

30 days from 2-Nov = 3-Oct, in that period there are 14 fails, so this is 1.
Why should it be zero?

btw - "If there is more than 10 fails in the last 30 days of the specific date then count this as 1, if there is less than count this is a 0" - this instruction is incomplete as it omits what shall happen with exactly 10 fails.

Hi thanks for your response

The reason as to why it should be 0 is because the record was marked as "pass", its only the fails that I am interested in counting.

To clarify if there are more than fails 10 in the last 30 days then this is counted as 1, if there were exactly 10 then this would be a 10.

Thank you

Hi Bo_Ry

You have nailed it, this works, thanks so much.

I always find Countifs' formulas a little strange in the sense that you pass the operators as text strings which I find confusing.

Thanks again!