# Help with SUMIFS formula with exceptions

1. ## 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.

Try:

3. ## 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.

5. ## 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. ## 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"))

K2
=SUM(SUMIFS(OFFSET(D:D,,{0,1}),B:B,"<>Driver",F:F,"<>Vacation",C:C,J2))

7. ## 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))

8. ## 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.

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