# 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.  Register To Reply

2. ## Re: Help with SUMIFS formula with exceptions

Try: ``Please Login or Register  to view this content.``  Register To Reply

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.  Register To Reply

4. ## Re: Help with SUMIFS formula with exceptions ``Please Login or Register  to view this content.``  Register To Reply

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.  Register To Reply

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))  Register To Reply

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))  Register To Reply

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.  Register To Reply

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