Excel: Formula for counting most recent consecutive failures based on multiple criteria.

Hello, I am trying to create a formula that will count the most recent consecutive appearances of "Fail" in the table below based on a number of criteria. I have been researching and come across a proposal for using a max/frequency array formula which I adapted for this dataset and pasted in below. The problem is that I want it to only count the most recent occurences of consecutive failure based on the date entered. For example the formula I am using returns a count of 5 consecutive failures before 1/11/2016. I want the result of this formula to display as 1 consecutive failure because there are "Pass" values that proceed the November row. If I was to set the date value in the formula to "1/12/2016" the formula should display 2 consecutive failures because both November and December's values are a 'Fail' but the failures earlier in the year should be disregarded. I am hoping to develop a catch-all formula that can be carried across the entire history of my data which will continue to be appended to each month and will only count the most recent set of consecutive failures for the given criteria. I have a sample workbook I can provide with the data and formula if that would be helpful. **enter as array formula (ctrl+shift+enter) =MAX(FREQUENCY(IF(A5:A16<="1/11/2016",IF(B5:B16="apple",IF(C5:C16="banana",IF(D5:D16="Fail",ROW(D5:D16))))),IF(A5:A16>="1/11/2016",IF(B5:B16<>"apple",IF(C5:C16<>"banana",IF(D5:D16<>"Fail",ROW(D5:D16)))))))

1/01/2016 apple banana Fail
1/02/2016 apple banana Fail
1/03/2016 apple banana Fail
1/04/2016 apple banana Pass
1/05/2016 apple banana Pass
1/06/2016 apple banana Pass
1/07/2016 apple banana Pass
1/08/2016 apple banana Pass
1/09/2016 apple banana Pass
1/10/2016 apple banana Pass
1/11/2016 apple banana Fail
1/12/2016 apple banana Fail

This question generated 11 answers. To proceed to the answers, click here.

This thread is current as of November 28, 2016.

For more resources for Microsoft Excel