Forum Discussion
Need help with an Excel roster document where I'm trying to journal hours
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!
Matt - these are the two spreadsheets I'm working with
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-
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.
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!