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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Stores to sum sales[/TD]
[TD]Location of sales data (many rows)[/TD]
[/TR]
[TR]
[TD]Store 1[/TD]
[TD]Closed workbook[/TD]
[/TR]
[TR]
[TD]Store 3[/TD]
[TD]"[/TD]
[/TR]
[TR]
[TD]Store 4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Store 7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Store 10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Store 11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Store 12
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Etc...[/TD]
[TD] "[/TD]
[/TR]
</tbody>[/TABLE]

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.