How to Sum<=40, sum, if 40 sum in next column
 

I am trying to sum a column of hours and minutes to calculate straight hours
and overtime. If the sum is <=40, then sum; otherwise move excess to the
next column. What is the foluma I should use to do this. I'm stumped.

How to Sum<=40, sum, if 40 sum in next column
 

You will need to use two formulas.
First column formula:
=if(sum(a1:a5)<=40,sum(a1:a5),40)
Second column:
=if(sum(a1:a5)40,sum(a1:a5)-40,"")

The second column will be blank if there is no overtime.
--
Best Regards,

Luke M


"justnotgettingit" wrote:

I am trying to sum a column of hours and minutes to calculate straight hours
and overtime. If the sum is <=40, then sum; otherwise move excess to the
next column. What is the foluma I should use to do this. I'm stumped.

How to Sum<=40, sum, if 40 sum in next column
 

try this:
=SUMPRODUCT(--(A2:A120),--(A2:A12<40),(A2:A12))

Regards,
Ryan--


--
RyGuy


"Luke M" wrote:

You will need to use two formulas.
First column formula:
=if(sum(a1:a5)<=40,sum(a1:a5),40)
Second column:
=if(sum(a1:a5)40,sum(a1:a5)-40,"")

The second column will be blank if there is no overtime.
--
Best Regards,

Luke M


"justnotgettingit" wrote:

I am trying to sum a column of hours and minutes to calculate straight hours
and overtime. If the sum is <=40, then sum; otherwise move excess to the
next column. What is the foluma I should use to do this. I'm stumped.

How to Sum<=40, sum, if 40 sum in next column
 

Thanks Luke, that worked.

"Luke M" wrote:

You will need to use two formulas.
First column formula:
=if(sum(a1:a5)<=40,sum(a1:a5),40)
Second column:
=if(sum(a1:a5)40,sum(a1:a5)-40,"")

The second column will be blank if there is no overtime.
--
Best Regards,

Luke M


"justnotgettingit" wrote:

I am trying to sum a column of hours and minutes to calculate straight hours
and overtime. If the sum is <=40, then sum; otherwise move excess to the
next column. What is the foluma I should use to do this. I'm stumped.

How to Sum<=40, sum, if 40 sum in next column
 

With hours:Min in A1:A10
The total up to 40: =MIN(40/24,SUM(A1:A10)*24)
The excess over 40: =IF(SUM(A1:A10)*2440,MOD(SUM(A1:A10),40/24),"")
Both cells formatted with [h]:m
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"justnotgettingit" wrote in
message ...
I am trying to sum a column of hours and minutes to calculate straight
hours
and overtime. If the sum is <=40, then sum; otherwise move excess to the
next column. What is the foluma I should use to do this. I'm stumped.

How to Sum<=40, sum, if 40 sum in next column
 

Regular hours:
=MIN(SUM(A1:A5),40)

OT hours:
=MAX(SUM(A1:A5)-40,0)


"justnotgettingit" wrote:

I am trying to sum a column of hours and minutes to calculate straight hours
and overtime. If the sum is <=40, then sum; otherwise move excess to the
next column. What is the foluma I should use to do this. I'm stumped.