+ Reply to Thread
Results 1 to 14 of 14

Sum and Countif Formulae with curly brackets to find a percentage

  1. #1
    Registered User
    Join Date
    04-04-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    8

    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. #2
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

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

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

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    DBY

  3. #3
    Registered User
    Join Date
    07-13-2015
    Location
    NC
    MS-Off Ver
    Office 2010
    Posts
    6

    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. #4
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    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.
    If you start your own new thread you'll most likely get replies to your question.

    DBY

  5. #5
    Registered User
    Join Date
    04-04-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    8

    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. #6
    Registered User
    Join Date
    07-13-2015
    Location
    NC
    MS-Off Ver
    Office 2010
    Posts
    6

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

    Thank you! New to this so that helps!

  7. #7
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    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")
    Last edited by Jonmo1; 07-13-2015 at 03:34 PM.

  8. #8
    Registered User
    Join Date
    04-04-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    8

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

    Thank you very much. That has done it.

  9. #9
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

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

    You're welcome.

  10. #10
    Registered User
    Join Date
    04-04-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    8

    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. #11
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    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. #12
    Registered User
    Join Date
    04-04-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    8

    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. #13
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

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

    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.

  14. #14
    Registered User
    Join Date
    04-04-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    8

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Find first row with text in brackets
    By cmb80 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-12-2014, 01:47 PM
  2. [SOLVED] SUM(COUNTIF.. with more than one pair of curly brackets ?
    By Gabor in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-30-2013, 03:46 PM
  3. what is the meaning of curly brackets in the functions { }
    By funtastic in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-04-2013, 04:23 PM
  4. What do curly brackets /braces {} do in VBA
    By Huugin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-17-2012, 04:33 PM
  5. [SOLVED] Excluding Single Member from CUBEVALUE using Curly Brackets
    By tlafferty in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-21-2012, 05:58 PM
  6. [SOLVED] Curly Brackets in formula view
    By VBSK8R in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-04-2012, 09:32 AM
  7. [SOLVED] Formulae - how many more to reach a certain percentage?
    By smith_ts in forum Excel General
    Replies: 3
    Last Post: 01-19-2010, 02:13 PM

Bookmarks

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