+ Reply to Thread
Results 1 to 12 of 12

Adding Multiple 'IF' together to Give a Total

  1. #1
    Forum Contributor
    Join Date
    12-13-2011
    Location
    Yorkshire, UK
    MS-Off Ver
    Office365
    Posts
    183

    Adding Multiple 'IF' together to Give a Total

    I'm trying to use multiple IF statements from a series of conditions in a sheet to give an overall total but I cant get the formula (below) to work. I suspect its the way I'm trying to link the elements together as each part of the formula before and after the '+' work if used on their own. Or, the way I'm trying to get it to work out the conditions in the cells before adding them together (in this case cells D8 and M8).


    =SUM(IF(AND(D8>0.31,D8<=0.5),25),IF(AND(D8>0.5,D8<=0.75),50),IF(AND(D8>0.75),100)+(IF(AND(M8>0,M8<=10),25),IF(AND(M8>10,D8<=20),50),IF(AND(M8>20),100)))

    Can anyone assist?

    In the attachment, I'm trying to generate a total score in Cell B8 of the Extended Dash based on the conditions in D8 and M8
    Attached Files Attached Files
    Last edited by JasonMcQueen; 01-25-2019 at 04:54 AM.

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Adding Multiple 'IF' together to Give a Total

    Maybe:

    =SUM(IF(AND(D8>0.31,D8<=0.5),25,IF(AND(D8>0.5,D8<=0.75),50,IF(AND(D8>0.75),100))),(IF(AND(M8>0,M8<=10),25,IF(AND(M8>10,M8<=20),50,IF(AND(M8>20),100)))))

    If not, what is the result desired in B8?
    Click the * to say thanks.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Adding Multiple 'IF' together to Give a Total

    1st, your IF statements are missing the 3rd argument...
    eg
    IF(AND(D8>0.31,D8<=0.5),25,then_what?)

    2nd, have you considered using SUMIFS() instead (although Im not sure that would work in this case)?
    3rd, you could restructure the whole formula to something like this...
    =sum(if(D8>0.75),100,if(D8>0.5,50,if(D8>0.31,25,0))).........
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Contributor
    Join Date
    12-13-2011
    Location
    Yorkshire, UK
    MS-Off Ver
    Office365
    Posts
    183

    Re: Adding Multiple 'IF' together to Give a Total

    Thanks Guys, PaulM100 formula works. Basically, what I was trying to do was take the value in a number of the cells to give an overall score depending on their performance. It was the way to keep linking the IF statements that was throwing me.

    FDibbins, I did think of restructuring the formula as you say but for some reason that I cant recall right now (it was late last night). I'll give it another thought.

    Thanks again.

  5. #5
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: Adding Multiple 'IF' together to Give a Total

    Try this one you dont need al the and statements or the sum around it

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

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Adding Multiple 'IF' together to Give a Total

    Joel, pretty much what I suggested, and you are right, the SUM is not really needed to add the 2 statements together

  7. #7
    Forum Contributor
    Join Date
    12-13-2011
    Location
    Yorkshire, UK
    MS-Off Ver
    Office365
    Posts
    183

    Re: Adding Multiple 'IF' together to Give a Total

    That works but I've encountered a new problem (didnt want to start a new thread).

    I've added a couple more additions to the formula to include columns Q and R :

    IF(D9>0.75,100,IF(D9>0.5,75,IF(D9>0.31,25,0)))+IF(M9>20,100,IF(M9>10,50,IF(M9>0,25,0)))+IF(N9>20,100,IF(N9>10,50,IF(N9>0,25,0)))+IF(Q9>=0,50,IF(Q9>75,25,IF(Q9>90,0,0)))+IF(R9>=0,50,IR(R9>75,25,IF(R9>90,0,0)))

    It seems to be working fine for row 9 but when it comes to row 10 its struggling to deal with the 100% values

    what am i doing wrong?
    Attached Files Attached Files

  8. #8
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: Adding Multiple 'IF' together to Give a Total

    Dont blame the formula, its workinig fine..
    It is you that is struggling with how to it up correctly
    the formula has some implied logic I did not explain

    You have to start with the highest value and work your way down. Right now you test >=0 first and that is always true so score is always 50 now for that column you need to revers the testing of values to be able to trigger the false part of the IF-function and do the next test.
    also 75 is not the same as 75% which you mean to test I believe

    SO for the Q column it should be
    Please Login or Register  to view this content.
    now you should be able to build the R column formula yourself I think?

    Edit: I used decimal values like 0.9 for the percentages but instead you could also use 90% that might make the formula a little easier to understand for a "human reader" of the formula
    Last edited by Roel Jongman; 01-25-2019 at 07:24 AM. Reason: added comment for alternative

  9. #9
    Forum Contributor
    Join Date
    12-13-2011
    Location
    Yorkshire, UK
    MS-Off Ver
    Office365
    Posts
    183

    Re: Adding Multiple 'IF' together to Give a Total

    Ah right, didnt know that. I did say I'm doing it wrong rather than the formula

    Yep, I'm okay to carry on with the others.

    Thanks

  10. #10
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: Adding Multiple 'IF' together to Give a Total

    lol, sorry, sometimes my Internal-translatormodule for English to Dutch is as "buggy" as the Google's translate tool

  11. #11
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Adding Multiple 'IF' together to Give a Total

    Does this right?

    =LOOKUP(D8,{-1,0.31,0.5,0.75}+10^-15,{0,25,75,100})+SUMPRODUCT(LOOKUP(M8:N8,{-1,0,10,20}+10^-15,{0,25,50,100}))+SUMPRODUCT(LOOKUP(Q8:R8,{-1,0.75,0.9}+10^-15,{50,25,0}))

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Adding Multiple 'IF' together to Give a Total

    Jason, happy to help and thanks for the feedback

+ 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. Adding up city names to give a total number.
    By Alexandre23 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-05-2018, 08:37 AM
  2. [SOLVED] Subtract dates to give a total amount of days - Multiple Data
    By t_man000 in forum Excel General
    Replies: 2
    Last Post: 07-23-2015, 11:14 PM
  3. HELP adding numbers in HH:MM format to give a general total?
    By BEN HM in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-15-2014, 08:31 AM
  4. Adding multiple numbers in one cell to give a total in another
    By Lunezilla in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-11-2013, 06:12 AM
  5. Replies: 1
    Last Post: 01-30-2013, 12:12 PM
  6. Looping and adding to give one total in one cell.
    By kiwiingrid in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-09-2012, 02:49 PM
  7. Adding Data Using Multiple Worksheets to Total into a Grand Total
    By Lillie in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-19-2005, 04:06 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