Sumif with 2 dimensions

ebroder

New Member
Joined
Apr 20, 2016
Messages
5
Following this thread, I have a similar problem but the solutions suggested here do not work for me. VBA is not an option as I can't afford the time to walk every one of my staff through this.

I have an input sheet for time. I need to limit the input to total hours worked, and have overtime and double time calculated each day based on *either-or* daily or weekly formulas:

Overtime = 1.5 * Hourly, charged at the greater of
  • 8-12 hours/day
  • 40-60/week
Double time, charged after the above thresholds.
ABEFGHIJK
4A
Staff
B
Position
E
Monday
F
Tuesday
G
Wednesday
H
Thursday
I
Friday
J
Saturday
K
Sunday
5JonesFrog9888
6SmithCat10101010101010
7AdamsDog13121212121212
8JonesChinchilla101010

<tbody>
</tbody>


When Jones switches to working as a chinchilla from a frog mid-week, he has accrued 33 hours: 1 hour OT and 32 hourly. While his position changes, he is still entitled to his weekly overtime, beginning on Friday.

This list is 100 employees long.

I need to create to create a database next to this that looks something like this:
M
Hourly MON
N
Hourly TUES...
T
OT MON...
AA
DT MON

<tbody>
</tbody>

In M5 currently I have (this needs some work too I believe but I'm just trying to wrap my head around the 2-dimensional requirement):
=IF((SUMIF(Sheet1!$E$5:E5,"<=8")+(COUNTIF(Sheet1!$E$5:E5,">8")*8))<40,(SUMIF(Sheet1!$E$5:E5,"<=8")+(COUNTIF(Sheet1!$E$5:E5,">8")*8)),0), which works but does not take into account the fact that someone could switch positions mid-week.

I tried applying this as:
=IF((SUMIFS($E:E,$E:E,"<=8",$A:$A,$A5)+COUNTIFS($A:$A,$A5,$E:E,">8")*8)<40,SUMIFS($E:E,$E:E,"<=8",$A:$A,$A5)+COUNTIFS($A:$A,$A5,$E:E,">8")*8,0). It works in the first column but as expected when I dragged it across it returned a #VALUE! error.

Any help on the above problem would be greatly appreciated.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
It looks like you need to take away the absolute references on your columns;
( Take away the dollar signs within your formulas)
 
Upvote 0
If I do that I don't account for weekly overtime. I.e. in the "Tuesday Hourly" column, I return:

=IF((SUMIFS(F:F,F:F,"<=8",$A:$A,$A5)+COUNTIFS($A:$A,$A5,F:F,">8")*8)<40,SUMIFS(F:F,F:F,"<=8",$A:$A,$A5)+COUNTIFS($A:$A,$A5,F:F,">8")*8,0)

Am I missing something?
 
Upvote 0
You'll need to explain that in more detail, how is it not calculating overtime (indeed how is overtime calculated)?

I don't quite get the logic of that formula, what is it supposed to do in business terms?
 
Upvote 0
You'll need to explain that in more detail, how is it not calculating overtime (indeed how is overtime calculated)?

I don't quite get the logic of that formula, what is it supposed to do in business terms?

Apologies, as my previous post was lengthy.

The formula is supposed to calculate hours worked at the regular hourly rate (before OT or DT). For these purposes, only overtime is important.

Overtime = 1.5 * Hourly, charged at the greater of

  • 8-12 hours/day OR
  • 40-60/week

After setup, this worksheet is essentially supposed to limit inputs to total hours worked in a day, and separate into hourly, overtime, and double time hours worked. As they vary project to project, the digits "8" and "40" in this formula will be replaced with a cell reference. I'm sure there's a better way to do it and this formula still needs some work as-is.

We are a time and materials company, and this will eventually spit out an invoice in a different worksheet based on hours entered by site staff.
 
Upvote 0
Why don't you just use

Code:
=SUMIFS(E:E,$A:$A,$A2)+(SUMIFS(E:E,$A:$A,$A2)-8)*0.5

Why don't you just use

Code:
=SUMIFS(E:E,$A:$A,$A2)+(SUMIFS(E:E,$A:$A,$A2)-8)*0.5

That doesn't account for weekly overtime? The logic is like this: IF weekly overtime, is greater than daily overtime, than weekly overtime is used, IF NOT then daily is used.
Weekly overtime = the sum of all hours worked over 40 in a week
Daily Overtime = the sum of all hours worked over 8 hours/day

Here are two examples to illustrate:
Employee 1

M T W T F S S
10 10 10 8 4 0 0

Weekly overtime = 2 hours
Daily overtime = 6 hours

Should be charged as:
Regular Time Hours = 8+8+8+8+4 = 36 hours
Overtime Hours = 2+2+2+0+0 = 6 hours


Employee 2

M T W T F S S
10 10 10 10 10 10 0

Daily Overtime = 2+2+2+2+2+2 = 12
Weekly Overtime = 0+0+0+0+10+10 = 20

Should be charged as:
Regular Time Hours = 10*4 = 40 hours (weekly cap)
Overtime Hours = 10*2 = 20 hours

So you see, the range has to be $E:E if I use this formula to make it work. I think there must be a better formula out there though, this one seems to me t be pretty messy. It is overcomplicated by the fact that after 12 hours/day OR 60 hours/week, hours are charged at double time. OT is the easy formula to create (Total-RT-DT).

The other issue with applying your formula to my situation is we can also have employees work 4-7 hours/day, and at 7 hours/day it seems to return 6.5 hours. The full 7 hours are hourly.

Sorry if I haven't explained it well enough. I try to explain this to employees all the time and struggle with it. Appreciate you guys trying to help.
 
Upvote 0
I think I get, but the one thing that needs clarification for me is Sat and Sun. In your example 2 you have them working 10 hours every day, but only 10 overtime hours. I would have thought that is 10 daily overtime, 20 weekend. So I think that should be 40 regular, 10 * 1.5 and 20 * 2, a total of 95.

Then take the example of 6 6 6 6 6 6 6. Is that 36 regular, non at 1.5 overtime and 12 at double rate overtime, or 40 regular, 2 at double rate?
 
Upvote 0
I think I get, but the one thing that needs clarification for me is Sat and Sun. In your example 2 you have them working 10 hours every day, but only 10 overtime hours. I would have thought that is 10 daily overtime, 20 weekend. So I think that should be 40 regular, 10 * 1.5 and 20 * 2, a total of 95.

Then take the example of 6 6 6 6 6 6 6. Is that 36 regular, non at 1.5 overtime and 12 at double rate overtime, or 40 regular, 2 at double rate?

Sorry for the late response on this, and I appreciate you helping me out. I had a mistake in my formula, but the OT and DT are flipped (first 4 days are regular time (40 total), next 2 days are OT (20 total), next 1 is Double (10 Total)) - a total of 90.

Your example would be a weekly overtime calculation, since daily OT is never hit. In that case, 42 total hours would be calculated as 40 * 1 and 2 * 1.5.

Just to clarify - the actual days of the week have no bearing on overtime. If someone works no hours during the regular workweek and 8 hours on Saturday, then another 8 on Sunday, the full 16 is calculated at the straight hourly rate.If the same example is used, but 10 hours are worked each day, the calculation is 8+8 = 16 (*1), and 2+2 = 4 (*1.5).

As I was driving to work today I remember a formula I had figured out that used two criteria and an indirect to calculate total hours worked in a year in a given position. It was this formula:

=IFERROR(SUMPRODUCT(SUMIFS(INDIRECT("'"&$AM$11:$AM$12&"'!X:X"),INDIRECT("'"&$AM$11:$AM$12&"'!D:D"),D11,INDIRECT("'"&$AM$11:$AM$12&"'!F:F"),F11)),).
(the bane of my existence as I'm sure you can understand that even a computer with 32 GB of RAM and 64-bit couldn't use this across 100 worksheets)

Obviously, there is no need for indirects as I am not referencing other worksheets. The reason I remembered it was because it accounts for the two criteria. I have a pretty good idea of how it works, but I can't quite tie it together with this example. I could probably figure it out but as I am not proficient to the level I'm sure you are, the formula would wind up very long and messy. Is there a way you can see tying the overtime criteria in with this formula, in a memory-efficient way?
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top