Need help with an Excel roster document where I'm trying to journal hours

[Claire Buchtmann]

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

[#2]

Claire-

Maybe try something like these conditional IF() formulas  (See attached excel file for example):

[#3]

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.

[#4]

Here's the other spreadsheet

[#5]

Using the attachment provided by Matt Mickle, you can use the following formula in F8:

=SUMIF(D8:D15,"TRN",D9:D16)

Blessings!

[#6]

And, for your file attached, see an option.  Blessings!

[#7]

Claire-

Here is a slight variation on John Jairo Vergara Dominguez's formula.  It works the pretty much the same but excludes the N() function:

=SUMPRODUCT(($A$4:$A$19=LOOKUP("Z",R$2:R2))*($C$4:$N$19=S2),$C$5:$N$20)

Here's some information on the N() Function.  It can come in handy if you use Google Sheets:

N function - Office Support

Remarks

• It is not generally necessary to use the N function in a formula, because Excel automatically converts values as necessary. This function is provided for compatibility with other spreadsheet programs.

• Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.

[#8]

Matt - these are the two spreadsheets I'm working with

[#9 · Accepted solution]

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!

[#10]

Claire-

After reviewing your scenario a little bit further I think these two formulas will work for what you need.  You will need to adjust the bold portion of the formulas to only encompass the month range (i.e. July) you are looking for:

July Example (Put this in cell C3 and drag down):

Formula Based on Employee Name Criteria:

=SUMPRODUCT(('[Semester 2 Formula Help.xlsx]Class Support - South Region'!$B$5:$B$20=LOOKUP("Z",A$3:A3))*('[Semester 2 Formula Help.xlsx]Class Support - South Region'!$E$5:$M$20=B3),'[Semester 2 Formula Help.xlsx]Class Support - South Region'!$E$6:$M$21)

Formula Based on Employee ID Criteria:

=SUMPRODUCT(('[Semester 2 Formula Help.xlsx]Class Support - South Region'!$B$6:$B$21=SMALL(A$4:A4,1))*('[Semester 2 Formula Help.xlsx]Class Support - South Region'!$E$5:$M$20=B3),'[Semester 2 Formula Help.xlsx]Class Support - South Region'!$E$6:$M$21)

Hope this helps.

[#11]

A Massive THANKS Matt Mickle - you've saved me hours of headaches and frustration!  THANK YOUx1000!!!!

I tried to like the last post, but it won't let me - but I will keep trying!

THANK YOU AGAIN!