Count cells above yes/no last ten

[Weefergie56]

Hi I want to count the number of yes answers in the last ten cells above that would not count any blank cells as part of the ten. I have found versions but they don't work if the formula is directly below it. Cany anyone help, please? This one works but not underneath the columns which is what I need. Thank you.

=COUNTIF(INDEX(G:G,MAX(MATCH("z", G:G)-9,1)):INDEX(G:G, MATCH("z", G:G)), "Yes")

 

[Joe4]

Apologies that is what I use at home. This is for another area.

I will take my telling off and try to remember next time.

I am not sure why you thought it was a telling off - that certainly wasn't the intention.
As a Moderator, I am simply explaining what that field is for, how people use it, and how to ensure that you can get answers that work for you in a timely and efficient manner.
We know it can be frustrating to both you and the responder when they post an answer that doesn't work for you because they thought you were using a different version.

 

[Weefergie56]

I do get that but it is an easy oversight when you work across different locations. Not all areas use the same. Maybe a requirement of posting should include your version to avoid this issue? I had no recollection of having to choose this when signing up and i realise not all functions are availableacross differentversions. You came across quite curt. I do understand it is frustrating to solve something but not for a silly reason and I appreciate the help I have been given so far.

 

[Cubist]

Unfortunately, I don't have version 2016 to test. Hopefully, someone else can assist you.

Edit: This problem appears much harder in older versions and probably requires additional columns. I would consider using VBA. This would solve the issue of addtional rows as well.

 

[Weefergie56]

Unfortunately, I don't have version 2016 to test. Hopefully, someone else can assist you.

Thank you for the help.

 

[Joe4]

I do get that but it is an easy oversight when you work across different locations. Not all areas use the same. Maybe a requirement of posting should include your version to avoid this issue?

We have a hard time getting people to populate the Office Version. We actually just changed it so it is required on all new accounts recently, but that does affect existing accounts.
You are able to select multiple version options there. If you do that though, you then will usually need to specify which version you are looking an answer for in your post.

You came across quite curt.

I do have a reputation for being rather direct (I like to be clear), but please don't mistake that for annoyance or anger!
Emotion can be hard to convey on a message board!

 

[Cubist]

Try if this UDF works for you. Insert the code in the general VB module.

Function CountLastTenYes(columnRange As Range) As Long
    Dim dataArr() As Variant
    Dim filteredArr() As Variant
    Dim c As Long
    Dim i As Long
    Dim countNonBlank As Long

    dataArr = columnRange.Value
    ReDim filteredArr(1 To UBound(dataArr, 1), 1 To 1)
    c = 0
    countNonBlank = 0
    For i = 1 To UBound(dataArr, 1)
        If Not IsEmpty(dataArr(i, 1)) Then
            countNonBlank = countNonBlank + 1
            filteredArr(countNonBlank, 1) = dataArr(i, 1)
        End If
    Next i
    For i = countNonBlank To countNonBlank - 9 Step -1
        If i > 0 And filteredArr(i, 1) = "Yes" Then
            c = c + 1
        End If
    Next i
    CountLastTenYes = c
End Function

Call the function CountLastTenYes like you would with normal Excel functions.

Book3
	A	B
1		Correct
2		Yes
3		Yes
4		No
5		Yes
6		No
7		Yes
8
9		Yes
10		Yes
11		Yes
12		Yes
13		Yes
14		Yes
15	Count Last 10 Yes	8
Sheet2

Cell Formulas
Range	Formula
B15	B15	=CountLastTenYes(B1:B14)