Hi all,
I get it that SUMIF(S) doesn't work with closed workbooks. Normally, SUMPRODUCT works fine in such scenarios. Except, if I have many lookup values. I can't figure out an easy way to resolve other than a very long/ugly SUMPRODUCT.
E.g.
<tbody>
</tbody>
If it were in the same workbook, I'd use a {SUM(SUMIFS....)) }.
Again, if I SUMPRODUCT the above, it would be massive since I'd have to use separate arrays for each individual store.
Any ideas?
Thanks
James
I get it that SUMIF(S) doesn't work with closed workbooks. Normally, SUMPRODUCT works fine in such scenarios. Except, if I have many lookup values. I can't figure out an easy way to resolve other than a very long/ugly SUMPRODUCT.
E.g.
Stores to sum sales | Location of sales data (many rows) |
Store 1 | Closed workbook |
Store 3 | " |
Store 4 | |
Store 7 | |
Store 10 | |
Store 11 | |
Store 12 | |
Etc... | " |
<tbody>
</tbody>
If it were in the same workbook, I'd use a {SUM(SUMIFS....)) }.
Again, if I SUMPRODUCT the above, it would be massive since I'd have to use separate arrays for each individual store.
Any ideas?
Thanks
James