I have spent time looking at the forum and elsewhere to try finding the answer to my question, but have not had any luck. I have attempted to modify solutions found in various sources (including the forums here) but no matter what I try, I get a#VALUE! error. I’ve come close and can get results if I use just one criteria, but not so well with two criteria. I apologise if I’m asking a question alreadyanswered, but I have attempted multiple potential solutions without success.
On my presentation sheet, I want to display the SUM of minutes (in [H:MM] format) spent on a certain task by my staff, with NAME and MONTH being the criteria to be met.
The data source sheet I am getting this information from, will remain closed. Therefore SUMIFS (apparently) won’t work so I have been (unsuccessfully) trying to get SUMPRODUCT to do the trick.
My latest attempt (based on something I found in the forum)is this:
=SUMPRODUCT(--('[Source File]]Sheet1'!$MO:$MO=$A32)*--(' SourceFile]]Sheet1'!$LZ:$LZ=$AJ30)*'[Source File]]Sheet1'!$MN:$MN)
On my source sheet, thecolumns are: MO (employee name), MN (number of minutes) LZ (month name), so the way I look at it, this formula is reading as: Employee name (=A32)*Month Name (=AJ30)*Minutes.
On my presentation sheet, the criteria is A32 (employee name) and AJ30 (month name)
Thank you very much for any assistance you can provide.
On my presentation sheet, I want to display the SUM of minutes (in [H:MM] format) spent on a certain task by my staff, with NAME and MONTH being the criteria to be met.
The data source sheet I am getting this information from, will remain closed. Therefore SUMIFS (apparently) won’t work so I have been (unsuccessfully) trying to get SUMPRODUCT to do the trick.
My latest attempt (based on something I found in the forum)is this:
=SUMPRODUCT(--('[Source File]]Sheet1'!$MO:$MO=$A32)*--(' SourceFile]]Sheet1'!$LZ:$LZ=$AJ30)*'[Source File]]Sheet1'!$MN:$MN)
On my source sheet, thecolumns are: MO (employee name), MN (number of minutes) LZ (month name), so the way I look at it, this formula is reading as: Employee name (=A32)*Month Name (=AJ30)*Minutes.
On my presentation sheet, the criteria is A32 (employee name) and AJ30 (month name)
Thank you very much for any assistance you can provide.
Last edited: