Time Card SUMIFS Conditions

[gplans]

Hi There,
I am trying to make a time card that has the following conditions.

1. The regular shift hours will not add up if the labour code is forgotten to be entered. (Done, but might have been done with better code)
2. Overtime and double hours are with the same criteria but are split. 4 hours will be over time, and any hours after that would be double time. (I have that working, except it still adds up if the labour code is missing, like regular hours.

I have attached the file for your suggestions.
Thanks

 

[vletm]

gplans​

You could get replies if You would explain ... where is Your challenge?

 

[gplans]

Well, I can't do it or figure it out. No one else has replied with an answer, so I guess it has proven to be a challenge.

 

[vletm]

...hmm?
if even You don't know - where do You need other kind of formula?
... then this is a challenge.

 

[gplans]

So what I need is a formula that will do the same as this:

=SUMPRODUCT(SUMIFS(C6:AH6,C4:AH4,{"Service";"N206";"N207";"N223";"N224";"N225";"N226";"N227";"N228";"N229";201;206;207;223;224;225;226;227;228;229;394;6403;2150}))

But I require the formula in the Overtime & Double-Time area to have more conditions added to it.

If the time added is over 4 hours it will put 4 hours in overtime but no more.
=IF(SUM(C16:V16)>4,4,SUM(C16:V16))

If the time is 5 hours + then 4 hours goes in overtime and the remaining hours goes in double time.
=IF(SUM(C16:V16)>4,SUM(C16:V16)-4,0)

The two formulas do this, but they do not have the condition like the first one, where the labour code is required before adding up.

I am sick and tired of getting time cards where the employees are forgetting to put their labour codes and I have to guess or contact them again and again.

 

[vletm]

Do You mean cell AI5?
=SUMIFS(C5:AH5,C4:AH4,"<>Labour Codes:") and paste formula down

 

[gplans]

That is definitely cleaner than what I had there before thanks

I was also referring to W15 and the formula in Y16 with the conditions of If the time added is over 4 hours it will put 4 hours in overtime but no more If the time is 5 hours + then 4 hours goes in overtime and the remaining hours goes in double time.

 

[vletm]

# I tested with row 4 & 15 data validations
... those comes from List-sheet
Could You try to do some modifications ....
Your formulas are upside-down...
a) there are some parameters which cannot be true.
b) try to sum values which can sum
rows 12,13, 23,24 ... You can have a list or lists which can sum.
I tested with row 12 ... with row 3
After You've modified those ... the rest could be possible.
> Are ALL of those You values correct?
if not then someway You should able to show ... what are correct values?

 

[gplans]

Maybe I am not understanding what you mean above or that I have not been clear.

The two formulas that are needed to be adjusted are the overtime and double time totals on the right. I need the ability to not put a total in if the employee forgets to put a labour code in. Just like the regular time total shift hours, but I need to have the ability to have overtime only add up to 4h and the remainder hours in the double time column.

 

[vletm]

Possible that .. both.
# Where?
I hope that now I finally got image - where are You challenges?
# Data validations
You'd there unique lists per needed cell - heavy to update > two list/sets in List-sheet
... do there need to be two different lists/sets?
# upside-down
You'd tried to find out which no need to calculate ... other parts seems to be more clear (eg Shift Hours)
# You skipped to verify that are those Your values/results correct.
There need to test - do this works better now?
# I modified almost all formulas in Week1-sheet - there are still formulas, which should able to write other way.
Week2-sheet is original.

If something will give unwanted result then You should write #which cell? #correct/verified result #how did You got it?

 

[gplans]

Thank you! This is what I was looking for help with. I knew there were better ways to write the formulas.

Thanks Again