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

#### singha23

##### New Member
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>

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hi,

 =MAX(FREQUENCY(IF(A5:A16<=DATE(2016,11,1),IF(B5:B16="apple",IF(C5:C16="banana",IF(D5:D16="Fail",ROW(D5:D16))))),IF(A5:A16<=DATE(2016,11,1),IF(B5:B16="apple",IF(C5:C16="banana",IF(D5:D16<>"Fail",ROW(D5:D16)))))))

<tbody>
</tbody>

Is that of any help to you?

Regards

Last edited:
Thanks for your reply Canapone. The formula you provided gives a result of 3. I am trying to get a result of 1 from the formula because the November failure is the first one in the second sequence of failures. The earlier failures need to ideally be disregarded. Hopefully this makes sense.

Thanks for your reply Canapone. The formula you provided gives a result of 3. I am trying to get a result of 1 from the formula because the November failure is the first one in the second sequence of failures. The earlier failures need to ideally be disregarded. Hopefully this makes sense.

No, it doesn't. One should never describe one's Excel problem in terms of an inappropriate or non-working formula. Better: describe in words how 1 obtains given the sample you posted. Is 1 a count or what?

Hi singha23,

not sure:

Code:
``=SUM(--(N(IF(1,FREQUENCY(IF(A5:A16<=DATE(2016,11,1),IF(B5:B16="apple",IF(C5:C16="banana",IF(D5:D16="Fail",ROW(D5:D16))))),IF(A5:A16<=DATE(2016,11,1),IF(B5:B16="apple",IF(C5:C16="banana",IF(D5:D16<>"Fail",ROW(D5:D16))))))))>1))``

to be confirmed with contr+shift+enter

To be tested...

Regards

--------

Sorry Aladin Akyurek, did not mean to overlap.

Last edited:
Thanks for your replies guys. Canapone that gets pretty close to what I was after. It counts 1 failure for November and 2 failures for December. It doesn't appear to count the earlier set of failures though if I set the date back to February the failure count shows 1 for example instead of 2. To give a clearer idea of what I am hoping for the results of the formula I am hoping for would be as follows:

For some background, I have written a custom function in vba which gives the desired result but it is a little slow when calculating over a lot of rows so was hoping there was a way to do this with a formula. If the frequency formula is not the best way to do this open to any and all suggestions.

 Date Consecutive fail count 1/01/2016 1 1/02/2016 2 1/03/2016 3 1/04/2016 0 1/05/2016 0 1/06/2016 0 1/07/2016 0 1/08/2016 0 1/09/2016 0 1/10/2016 0 1/11/2016 1 1/12/2016 2

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

Hi again

the data you shared show 1 (group of) consecutive Fail <=february 2016 (not 2).

Last edited:
Hi, I am hoping to have each individual failure counted but only when they are in a consecutive group. If you look at my previous post you will see what I was hoping the results would be for each row. Perhaps this isn't something that can be done with a formula and my custom function was the way to go?

Hi, I am hoping to have each individual failure counted but only when they are in a consecutive group. If you look at my previous post you will see what I was hoping the results would be for each row. Perhaps this isn't something that can be done with a formula and my custom function was the way to go?

Still not clear why 1 is the desired count...

For November, 1 is the desired count because in October the value was a "Pass". Therefore in counting consecutive 'Fails' this is the first in the sequence. December would be the second 'Fail' in that sequence. In the first group of consecutive fails the count would be 1 for January, 2 for February and 3 for March. April would be zero because that row is a 'Pass' and so on.

Replies
4
Views
143
Replies
4
Views
276
Replies
3
Views
243
Replies
7
Views
119
Replies
3
Views
495

1,203,245
Messages
6,054,368
Members
444,720
Latest member
saathvik

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back