Dell XPS 400

I need help adding a range of cells. Each cell in the range was determined by the if statement: =IF(K12>40,K12-40,L12=0. When I add the range L5:L16 I get Zero. This is used for figuring out overtime and I want to be able to get the total OT for 12 employees. It appears using summation for cells that have an if statement is different. Thanks!

Using the result of a summation does not change the behavior of an IF statement. Please repost yout formula. It does not have a closing parenthesis, so I'm not sure what it is really supposed to be.

=IF(K12>40,K12-40,L12=0

Column K has the total number of hours an employed has worked in a week. The range starts at K5 and goes thru K16. In column L I used the if statement to figure out OT hours above the 40 in column K. Of course if an employee has less than 40 hours the if statement will return a value of 0 in column L. When I go to add up the cells in column L using the sum function I get 0 in L17. My if statement for L5 is =IF(K5>40,K5-40,L5=0)and so on for the rest of the cells. Again, when I try to sum the OT hours in the L17 cell I get an answer of 0.

Your IF statement is incorrect. It is causing a circular reference which is confusing Excel. Since it can't figure out what to do in the cells with the incorrect formulae, it gives you a 0 answer when you try to sum all the "mistakes".

Your IF Statement should read:

=IF(K5>40,K5-40,0)When you use L5=0 as your "condition if false", you are actually telling Excel to test and see if L5=0 if K5 <= 40. It can't do because it is trying to evaluate the IF statement in L5. Take your IF statement and put in any other cell, and you'll get TRUE or FALSE based on whatever is in L5 if K5 is not greater than 40.

By the way, if all you want is the total number of overtime hours, and not each individual's OT hours, you could use:

=SUMIF(K5:K16,">40")-COUNTIF(K5:K16,">40")*40 in any cell.

Ask Your Question

Weekly Poll

Would you buy mobile phone service from Google?

Discuss in The Lounge

Poll History