+ Reply to Thread
Results 1 to 9 of 9

Sum if based on two criteria

  1. #1
    Registered User
    Join Date
    08-06-2012
    Location
    Quincy,IL
    MS-Off Ver
    Excel 2003
    Posts
    84

    Sum if based on two criteria

    HI i have a ver simple question but for some reason can not figure it out!

    Crazy I know!

    Ok so heres the scoop,

    I need a sum ifs frormula to sum the cells Master(2) d3:V3 IF the dates Master(2) B:B matches Sheet6 B2 and if Master(2) C:C matces Sheet6 A3.

    Ok i thought that was simple so i came up with this fomula.

    =SUMIFS('Master (2)'!D3:V3,'Master (2)'!B:B,B2,'Master (4)'!D:D,A3)

    Doesnt work. I get #VALUE!

    So I'm pretty much at a loss for what to do.

    *I really have 12 weeks of infomation but did not post it to keep my file size down*Book1111.xlsx

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Sum if based on two criteria

    The way you are swapping ranges between horizontal and vertical is not going to work plus the ranges need to be equal in size.

    Here is how I would do it (or at least this is what comes to mind). On the Master (2) tab added a helper column in AC. On Sheet6 used an Index/Match CSE function.
    Attached Files Attached Files
    HTH
    Regards, Jeff

  3. #3
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Sum if based on two criteria

    It would be more helpful if you described what you want to do based on cell contents rather than cell references.
    Example: I want to sum the time categories on the Master sheet where the driver matches the driver on Sheet6 and the date on the Master sheet matches the date on Sheet6.

    Meanwhile, I'll see if I can figure out what you want based on the workbook you posted.
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Registered User
    Join Date
    08-06-2012
    Location
    Quincy,IL
    MS-Off Ver
    Excel 2003
    Posts
    84

    Re: Sum if based on two criteria

    Thanks so much guys! I never know how to talk about cells without confusing people.

  5. #5
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Sum if based on two criteria

    Ok still trying to get these forumula straight in my noggin', but here is one without a helper column...

    =SUMPRODUCT(($A3='Master (2)'!$C$3:$C$280)*(B$2='Master (2)'!$B$3:$B$280)*('Master (2)'!$D$3:$V$280))

  6. #6
    Registered User
    Join Date
    08-06-2012
    Location
    Quincy,IL
    MS-Off Ver
    Excel 2003
    Posts
    84

    Re: Sum if based on two criteria

    Jeff, thanks for the formula however it doesnt seem to be workingi keep getting #value! when i use this formula

    =SUMPRODUCT(($A3='Master (2)'!$C$3:$C$280)*(B$2='Master (2)'!$B$3:$B$280)*('Master (2)'!$D$3:$V$280))

  7. #7
    Registered User
    Join Date
    08-06-2012
    Location
    Quincy,IL
    MS-Off Ver
    Excel 2003
    Posts
    84

    Re: Sum if based on two criteria

    Jeff, I tried the one that you put in the work book and it works wonderfully!

    Thank you, however like i said before i have 12 weeks of data.....i only have 2 shown to keep file size down, when i change the size of the colums the formula no longer works. I need it to cover from 3-2000 at least........

  8. #8
    Registered User
    Join Date
    08-06-2012
    Location
    Quincy,IL
    MS-Off Ver
    Excel 2003
    Posts
    84

    Re: Sum if based on two criteria

    my blonde hair is taking over i didnt realize it was a array forumla i gues jeepers

    thanks so much

  9. #9
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Sum if based on two criteria

    I'm glad your "blonde" hair finally kicked in. Thanks for the feedback and you are welcome. Happy Thanksgiving!!!

    ========================================================================================

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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