Nested if statement that adds

[TerylFields]

Hello Board, I need I think a nested if statement that adds overtime. I have 5 columns and 25 rows. Each row has a field that calculated 5 different column of time to figure out overtime. Any of the 5 cell that hold hours over 40 need to calculate the over 40 number as overtime. The formula needs to check all 5 cells and if any of them are over 40 hours do the calculation and add all 5 cells and place that number in 1 cell. example If week 1 is 43 with 3 hours overtime, and week 2 has 47 with 7 hours overtime and the remaining week with 40 hours. Then I need a formula that will check all the cell and calculate the number of overtime hour time the rate of pay with is located in one of the cell in the beginning of the row. I tried a nested if but it didn't add all the cell with overtime together so maybe a nested if is not what I need. Please help, this formula would save a lot of time....Thanks in advance

 

[etaf]

this is a bit clunky - and i'm sure there is a better way

=SUMIF(A1:E1,">40")-(COUNTIF(A1:E1,">40")*40)
so its adding up the cells that are over 40 hours
and then also counting those cells for each cell over 40 and subtracting

so if you have three cells over 40 it will sum those 3 cells including each of the 40 hours
now we need to subtract each of those 40
so a count * 40 will do that

 

[ukmikeb]

Hi

Or :-

=SUMPRODUCT(--(A1:E1>40),--(A1:E1-40))

adjust columns accordingly.

hth