I have a report that references an external workbook and utilizes sumifs statements so some of my users are getting #VALUE errors when the source workbooks aren't open. I would like to utilize sumproduct instead since I have read that it will fix this issue but I don't have any experience with it and am running into some issues trying to convert my formula. Here is the original sumifs statement I would like to convert:
=SUM(SUMIFS([Source]Sheet!'!$I$1:$I$65536,[Source]Sheet!'!$C$1:$C$65536,101,[Source]Sheet!'!$J$1:$J$65536,{"Breakage","Cycle Count","Manager Cycle Count"}, [Source]Sheet!'! $L$1:$L$65536,$A3))
I think my main issue is converting the array formula section of my original statement but it could be something else I am missing? Thanks in advance for any help.
Thanks,
Mike
=SUM(SUMIFS([Source]Sheet!'!$I$1:$I$65536,[Source]Sheet!'!$C$1:$C$65536,101,[Source]Sheet!'!$J$1:$J$65536,{"Breakage","Cycle Count","Manager Cycle Count"}, [Source]Sheet!'! $L$1:$L$65536,$A3))
I think my main issue is converting the array formula section of my original statement but it could be something else I am missing? Thanks in advance for any help.
Thanks,
Mike