MustLoveCorgis
New member
- Joined
- Jun 25, 2020
- Messages
- 3
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- Office 365 Plus
Okay, this is killing me!
I have two workbooks, Banks and Supervisor. I want to be able to take information from Banks and calculate it into Supervisor. I have time added up in Banks, [hh]:mm custom format in Column L. Column P is the Activity Code. Column B is the date.
In Supervisor, I want to look for any date that falls within the month (in this case, July - found in X6 and W6), and has an activity code of 1. With those found, sum up the time spent and put it in cell B6 of Supervisor. I know SUMIFS won't work if Banks is closed, so I have to use SUMPRODUCT. I know I've done this before, but for the life of me, I can't get it to work. It always shows up as #VALUE!. Everything is formatted as Custom: [hh]:mm
=SUMPRODUCT(([Banks.xlsx]Log!$B$11:$B$1999<=X6)*([Banks.xlsx]Log!$B$11:$B$1999>=W6)*([Banks.xlsx]Log!$P$11:$P$1999=1)*[Banks.xlsx]Log!$L$11:$L$1999)
**Note: Both sheets are Restricted. However, I have full access to both.**
I have two workbooks, Banks and Supervisor. I want to be able to take information from Banks and calculate it into Supervisor. I have time added up in Banks, [hh]:mm custom format in Column L. Column P is the Activity Code. Column B is the date.
In Supervisor, I want to look for any date that falls within the month (in this case, July - found in X6 and W6), and has an activity code of 1. With those found, sum up the time spent and put it in cell B6 of Supervisor. I know SUMIFS won't work if Banks is closed, so I have to use SUMPRODUCT. I know I've done this before, but for the life of me, I can't get it to work. It always shows up as #VALUE!. Everything is formatted as Custom: [hh]:mm
=SUMPRODUCT(([Banks.xlsx]Log!$B$11:$B$1999<=X6)*([Banks.xlsx]Log!$B$11:$B$1999>=W6)*([Banks.xlsx]Log!$P$11:$P$1999=1)*[Banks.xlsx]Log!$L$11:$L$1999)
**Note: Both sheets are Restricted. However, I have full access to both.**
