Forum Discussion
Need help with an Excel roster document where I'm trying to journal hours
Hi - I'm trying to write a formula to add the number of hours across a month that somebody might work in a roster. In that month they might work in 3 different areas, so I need to have the formula along the lines of
COUNTIF the conditional formatting = "TRN" then add the hours that are associated with "TRN". So, TRN appears on Cell D8, D11 and D15 but the hours worked there appear on D9, D12 and D16
Then I also need to put another formula in that says that if Cell B3 equals TRN then Cell F3 equals a specific Cost Centre number.
Any assistance would be greatly appreciated. I hope that it makes sense.
Thanks
Claire
Claire-
Please try this formula. The data is a little different then the initial file you provided which alters the formula a tad bit. THe important thing is that the Employee # matches in both sheets in the exact same format / cells. :
=SUMPRODUCT(('[Semester 2 Formula Help.xlsx]Class Support - South Region'!$B$6:$B$21=LOOKUP("Z",A$4:A4))*('[Semester 2 Formula Help.xlsx]Class Support - South Region'!$E$5:$FA$20=B3),'[Semester 2 Formula Help.xlsx]Class Support - South Region'!$E$6:$FA$21)
Hope this helps!
Claire-
Maybe try something like these conditional IF() formulas (See attached excel file for example):
Thanks Matt - i'm actually working across two difference spreadsheets, so the Roster is in one and the Journal is another one. I've attached two "samples" of it so you can see what I mean. Obviously in the journal one at the moment I've just the Sum function for Total hours for line C4 downwards, however as the "real" roster goes for an entire semester I don't want to have to be clicking on individual cells to make that formula.
your assistance is greatly appreciated.
Here's the other spreadsheet
- Using the attachment provided by Matt Mickle, you can use the following formula in F8:
=SUMIF(D8:D15,"TRN",D9:D16)
Blessings!And, for your file attached, see an option. Blessings!
