ifsum or ifcount formula

[ESPALLER]

Okay, In cell E17 I am trying to say that if the data in cells P17-T17 are greater than 8, then the sum of what is greater than 8 will go in E17.. basically, overtime is anything over 8 hrs a day, cells p17 through t17 are the days of the week.. if any of them are over 8, then i need what is over 8 to sum in cell E17 for the total overtime.. I tried this:

=IF(SUMIF(P17:T17,0)>8,0,SUM(0))


It does not work! What am I missing?

 

[danzon]

Try

=SUMIF(P17:T17,">8")

 

[danzon]

Or something like this to add only the TRUE overtime... that is to sum the hours in each day that exceed the standard of 8

=SUMIF(P17:T17,">8")-(COUNTIF(P17:T17,">8")*8)


but.... question... if you work 9 hours on Monday...but then 0 on Tues thru Friday... do you still want to show 1 hour of overtime ?

You may want to check for a true weekly total over 40 before implementing the overtime calculation

 

[ESPALLER]

Or something like this to add only the TRUE overtime... that is to sum the hours in each day that exceed the standard of 8

=SUMIF(P17:T17,">8")-(COUNTIF(P17:T17,">8")*8)


but.... question... if you work 9 hours on Monday...but then 0 on Tues thru Friday... do you still want to show 1 hour of overtime ?

You may want to check for a true weekly total over 40 before implementing the overtime calculation

AHH, you're wonderful! it worked perfectly!
thank you so much!

 

[Scott Huish]

Also:

=SUM(IF(P17:T17>8,P17:T17-8))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.

 

[ESPALLER]

Or something like this to add only the TRUE overtime... that is to sum the hours in each day that exceed the standard of 8

=SUMIF(P17:T17,">8")-(COUNTIF(P17:T17,">8")*8)


but.... question... if you work 9 hours on Monday...but then 0 on Tues thru Friday... do you still want to show 1 hour of overtime ?

You may want to check for a true weekly total over 40 before implementing the overtime calculation

I sent you a direct message, too, danzon, but if anyone else sees this... this formula he gave was perfect.

=SUMIF(P17:T17,">8")-(COUNTIF(P17:T17,">8")*8)


and I need further help.

In the same vein, I need the sum of cells p:17-T17 for what is 8 and less in each cell.

So one cell with be the formula above and will calculate anything that is over 8, and the other will calculate anything that is 8 and below.

that way i'll have 1 cell that is for overtime, one for straight and then can total the two in an additional.

 

[danzon]

Can't you just use

=SUM(P17:T17)-P19 where P19 is the overtime calculation that we solved previously... ?


aslo... you didn't answer in my previous post where I asked... "but.... question... if you work 9 hours on Monday...but then 0 on Tues thru Friday... do you still want to show 1 hour of overtime ?"

^ this point is kind of important

 

[ESPALLER]

Can't you just use

=SUM(P17:T17)-P19 where P19 is the overtime calculation that we solved previously... ?


aslo... you didn't answer in my previous post where I asked... "but.... question... if you work 9 hours on Monday...but then 0 on Tues thru Friday... do you still want to show 1 hour of overtime ?"

^ this point is kind of important

Yes, I do still want it to reflect that. We pay overtime rate on anything over 8 in 1 day, not 40 in 1 week. So it has to be by the daily.

I guess I am not sure what you mean to just use the one we solved previously. I tried to change it around myself so that it would reflect anything 8 and less the sum of the parts, but wasnt able to get it.

 

[danzon]

OK... so what I am suggesting basically looks like this

=(SUM(P17:T17))-(SUMIF(P17:T17,">8")-(COUNTIF(P17:T17,">8")*8))


so that you are just adding up all the total hours....and then subtracting the overtime

 

[Scott Huish]

Excel Workbook
	O	P	Q	R	S	T
17		12	5	4	12	11
18
19	Reg Hrs:	33
20	OT Hrs:	11
Sheet1