sumif formula to calculate value of cell if certain criteria are m
 

i am working on a time sheet speadsheet. Cells B14 to H15 are the hours per
day worked. The formula I am trying to figure out is

if the sum of b14 to h14 44 then enter the value 44, if the sum is less the
44 then enter the sum of the cells.
--
newbie at large!

sumif formula to calculate value of cell if certain criteria are m
 

=MIN(SUM(B14:H14),44)
--
Gary''s Student - gsnu200827


"Lisa" wrote:

i am working on a time sheet speadsheet. Cells B14 to H15 are the hours per
day worked. The formula I am trying to figure out is

if the sum of b14 to h14 44 then enter the value 44, if the sum is less the
44 then enter the sum of the cells.
--
newbie at large!

sumif formula to calculate value of cell if certain criteria a
 

now I have problem with a formula in another cell that depended on the value
of the cell containing the formula you gave me.

Cells B14:H14 are the number of hours per day, cell I14 equals the number of
regular hours up to 44, cell j14 equals the overtime hours, those greater
than 44.

what formula do I use to calculate the overtime hours. I had tried
=sumif(B14:H14.44,((B14:H14)-44),0) how close am I?

--
newbie at large!


"Gary''s Student" wrote:

=MIN(SUM(B14:H14),44)
--
Gary''s Student - gsnu200827


"Lisa" wrote:

i am working on a time sheet speadsheet. Cells B14 to H15 are the hours per
day worked. The formula I am trying to figure out is

if the sum of b14 to h14 44 then enter the value 44, if the sum is less the
44 then enter the sum of the cells.
--
newbie at large!

sumif formula to calculate value of cell if certain criteria a
 

I assume that you want to calculate the portion of OT hours which is 44 hours
in J14
try this formula, in J14

=IF(SUM(B14:H14)<=44,"No OT",SUM(B14:H14)-44)

Does this do waht you want? if not, you need to post back with an example.

HTH
--
Your feedback is very much appreciate, pls click on the Yes button below if
this posting is helpful.

Thank You

cheers, francis


"Lisa" wrote:

now I have problem with a formula in another cell that depended on the value
of the cell containing the formula you gave me.

Cells B14:H14 are the number of hours per day, cell I14 equals the number of
regular hours up to 44, cell j14 equals the overtime hours, those greater
than 44.

what formula do I use to calculate the overtime hours. I had tried
=sumif(B14:H14.44,((B14:H14)-44),0) how close am I?

--
newbie at large!


"Gary''s Student" wrote:

=MIN(SUM(B14:H14),44)
--
Gary''s Student - gsnu200827


"Lisa" wrote:

i am working on a time sheet speadsheet. Cells B14 to H15 are the hours per
day worked. The formula I am trying to figure out is

if the sum of b14 to h14 44 then enter the value 44, if the sum is less the
44 then enter the sum of the cells.
--
newbie at large!

sumif formula to calculate value of cell if certain criteria are m
 

Hi Lisa

There are a couple of ways of doing this:

1. =IF(SUM(B14:H14)44,44,SUM(B14:H14))
2. =MIN(SUM(B14:H14),44)

hth,

Neil

"Lisa" wrote in message
...
i am working on a time sheet speadsheet. Cells B14 to H15 are the hours
per
day worked. The formula I am trying to figure out is

if the sum of b14 to h14 44 then enter the value 44, if the sum is less
the
44 then enter the sum of the cells.
--
newbie at large!

sumif formula to calculate value of cell if certain criteria are m
 

Lisa

I assume that B14 to H15 are hours worked morning and afternoon and that
what you want is of the sum B14 to H15 44 then enter 44, rather than H14
as you say below.

Anyway, wherever you want the answer, insert the formula
=IF(SUM(B14:H15)=44,44,SUM(B14:H15)) . Tweak as you need to....

"Lisa" wrote in message
...
i am working on a time sheet speadsheet. Cells B14 to H15 are the hours
per
day worked. The formula I am trying to figure out is

if the sum of b14 to h14 44 then enter the value 44, if the sum is less
the
44 then enter the sum of the cells.
--
newbie at large!