+ Reply to Thread
Results 1 to 4 of 4

multiple "if" functions depending on previous functions

  1. #1
    Registered User
    Join Date
    08-18-2010
    Location
    Worcester
    MS-Off Ver
    Excel 2007
    Posts
    11

    Smile multiple "if" functions depending on previous functions

    Hi all, I'm struggling to get this formulae to work right! I'm a teacher and want to develop a grid to track marks for assessments and work out points.

    If a candidate gets all the Ps ( 9 in total for unit 2) They get 12 points, if they get the 7 Ms they get another 6 points making 18, if they get all 5 Ds they get another 6 points to make 24. I have that bit sorted, the tricky part is if an M is missing, they are limited to 12 points (even if they have all the other Ms and Ds) and if any P is missing, they get 0.

    Any help would be much appreciated!! See attached spreadsheet.

    I have used the countif function to count the Ps, Ms and Ds in the cells as a helper column to the right.

    Attached Files Attached Files

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: multiple "if" functions depending on previous functions

    You can get rid of the helper columns and use

    =IF(COUNTIF(D4:X4,"p")=9,12+IF(COUNTIF(D4:X4,"M")=7,6+IF(COUNTIF(D4:X4,"D")=5,6,0),0),0)
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: multiple "if" functions depending on previous functions

    Does this work?

    =IF(COUNTIF(D4:X4,"p")<>9,0,12+IF(COUNTIF(D4:X4,"m")<>7,0,6+IF(COUNTIF(D4:X4,"d")=5,6,0)))
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  4. #4
    Registered User
    Join Date
    08-18-2010
    Location
    Worcester
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: multiple "if" functions depending on previous functions

    Thank-you that is so helpful, exactly what I wanted, thank-you so much!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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