+ Reply to Thread
Results 1 to 8 of 8

Help with SUMIFS formula with exceptions

  1. #1
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    2019
    Posts
    115

    Help with SUMIFS formula with exceptions

    Hi,

    I have a spreadsheet that I need help with. I need to calculate the total amount of hours with a few exceptions.
    The formula needs to calculate the following: Hours + Over Time - Notes (any cell that's not blank) - "Driver" (under department)

    I have attached a spreadsheet with the highlighted field where the formula should go and the correct answer next to it.

    Thanks in advance for the help.
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,006

    Re: Help with SUMIFS formula with exceptions

    Try:
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    2019
    Posts
    115

    Re: Help with SUMIFS formula with exceptions

    Very close. The fomula doesnt take into account the "Driver" that is under the Department column. That number needs to be subtracted.

    Thanks.

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,006

    Re: Help with SUMIFS formula with exceptions

    Add 1 more statement:

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    2019
    Posts
    115

    Re: Help with SUMIFS formula with exceptions

    Is it possible to make this formula work in this form: =SUMPRODUCT((C:C=J2)*(B:B<>"Driver")*(F:F="")*D:E)
    The table grows daily with new entries.

  6. #6
    Valued Forum Contributor
    Join Date
    08-15-2020
    Location
    malaysia
    MS-Off Ver
    office2007
    Posts
    518

    Re: Help with SUMIFS formula with exceptions

    K2
    =SUMPRODUCT(($C$2:$C$19=J2)*(($D$2:$D$19)+($E$2:$E$19))*($F$2:$F$19<>"Vacation")*($B$2:$B$19<>"Driver"))

    Your Office Version 2019
    K2
    =SUM(SUMIFS(OFFSET(D:D,,{0,1}),B:B,"<>Driver",F:F,"<>Vacation",C:C,J2))
    Last edited by wk9128; 08-20-2020 at 11:11 PM.

  7. #7
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    2019
    Posts
    115

    Re: Help with SUMIFS formula with exceptions

    I had to make a small modification to the formula, and now it works perfectly. Thank you very much for your help!
    Final formula: =SUM(SUMIFS(OFFSET(D:D,,{0,1}),B:B,"<>Driver",F:F,"",C:C,J2))
    Rep Added and thread maked as solved.

  8. #8
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,006

    Re: Help with SUMIFS formula with exceptions

    Strongly advice to use my #4, with range extended to possible maximum, i.e, $C2:$C19 to be $C2:$C10000

    SUMIFS whole range (>1 milion rows) and OFFSET (Volatile formula, moving each cell in column D rightward 0 and 1 column) will kill your CPU.

+ 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. Formula Exceptions
    By mottis9 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-28-2018, 02:20 AM
  2. Replies: 1
    Last Post: 10-09-2017, 05:06 PM
  3. [SOLVED] Lookup Formula To Check Exceptions
    By Camel in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-09-2017, 06:51 AM
  4. Formula: % for each month and Number count for Exceptions
    By mydragonstalents in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-19-2013, 05:58 PM
  5. Exceptions to a formula
    By ButtersEFC in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-28-2013, 07:20 AM
  6. [SOLVED] Sumifs, problem with the formula: =sumifs(c10:c200,<=today(),0)
    By Faustocruz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-08-2012, 04:26 AM
  7. Need sumif formula with exceptions
    By Kfain in forum Excel - New Users/Basics
    Replies: 13
    Last Post: 05-31-2012, 02:25 PM

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