Sum and Countif Formulae with curly brackets to find a percentage

I am trying to find a percentage of a particular subgroup of pupils who have attained a 4, 5 or 6 on a each of three tests.

The formula I am using is as follows, where H, M and R are the columns containing the test results and C is the column identifying the pupils' 'attendance' in that particular subgroup:

=SUM(COUNTIFS($H$2:$H$33,{"4?","5?","6"},$M$2:$M$33,{"4?","5?","6"},$R$2:$R$33,{"4?","5?","6"},$C$2:$C$33,"S"))/SUM(COUNTIF($C$2:$C$33,"S"))

The problem I'm having is that the way this formula is written, it will only consider pupils who have attained the same score on each test, i.e., either 444, 555 or 666. I want it to consider pupils who have attained any combination of a 4, 5 or 6, i.e., 444, 445, 446, etc.

Could anyone help me tweak this formula, please? I know it's not particularly elegant and am open to other suggestions!

Thank you!

Hello
Try the following, does this give the required result?

Formula:

Please Login or Register  to view this content.

DBY

Hello! I am not sure if I am posting in the right place. I am trying to find a formula to count like columns. For example, Column A has the Number "1" that represents 'race" and Column "B" has another number that represents "gender". I want the formula to tell me count the number of AA males or white females. How would I do this?

Hello kndavis2
Welcome to the forum. Unfortunately it's against forum rule #2 to post in another member's thread:

2. Don't post a question in the thread of another member -- start your own.

If you start your own new thread you'll most likely get replies to your question.

DBY

Thanks. I amended it to this: =SUMPRODUCT(($C$2:$C$33="S")*(($H$2:$H$33={"4?","5?","6?"})+($M$2:$M$33={"4?","5?","6?"})+($R$2:$R$33={"4?","5?","6?"})))/COUNTIF($C$2:$C$33,"S")

I added a ? after each level because there is a letter following the number 4 or 5 or 6, but when I didn't put " " around it, it returned an error.

Unfortunately, both with your original and my amended version, the formula returns 0.

Thank you for trying!
Deborah

Thank you! New to this so that helps!

Thank you very much. That has done it.

You're welcome.

Actually, if I may...there is a part 2 to this.

The test results are actually 4C or 4B or 4A (among others) and at one point, I need to calculate the percentage of pupils who scored 4B, 4A, 5C, 5B, 5A or 6 in each of the three tests. I tried to amend the helper column to read =AND(OR(LEFT(H2,1)={"4B","4A","5","6"}),OR(LEFT(M2,1)={"4B","4A","5","6"}),OR(LEFT(R2,1)={"4B","4A","5","6"})), but it returned incorrect FALSE values.

Do you have any ideas?

Thank you again.
Deborah

In that case, you don't need the LEFT functions if you're going to specify the whole string 4B

Just remove the LEFT functions from each OR
Change
OR(LEFT(H2,1)={"4B","4A","5","6"})
to
OR(H2={"4B","4A","5","6"}

Thank you again. I appreciate you taking the time to help. I've learnt a few new things from you today, which is always useful!

Glad to help.

I'll just throw this out there too, the reason your original attempt failed.
=SUM(COUNTIFS($H$2:$H$33,{"4?","5?","6"},$M$2:$M$33,{"4?","5?","6"},$R$2:$R$33,{"4?","5?","6"},$C$2:$C$33,"S"))/SUM(COUNTIF($C$2:$C$33,"S"))

When you use the syntax SUM(COUNTIFS(Range1,{criteria1,criteria2}))
That can only work for 1 or 2 'sets' of or criteria.
And if you do 2 'sets' of criteria, they have to be transposed of each other (use , for one set, and ; for the other)
SUM(COUNTIFS(Range1,{criteria1,criteria2},Range2,{criteria3;criteria4}))

I am unaware of any way to apply this sum(countifs syntax to more than 2 'sets' of criteria.
Hence the helper column to do the OR for you.

Wow. Thank you. That will take me a little bit of time to get my head around!