SUMPRODUCT with closed workbook

[JamesonMH]

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.

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

 

[Aladin Akyurek]

=SUMPRODUCT(SumRange,--ISNUMBER(MATCH(ConditionRange,ConditionList,0)))

sums a range where the values of a condition range match a list of conditions.

 

[JamesonMH]

=SUMPRODUCT(SumRange,--ISNUMBER(MATCH(ConditionRange,ConditionList,0)))

sums a range where the values of a condition range match a list of conditions.

Aladin - you came through, again. Thank you this works perfectly!

 

[Aladin Akyurek]

Aladin - you came through, again. Thank you this works perfectly!

You are welcome.