# Sum and Countif Formulae with curly brackets to find a percentage

1. ## 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!

2. ## Re: Sum and Countif Formulae with curly brackets to find a percentage

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

Formula:
`Please Login or Register  to view this content.`

DBY

3. ## Re: Sum and Countif Formulae with curly brackets to find a percentage

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?

4. ## Re: Sum and Countif Formulae with curly brackets to find a percentage

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.

DBY

5. ## Re: Sum and Countif Formulae with curly brackets to find a percentage

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

6. ## Re: Sum and Countif Formulae with curly brackets to find a percentage

Thank you! New to this so that helps!

7. ## Re: Sum and Countif Formulae with curly brackets to find a percentage

I would suggest a helper column, in say column S
In S2 and filled down put

=AND(OR(LEFT(H2,1)={"4","5","6"}),OR(LEFT(M2,1)={"4","5","6"}),OR(LEFT(R2,1)={"4","5","6"}))

Then use
=COUNTIFS(\$S\$2:\$S\$33,TRUE,\$C\$2:\$C\$33,"S")/COUNTIF(\$C\$2:\$C\$33,"S")

8. ## Re: Sum and Countif Formulae with curly brackets to find a percentage

Thank you very much. That has done it.

9. ## Re: Sum and Countif Formulae with curly brackets to find a percentage

You're welcome.

10. ## Re: Sum and Countif Formulae with curly brackets to find a percentage

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

11. ## Re: Sum and Countif Formulae with curly brackets to find a percentage

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

12. ## Re: Sum and Countif Formulae with curly brackets to find a percentage

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!

13. ## Re: Sum and Countif Formulae with curly brackets to find a percentage

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.

14. ## Re: Sum and Countif Formulae with curly brackets to find a percentage

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1