Hi folks.
Just seeing if I could get advice on SUMIFS to SUMPRODUCT formulas.
I have, attached, 3 'stock trackers' for use in my org with a 4th file that aggregates info from the three other trackers.
(Note: excel I have available at work is 2013, edited these to zero 'sensitive' info on office 365, I know that there are better features that might work better for me on Excel 365 but I'm tied to 2013 sadly).
The issue I'm having, which appears to be a common issue folk run into with as little excel knowledge as I have, is that the National snapshot file will not update without the other three trackers being open.
I'm aware that the issue is the SUMIF argument and that SUMPRODUCT would be better but I don't have the knowledge to know how to make a change that keeps the purpose of the SUMIF formula intact. I'm also wondering if my used of tables might cos an issue, I hope not.
You can see from the East/North/West trackers that there are a fair few SUMIF formulas.
The columns in question as on the 'Stock Movement' sheets, columns Q, R and U-Z (except West where it is U-Y).
The data goes to the National tracker in columns K-S and V-AA.
Any help would be appreciated but I can, if not possible to correct, just stipulate any work on the National tracker requires all 4 docs to be open...the National one will see rare use beyond a month archiving.
Point of note: I used these formulas off the back of another much small stock sheet I made last year as a precursor to this. The same set up but, for this one, the National sheet updated with no problem while the others were closed. I think this might be due to the fact that I had collated regional data from the SUMIFs into a sheet on the regional trackers and pulled the national data from that rather than the SUMIFs (sorry if that sounds confusing). That reasoning is why there is a 'Sheet2' in the East tracker as it is an avenue I may follow if there is not solution with the SUMIFS/SUMPRODUCT issue.
Any feedback/advice would be very much aprrciated.
Thanks
Malc
