IF statements and Finding text in one column and displaying value in adjacent column

[jrace]

Hi, I am working on a timesheet and am running into some problems with my formulas.
First I need to take the total from H18 and if it is over 40 subtract 40 from H18, I need to do the same for H28. I know how to do this separately with an IF statement but I need the combined total (if any) to display in cell B30.
Second I Need to display any of the numerical values that may be entered in column N21:N28 to populate in the corresponding cells at the bottom of the sheet based on the Code values that may be entered into column M21:M27 (H, LWOP, M, F, J ML, D, FS)
I have tried attached the workbook so that it makes more sense.Timesheet for PPE 4-12-13.xlsx

[tigeravatar]

jrace,

Welcome to the forum!
For your first question, this formula in cell B30 should do the trick:

=MAX(H18-40,0)+MAX(H28-40,0)

For your second question, you just need a Sumif() formula. For example, in cell B31 (Holiday Hrs), the formula would look like this:

=SUMIF($M$21:$M$27,"H",$N$21:$N$27)

Copy that sumif formula to the other cells that need to be summed by code and change the code you're looking for from "H" to the appropriate code.

[jrace]

Thanks Tiger! The first formula works great but I entered the second formula in B31 and then the H code in column M and corresponding hours in column N and B31 is still showing 0!

[tigeravatar]

It works fine for me... can you supply a sample workbook that is experiencing the issue?

[jrace]

Timesheet for PPE 4-12-13.xlsx
I appreciate your help!

[tigeravatar]

jrace,

From your original post:

Second I Need to display any of the numerical values that may be entered in column N21:N28 to populate in the corresponding cells at the bottom of the sheet based on the Code values that may be entered into column M21:M27 (H, LWOP, M, F, J ML, D, FS)

Using that information, I provided the Sumif formula that only looks in that range of cells. You have used that formula, but then put the code and hours outside that range of cells. You put the code in cell M13 (with its corresponding hours in N13). That is not within the range of rows 21:27, so the formula isn't looking there and so that information never gets summed. If you want to include that range, you'll need to adjust where the formula is looking:

=SUMIF($M$11:$M$27,"H",$N$11:$N$27)

[jrace]

Thank you!! Too much multi-tasking today!