Sum Frequency IF formulas with multiple criteria

[NewbieExcel123]

Hi all,

I have a formula I found on here (thanks for the help), but I need to add another criteria for it to search for.
It searches for how many times the word 'Pass' appears more than 5 times in a row until a 'Fail' is found, and then it tells me the total times that occurs.

Column N	Column O
AO	Pass
AO	Pass
AO	Pass
EO	Pass
EO	Fail
EO	Pass
EO	Pass
EO	Pass
AO	Pass
AO	Pass

Currently the formula is:
=SUM(--(FREQUENCY(IF(O5:O99="Pass",ROW(O5:O99)),IF(O5:O99="Fail",ROW(O5:O99)))>=5))

But I want to add the option for have one formula also search for EO and another formula search for AO.

Essentially I need to add into the above formula:

$N$5:$N$100,"=AO", $O$5:$O$100,"=Pass
or
$N$5:$N$100,"=EO", $O$5:$O$100,"=Pass

I am unable to upload files unfortunately.

Thank you

 

[NewbieExcel123]

So I've come across a problem, it all seemed to be working fine, turned the result into a percentage so that 1 block of 5 "Pass" including "Pass (Advisory)" would equal 50% and two blocks of 5 would equal 100%.
But for some reason it's not done it in this one, is it because there's no breaks between the two blocks of 5? just 10 "Pass" in a row?

AO Passes = 10	50%
EO Passes = 0	None Completed
AO	Fail
AO	Pass
AO	Pass
AO	Pass
AO	Pass
AO	Pass
AO	Pass
AO	Pass
AO	Pass
AO	Pass

=IFERROR(LET(f,FILTER($I$5:$I$103,$H$5:$H$103="AO"),s,SEQUENCE(ROWS(f)),fr,FREQUENCY(IF(ISNUMBER(SEARCH("Pass",f)),s),IF(f="fail",s)),SUM(--(fr>=5)))*0.5,"None completed")

Thank you

It is meant to have 10 "Pass" in the above table

 

[Eric W]

But for some reason it's not done it in this one, is it because there's no breaks between the two blocks of 5? just 10 "Pass" in a row?

The way the formula works now is that it finds any streaks, whether the streak is 1 or 5 or 23, then counts each streak that is >=5, so a streak of 5 counts as 1, and a streak of 23 counts as 1. But if I understand you, if a streak is over 5, you want to count that as 1, and each section of 5 should count as 1? So an overall streak of 23 should count as 4? If so try:

=LET(f,FILTER($O$2:$O$100,$N$2:$N$100=K2,"Fail"),s,SEQUENCE(ROWS(f)),fr,FREQUENCY(IF(ISNUMBER(SEARCH("pass",f)),s),IF(f="fail",s)),SUM(INT(fr/5)))

I had to think about it, but it turned out to be an easy change.