How to use the SUMIFS function so it calculates the right values

Hi!

I have a worksheet where I type in hours for the ppl working in the factory.
At the end of the moth I have to know (for each person) how many hours has he worked (regular hours) how much overtime he has and how much vacation he used.
The sheet that I have looks like this sample just has all the months in it this one just has January.

The "dop." column counts the number of "D" in the row <---- This works fine it just counts how many days the person is missing.
The "na.u." column is the column that should count overtime for the whole month.
Overtime counts as any hour that he worked on the weekend (weekends are painted gray), any hour in during the week that is above 8 (so if it says 12 that 4 hours of overtime)
The "ure" column should count every work day (Monday-Friday) up to 8hrs. (So if it says 12 then thats 8 regular hours and 4hours over time).

I hope some one understands what I'm tryn to do here
Is it possible to do this with a functions and formulas?

If not then I'll write a macro, but it's not realy for me it's for my co-worker but he and computres (two different pair of shoes )

can u provide this sheet in english?
so that i can understand your inside matter and can help u?

Regards
CA Mahaveer Somani

Yeh benishiryo!

That works Great.
The only problem is that the sheet in my workbook looks a bit different.
Every next month is to the right.

I tryed somehow copying it to the right or using the past special but it kind of doesn't work
Do I have to copy the formulas in some other way to other months or how do I copy it?
Here is the original file. Or how it should look like

Yeh benishiryo!

That works Great.
The only problem is that the sheet in my workbook looks a bit different.
Every next month is to the right.

I tryed somehow copying it to the right or using the past special but it kind of doesn't work
Do I have to copy the formulas in some other way to other months or how do I copy it?
Here is the original file. Or how it should look like

Is it also possible to color hollidays as gray?
I mean so if some one works on a holliday that should also be count as over time.
But if I gray the hollidays manualy it won't count the hours as over time it will count it as regular hours right?

Sory about the 2 posts. But some error accured in the Data here in the forum (don't know what that was about) and when I looked this happend

Ok!
I'm going to try this out.
I'll get back to you in 2hrs.

Nope.
The normal work hours up to 8hrs works fine.
The "D" for workers free day works fine.
The overtime works fine if I type 12hrs on a work day. <---Thats good.
But if I type 4hrs on a holiday or saturday or sunday (gray areas) then it doesn't count it as overtime or normal work time.
It should count it as ovetime because this is not taken as normal work time.

This is from my first post:

The "na.u." column is the column that should count overtime for the whole month.
Overtime counts as any hour that he worked on the weekend (weekends are painted gray), any hour in during the week that is above 8 (so if it says 12 that 4 hours of overtime)

But to make it simpler any hour that is typed in the gray area should be counted in the overtime column of the month (na.u. - column).

I've added a few holidays to the list and changed the month names so I'm returning this workbook back to you, hoping that you'll help me with this this too.
If you don't I'm lost
I was never good with excel forumlas hehe. But you've done an amasing job!

Under december I've inserted some work hours in normal field and some overtime during the week and some during the weekend (gray area)
The outcome should be ure: 16 na.u: 8
17th Dec. get's count under ure (normal work time)
19th Dec. get's count 8hrs under ure (normal work time) and 4hrs under na.u (overtime)
21st Dec. get's count under na.u 4hrs - Because it's a Saturday and that is not a normal work day.

Or maby I did something wrong when I copyed the months?

Hello again!
I'll do that and get back to you with an answer hehe

Well I guess this is it
I think it all works now.

You rocked the s**t out of this workbook
Many thanks for your help!

I don't know what else to say