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")

 

[Cubist]

Without seeing your data, I'd have to take a guess based on your description. Try

=SUM(--(TAKE(TOCOL(G:G,1),-10)="Yes"))

 

[Weefergie56]

Thanks, it's giving me a name error. I will try later ro add a mini sheet for data example.

 

[Weefergie56]

Hi - I have attached a screenshot. Hopefully you can help if you are still interested. Thank you!

 

[Cubist]

Have you tried my suggested formula?

Book2
	G
1	Yes
2
3
4
5
6
7	Yes
8	Yes
9	No
10	No
11	Yes
12	Yes
13	Yes
14	Yes
15	No
16	7
Sheet3

Cell Formulas
Range	Formula
G16	G16	=SUM(--(TAKE(TOCOL(G1:G15,1),-10)="Yes"))

 

[Weefergie56]

Have you tried my suggested formula?

Book2
	G
1	Yes
2
3
4
5
6
7	Yes
8	Yes
9	No
10	No
11	Yes
12	Yes
13	Yes
14	Yes
15	No
16	7
Sheet3

Cell Formulas
Range	Formula
G16	G16	=SUM(--(TAKE(TOCOL(G1:G15,1),-10)="Yes"))

Yes but I get a name error. The column has a heading name. Say it is called "Correct" so I adjusted the formula to start from G2. Another issue might be that I am likely to add more rows so the range would change...

 

[Cubist]

Yes but I get a name error. The column has a heading name. Say it is called "Correct" so I adjusted the formula to start from G2. Another issue might be that I am likely to add more rows so the range would change...

1) The #NAME error could result from either misspelling the function names or you're not using XL 365.
2) Depending on you're adding more rows, the formula can auto-adjust the range.

 

[Weefergie56]

Yes I am not using 365. It is 2016.

 

[Joe4]

Yes I am not using 365. It is 2016.

Then you should update your profile to reflect that:

If you typically use 365, but just using 2016 for this question, then it is fine to leave your Profile as-is, but be sure to mention in this question what version you are using.
As you can see, certain solutions work in some versions and not others, which is why it is important for responders to know exactly which version you are using for this.

 

[Weefergie56]

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.