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:
Please Login or Register to view this content.
Quang PT
Very close. The fomula doesnt take into account the "Driver" that is under the Department column. That number needs to be subtracted.
Thanks.
Add 1 more statement:
Please Login or Register to view this content.
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.
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.
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.
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)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks