+ Reply to Thread
Results 1 to 13 of 13

Multiple Criteria Formula

  1. #1
    Registered User
    Join Date
    12-08-2020
    Location
    Barnsley
    MS-Off Ver
    2019
    Posts
    7

    Multiple Criteria Formula

    I am trying to create a doc to calculate % formulas for data, I have managed the vast majority but am struggling with the RWM combined % and number of children,

    Basically I need to work out how many children in reading writing and maths combined were at working towards (so anyone BLW doesn't count) - manually looking at the data - there are 2 children that aren't WTS, EXS or GDS in columns F, H, and J so id expect the number to be 55 but its not!

    Im not sure if it cant be done because its multiple criteria and also more than 2 columns?

    Any help would be appreciated,

    Thank you

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Multiple Criteria Formula

    If I've understood;

    C18: =SUMPRODUCT((Data!F$2:F$58<>"BLW")*(Data!H$2:H$58<>"BLW")*(Data!I$2:I$58<>"BLW"))
    copied to D18
    would return 55 and 57 respectively.

    I don't think COUNTIFS will work for you, in this instance but, in general terms:

    =COUNTIFS(range1,"A",range2,"B")
    would return the count of instances where both tests are met, on the same row etc

    when you use an array constant for the criterion like

    =COUNTIFS(range1,{"A","B"})

    you are, in effect, performing 2 completely distinct calculations -- the count of A, and the count of B
    by default only the "A" result will be returned - an outer SUM(...) would be required to return the aggregate of both tests

    when used like

    =COUNTIFS(range1,{"A","B"},range2,{"X","Y"})

    you are still performing 2 calculations only now these are the combinations of: (1) A + X and (2) B + Y ... again, an outer SUM would be required to return both

    if, conversely, you split the delimiters of the two array constants, i.e. one column based, one row based {c/o , and ; }, you can get an OR

    =COUNTIFS(range1,{"A","B"},range2,{"X";"Y"})

    so you are now performing A + X, A + Y, B + X, B + Y, again the outer SUM would be required to aggregate them.

    to reiterate however, in your scenario the SUMPRODUCT route is more likely the route you want to go.

  3. #3
    Registered User
    Join Date
    12-08-2020
    Location
    Barnsley
    MS-Off Ver
    2019
    Posts
    7

    Re: Multiple Criteria Formula

    Thank you - its worked for the number of children BLW but I'm not sure how to then do it as a % too - also am stuck on the next level - i.e. national Standard which would be any children that were EXS and GDS in the same columns.....

    Vicky

  4. #4
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Multiple Criteria Formula

    so

    WTS+ {note I had an error in earlier example - Col I should have been J}
    C17: =SUMPRODUCT((Data!F$2:F$58<>"BLW")*(Data!H$2:H$58<>"BLW")*(Data!J$2:J$58<>"BLW"))
    copied to right
    will (still) return 55 and 57 respectively

    EXS+
    G17: =SUMPRODUCT(ISNUMBER(SEARCH(Data!F$2:F$58,{"EXSGDS"})*SEARCH(Data!H$2:H$58,{"EXSGDS"})*SEARCH(Data!J$2:J$58,{"EXSGDS"}))+0)
    copied to right
    will return 55 and 54 respectively

    GDS:
    K17: =SUMPRODUCT((Data!F$2:F$58="GDS")*(Data!H$2:H$58="GDS")*(Data!J$2:J$58="GDS"))
    copied to L17
    will return 22 and 21 respectively

    w.r.t % -- I would have assumed this is just the above results / count of all students.

  5. #5
    Registered User
    Join Date
    12-08-2020
    Location
    Barnsley
    MS-Off Ver
    2019
    Posts
    7

    Re: Multiple Criteria Formula

    Thank you - I did the % based on the result / cohort - think i was reading too deep into it!

    Ive managed to do for EXS and GDS too thank you,

    Can i throw demographics in next? So how many girls / boys / SEN or is that too complex?

    Thank you

  6. #6
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Multiple Criteria Formula

    so, with SUMPRODUCT the key is:

    ANDs are performed by multiplication
    ORs are performed by addition

    In the case of demographics you're simply adding a further test so, given above, it should be as straightforward as adding the test with * - i.e.

    C30:
    =SUMPRODUCT((Data!$C$2:$C$58="F")*(Data!F$2:F$58<>"BLW")*(Data!H$2:H$58<>"BLW")*(Data!J$2:J$58<>"BLW"))
    etc

  7. #7
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Multiple Criteria Formula

    edit: in hindsight, for the WTS + GDS calculations you could, in fact, use COUNTIFS if you prefer, e.g.

    C30:
    =COUNTIFS(Data!$C$2:$C$58,"F",Data!F$2:F$58,"<>BLW",Data!H$2:H$58,"<>"BLW",Data!J$2:J$58,"<>BLW")
    copied to right

    however, for the XDS search you're better off persisting with the SUMPRODUCT
    (in that instance you can't simply test for XDS and add the GDS result - as both the XDS-only and GDS-only results will exclude combinations of XDS + GDS which should "count")

    for consistency sake, given limited data set size, you may find it easier to persist with SUMPRODUCT for all of the calcs.

    apologies for confusion this may have caused - senior moment, as they say.

  8. #8
    Registered User
    Join Date
    12-08-2020
    Location
    Barnsley
    MS-Off Ver
    2019
    Posts
    7

    Re: Multiple Criteria Formula

    Thank you - the only other issue I have is that its using cells 2 - 58 there may be some instances where there is more data - i.e. a bigger cohort. I know its simple to change the value to the length of the data dropped in but the issue I have is its teachers who are dropping the data in and it may be too complex for them to know to do that!

  9. #9
    Registered User
    Join Date
    12-08-2020
    Location
    Barnsley
    MS-Off Ver
    2019
    Posts
    7

    Re: Multiple Criteria Formula

    Used "" and that seems to have worked!

  10. #10
    Registered User
    Join Date
    12-08-2020
    Location
    Barnsley
    MS-Off Ver
    2019
    Posts
    7

    Re: Multiple Criteria Formula

    The GDS / EXS doesnt seem to work? As i extended the parameters to $98$ incase of bigger cohorts its bringing back 96 rather than 55.

    =SUMPRODUCT(ISNUMBER(SEARCH(Data!$F$2:$F$98,{"EXSGDS"})*SEARCH(Data!$H$2:$H$98,{"EXSGDS"})*SEARCH(Data!$J$2:$J$98,{"EXSGDS"})*(Data!$F$2:$F$98<>"")*(Data!$H$2:$H$98<>"")*(Data!$J$2:$J$98<>""))+0)
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  11. #11
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Multiple Criteria Formula

    Hi, so, given the outer test is an IsNumber test you need to ensure the test generates a non-numeric wherever one or more condition fails

    To better illustrate

    =ISNUMBER(SEARCH("","apple")*(""<>"")) --> True
    this is because 1*FALSE -> 1*0 -> 0

    however, if you choose to divide the SEARCH by the Blank test...

    =ISNUMBER(SEARCH("","apple")/(""<>"")) --> False
    this is because 1/FALSE -> 1/0 -> #DIV/0!

    so, converting your formula using the above logic -- for ex.:

    G17:
    =SUMPRODUCT(ISNUMBER(SEARCH(Data!F$2:F$98,"EXSGDS")/(Data!F$2:F$98<>"")*SEARCH(Data!H$2:H$98,"EXSGDS")/(Data!H$2:H$98<>"")*SEARCH(Data!J$2:J$98,"EXSGDS")/(Data!J$2:J$98<>""))+0)
    note: I removed the constant array as superfluous (hangover from earlier testing)

    Hope that helps

  12. #12
    Registered User
    Join Date
    12-08-2020
    Location
    Barnsley
    MS-Off Ver
    2019
    Posts
    7

    Re: Multiple Criteria Formula

    Thank you - youre a star! Hopefully one last issue- tried to do it for demographics (gender) and getting a #value!

    Vicky

    =SUMPRODUCT(Data!$C$2:$C$98,"F")*(ISNUMBER(SEARCH(Data!F$2:F$98,"EXSGDS")/(Data!F$2:F$98<>"")*SEARCH(Data!H$2:H$98,"EXSGDS")/(Data!H$2:H$98<>"")*SEARCH(Data!J$2:J$98,"EXSGDS")/(Data!J$2:J$98<>""))+0)

  13. #13
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Multiple Criteria Formula

    Hi, almost - your opening parenthesis is misplaced (you have in front of ISNUMBER whereas should precede the "F" test:

    =SUMPRODUCT((Data!$C$2:$C$98="F")*ISNUMBER(SEARCH(Data!F$2:F$98,"EXSGDS")/(Data!F$2:F$98<>"")*SEARCH(Data!H$2:H$98,"EXSGDS")/(Data!H$2:H$98<>"")*SEARCH(Data!J$2:J$98,"EXSGDS")/(Data!J$2:J$98<>""))+0)

+ 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. [SOLVED] Retrieving multiple text strings based on multiple criteria (formula update)
    By McKneezy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-24-2019, 08:52 PM
  2. Multiple criteria formula with date as criteria
    By Phlegon_of_Tralles in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 03-06-2019, 09:27 PM
  3. Replies: 4
    Last Post: 04-14-2017, 05:14 PM
  4. Formula to Sum 3 Column Values based on Multiple Criteria for Multiple Rows
    By RMerckling in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-19-2016, 07:46 PM
  5. Formula for summing on multiple row criteria and a dynamic column criteria
    By ianswilson815 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-25-2016, 01:58 PM
  6. Replies: 12
    Last Post: 02-10-2014, 11:59 AM
  7. Replies: 1
    Last Post: 05-16-2011, 05:00 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