Okay, this is driving me crazy and I know the solution is a simple one, I'm just not seeing it no matter what I do. I have two workbooks (for now, it is technically more, but right now I'm just working with two). BanksTest and SupervisorTest.
BanksTest is where an employee will log in what work they have done.
Column B is the date.
Column F is the time spent (calculated from Columns D and E, and will be hidden from view, the employees really don't need to see any calculations)
Column J is the Activity Code (type of work done)
What the supervisor wants to see is how much time per month was spent on each Activity Code. I know SUMIFS will not work unless BanksTest is open. I don't want the supervisor to have to remember to have each employee's sheet open when he's looking at his own spreadsheet (nice guy, not computer savvy... I had to make a video on how to create a desktop shortcut). So I'm looking to SUMPRODUCT for the calculation, but I still cannot get it to work.
So for July (the beginning of our fiscal year), Activity Code 01
=SUMPRODUCT(--([BanksTest.xlsx]Log!$J$13:$J$18=1),--([BanksTest.xlsx]Log!$B$13:$B$18>=$U6),--([BanksTest.xlsx]Log!$B$13:$B$18<=$V6),--([BanksTest.xlsx]Log!$F$13:$F$18))
And the blasted thing STILL gives me !VALUE#
What am I not catching....
(At this point I'm not even worried about the quarterly/yearly stuff, if I can get the monthly to add up, I can extrapolate that info)
Bookmarks