Countif with single column but criteria

Hey all, my first post...

I did a quick search and read a few posts that used countif statements with multiple criteria, but didn't find what I was looking for.

Basically, what I have been trying unsuccessfully to do is this...
I have a column (Column A) which has three text strings (yes,no,maybe).
What I wanted to happen is IF A(n)=yes AND A(n+1)=yes, count the number of times A(n+2)=no. For example, if I had yes, yes, maybe, no, yes, yes, no, yes, maybe, no in one column. The count should measure 1, because there there are two repeating yes's then followed by a no. However since they are all in the same column, I am not sure how to address this. In the end, I wanted to calculate the percentage of times that repeating yes's are followed by a no. So for above example would be 50% because there were two repeating yes's, but only 1 was followed by a no.

Hope that makes sense. My effort is posted below, but at the step I was at, I just wanted to see if I could count everytime an yes was followed by a no. I had to offset it by one row to get it to work.
=COUNTIFS(A2:A129,"=yes",A3:A130,"=no")
I would love to be able to do A:A, because I would be copying this formula to different sheets which different number of rows and instead of having to update all of them manually, would be tons easier to just paste the formula once and have it update everything. However, beggers can't be choosers, anything would help!

Hi

Using your example data in the range A1:A10 try this array entered (ctrl, shift, enter) formula

=SUM(IF((A2:A9="yes")*(A1:A8="yes")*(A3:A10="no"),1,0))

or a bit more logically
=SUM(IF((A1:A8="yes")*(A2:A9="yes")*(A3:A10="no"),1,0))

or without array enter using SUMPRODUCT
=SUMPRODUCT(--(A1:A8="yes"),--(A2:A9="yes"),--(A3:A10="no"))


rylo

Originally Posted by rylo

or without array enter using SUMPRODUCT
=SUMPRODUCT(--(A1:A8="yes"),--(A2:A9="yes"),--(A3:A10="no"))
rylo

Thanks! That worked like a charm!
To make this even more difficult...is it possible to do the following in different sytax?
=SUMPRODUCT(--(AND(A1:A8="yes",B1:B8="high")),--(AND(A2:A9="yes",B2:B9="high")),--(AND(A3:A10="no",B3:B19="low")))

As to only count when TWO columns meet a certain criteria...
My attempt above didn't work :-(