Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Criteria Syntax in SUMIF formula | Excel Discussion (Misc queries) | |||
Calculate a SUMIF if criteria is between 2 date ranges | Excel Worksheet Functions | |||
SUMIF Formula w/ OR Criteria | Excel Discussion (Misc queries) | |||
Sumif with 2 cell criteria | Charts and Charting in Excel | |||
Can a formula be used in the Criteria field of SUMIF?? | Excel Worksheet Functions |