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

  • Thread starter Thread starter Lisa
  • Start date Start date
L

Lisa

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.
 
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?
 
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
 
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

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....
 
Back
Top