Hello, I only want to add positive values in a cell and not a negative value in a row. But the cells I want to total in the row are every 5. Is there a formula that can do this? Many thanks
I want to find the amount of overtime hours we are paying each week to employees and I want a monthly total at the end of the row for each employee. How I have set the spreadsheet up to get the O/T value is:
F4(Actual Hrs) – B4(Contracted Hrs) = E4(O/T Hrs) That formula is for one employee for the one week, so there are another 3 or 4 formulas going across the rows for the month for the employee. The trouble is when I get the final formula for the end of the month it is calculating the negative values ie if the employee hasn’t reached their contracted hours as well as the positive values ie if they have gone over their contracted hours (which I want to know) Whatever formula I put in at the end it never picks up only the positive values eg, e4+k4+q4+w4+ac4+ai4,”>0″e4+k4+q4+w4+ac4+ai4
—–
Chris
I don't understand why the values are in every fifth row. Every row should be one record of data. Why are 4 rows skipped?
If you have E4 contains the number of overtime hours, instead of it being F4-B4, make it MAX(F4-B4,0). So it will be 0 if the number is negative. Then just use SUM(E) to get the total.
Thank you Gary, you have solved my problem and saved me many hours. Many thanks. PS I meant columns not rows.