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

[singha23]

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

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[Canapone]

Hi again,

in E5 array entered

=ROWS($1:5)-IFERROR(LARGE(IF(A$5:$A$16<=A5,IF($D$5:$D$16="Pass",ROW($5:$16))),1),4)

or if you're using Excel 2010

=ROWS($1:5)-IFERROR(AGGREGATE(14,6,ROW($5:$16)/(($A$5:$A$16<=A5)*($D$5:$D$16="Pass")),1),4)

Regards

 

[singha23]

Thanks Canapone! This appears to give exactly the desired results for the sample data set

I need to spend some time looking into how the formula you wrote is working but there is one last thing that I need to be able to do. That is to add the two criteria for the "apple" and "banana" columns to the formula so that I could change some of those values to say "pear" and "orange" and have it count those failures separately in the same manner as the apple and banana values.

 

[Canapone]

Hi,

what version of Excel are you using?

 

[singha23]

Hi, I'm using Excel 2013.