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

 

[Eric W]

Welcome to the MrExcel forum.

Try:

Book5
	K	L	M	N	O
1	Code	# of 5-in-a-row		Code	Pass/Fail
2	AO	2		AO	Pass
3	EO	1		AO	Pass
4				AO	Pass
5				EO	Pass
6				EO	Fail
7				EO	Pass
8				EO	Pass
9				EO	Pass
10				AO	Pass
11				AO	Pass
12				AO	Pass
13				AO	Pass
14				AO	Fail
15				AO	Pass
16				AO	Pass
17				AO	Pass
18				AO	Pass
19				AO	Pass
20				EO	pass
21				EO	pass
22				EO	pass
23				AO	fail
24
Sheet4

Cell Formulas
Range	Formula
L2:L3	L2	=LET(f,FILTER($O$2:$O$100,$N$2:$N$100=K2),s,SEQUENCE(ROWS(f)),fr,FREQUENCY(IF(f="pass",s),IF(f="fail",s)),SUM(--(fr>=5)))

 

[NewbieExcel123]

Welcome to the MrExcel forum.

Try:

Thank you very much, that worked great, I have a question to make it work with what I need, can I use a wild card search on the 'Pass' bit? I also have another criteria which is "Pass (Advisory)" which I need to include in the total of cells matching EO and AO? I did have a go adding in *Pass or ?*** but it broke the formula.

Thank you

 

[NewbieExcel123]

Or if a wild card can't be used, how would I add a second criteria to search "Pass" and "Pass (Advisory)" under AO and EO?
Thank you

 

[Eric W]

I'd suggest using the ISNUMBER(SEARCH("pass",string)) construct. SEARCH will return the offset into string where "pass" starts, or an error if it's not found. Then you can see if you get a number back by using ISNUMBER, telling you that it's in there somewhere. So the formula becomes:

Book5
	K	L	M	N	O
1	Code	# of 5-in-a-row		Code	Pass/Fail
2	AO	2		AO	Pass
3	EO	1		AO	Pass
4				AO	Pass
5				EO	Pass
6				EO	Fail
7				EO	Pass
8				EO	Pass
9				EO	Pass
10				AO	Pass (Advisory)
11				AO	Pass
12				AO	Pass
13				AO	Pass
14				AO	Fail
15				AO	Pass
16				AO	Pass
17				AO	Pass
18				AO	Pass
19				AO	Pass
20				EO	pass
21				EO	pass
22				EO	pass
23				AO	fail
Sheet4

Cell Formulas
Range	Formula
L2:L3	L2	=LET(f,FILTER($O$2:$O$100,$N$2:$N$100=K2),s,SEQUENCE(ROWS(f)),fr,FREQUENCY(IF(ISNUMBER(SEARCH("pass",f)),s),IF(f="fail",s)),SUM(--(fr>=5)))

 

[NewbieExcel123]

Thank you again! After googling I'm glad you came back with that as it kinda of was in the direction I was heading
However I've popped that in but I'm getting a #calc! Errors on ones where there are no AO or EO for it to search, so I assume it needs something to tell to return zero where no AO or EO is present?

This is a great learning journey and you're helping loads, thank you for helping me.

 

[Eric W]

I think this is easiest:

=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(--(fr>=5)))

If the filter doesn't find AO or EO, then it just returns a default value of "Fail", and when that is given to the rest of the formula, it returns 0.

Happy to help!

 

[Cubist]

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.

Eric's formula is counting 5 or more, but your OP asked for more than 5 times. If it's more than 5 times then you can change my formula to 6 towards the end of the formula.
One more option:

Book3
	K	L	M	N	O
1	Code	# of 5-in-a-row		Code	Pass/Fail
2	AO	2		AO	Pass
3	EO	1		AO	Pass
4	XD	0		AO	Pass
5				EO	Pass
6				EO	Fail
7				EO	Pass
8				EO	Pass
9				EO	Pass
10				AO	Pass (Advisory)
11				AO	Pass
12				AO	Pass
13				AO	Pass
14				AO	Fail
15				AO	Pass
16				AO	Pass
17				AO	Pass
18				AO	Pass
19				AO	Pass
20				EO	pass
21				EO	pass
22				EO	pass
23				AO	fail
Sheet1

Cell Formulas
Range	Formula
L2:L4	L2	=SUM(--(SCAN(0,FILTER($O$2:$O$23,$N$2:$N$23=K2,0),LAMBDA(a,b,IF(ISNUMBER(SEARCH("Pass",b)),a+1,0)))=5))

 

[NewbieExcel123]

Eric's formula is counting 5 or more, but your OP asked for more than 5 times. If it's more than 5 times then you can change my formula to 6 towards the end of the formula.
One more option:

Sorry I did mean how many times does Pass or Pass (advisory) appear 5 times in a row, so how many blocks of 5 are there, so if a Fail appears say 3 Passes in, the count starts again after that fail to look for another 5 in a row.

 

[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