Hi everyone, sorry if this is a little incoherent, I've read so many posts and articles today trying to figure this out my brain is a little scrambled. Basically I'm trying to use SumProduct as a SumIf because the source data is in 21 external files that will be closed so SumIf and Indirect are out if I understand correctly. The 21 external files are all formatted identically so the ranges are exactly the same for each. There are however some empty rows and I think I recall reading that this can be an issue, not sure if it's a resolvable one or not though.
I've attached a workbook with some examples of the external file ranges and the desired outcome as well as a few of the millions of different formula combinations I've tried. Hopefully it will be clear to you experts what it is I'm trying to accomplish here, but just to add a little more clarity (hopefully), here is the description...
My Destination Table has 4 columns:
Folder Path File Name Cycle Workable Hours
I'm trying to incorporate the Folder Path and File Name values into a SumProduct formula to look in the source workbook on the 'Time Entry' worksheet in Range $A$3:$A$274 and sum all the values in Range $BJ$3:$BJ$274 where the cell values of the rows in the first range match with the 'Cycle' value in my destination table, so basically a SUMIF... as I said there are blank rows in the source wbs, and I'm wondering if that's a dealbreaker?
I've tried using Power Query to do this, but something really weird happens if the source wbs are opened, and it messes up the queries. I've never experienced that before and was wondering if it had something to do with the source wb file extensions being .xlsb? Either way, I thought a formulaic approach might actually be better and faster if it's possible.
Thanks in advance!
Joe
Bookmarks