SUMPRODUCT on 2590 cells - Very slow

[JamesW]

Hi,

As you can imagine, running SUMPRODUCT on 2590 cells is very slow. What else can I use?

=SUMPRODUCT(--((MID(B$2,FIND(" ",B$2,4)+1,2)+0)='wk 5 - 16'!$A$1:$A$64271),--($A3='wk 5 - 16'!$C$1:$C$64271),--('wk 5 - 16'!$F$1:$F$64271="YES"))

Main sheet where the formula is:

<table style="width: 392px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 102pt;" width="136"> <col style="width: 48pt;" width="64" span="4"> <tbody><tr style="height: 12.75pt;" height="17"><td class="xl27" style="height: 12.75pt; width: 102pt;" width="136" height="17">Week Totals</td><td class="xl28" style="border-left: medium none; width: 48pt;" align="right" width="64">449</td><td class="xl28" style="border-left: medium none; width: 48pt;" align="right" width="64">54</td><td class="xl28" style="border-left: medium none; width: 48pt;" align="right" width="64">212</td><td class="xl29" style="width: 48pt;" align="right" width="64">309</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl30" style="height: 12.75pt; border-top: medium none;" height="17">End Market</td><td class="xl32" style="border-top: medium none; border-left: medium none;">Week 5</td><td class="xl32" style="border-top: medium none; border-left: medium none;">Week 6</td><td class="xl32" style="border-top: medium none; border-left: medium none;">Week 8</td><td class="xl33">Week 9</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl31" style="height: 12.75pt; border-top: medium none;" height="17">Poland </td><td class="xl26" style="border-top: medium none; border-left: medium none;" align="right">0</td><td class="xl26" style="border-top: medium none; border-left: medium none;" align="right">0</td><td class="xl26" style="border-top: medium none; border-left: medium none;" align="right">0</td><td class="xl25" style="border-top: medium none;" align="right">0</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl31" style="height: 12.75pt; border-top: medium none;" height="17">Denmark</td><td class="xl26" style="border-top: medium none; border-left: medium none;" align="right">19</td><td class="xl26" style="border-top: medium none; border-left: medium none;" align="right">13</td><td class="xl26" style="border-top: medium none; border-left: medium none;" align="right">0</td><td class="xl25" align="right">57</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl31" style="height: 12.75pt; border-top: medium none;" height="17">Czech Republic</td><td class="xl26" style="border-top: medium none; border-left: medium none;" align="right">9</td><td class="xl26" style="border-top: medium none; border-left: medium none;" align="right">0</td><td class="xl26" style="border-top: medium none; border-left: medium none;" align="right">2</td><td class="xl25" align="right">27</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl31" style="height: 12.75pt; border-top: medium none;" height="17">Hungary</td><td class="xl26" style="border-top: medium none; border-left: medium none;" align="right">398</td><td class="xl26" style="border-top: medium none; border-left: medium none;" align="right">0</td><td class="xl26" style="border-top: medium none; border-left: medium none;" align="right">4</td><td class="xl25" align="right">0</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl31" style="height: 12.75pt; border-top: medium none;" height="17">Switzerland</td><td class="xl26" style="border-top: medium none; border-left: medium none;" align="right">2</td><td class="xl26" style="border-top: medium none; border-left: medium none;" align="right">0</td><td class="xl26" style="border-top: medium none; border-left: medium none;" align="right">4</td><td class="xl25" align="right">9</td></tr></tbody></table>

Sheet where the data is:

<table style="width: 852px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 32pt;" width="43"> <col style="width: 114pt;" width="152"> <col style="width: 128pt;" width="170"> <col style="width: 58pt;" width="77"> <col style="width: 255pt;" width="340"> <col style="width: 53pt;" width="70"> <tbody><tr style="height: 12.75pt;" height="17"><td class="xl25" style="height: 12.75pt; width: 32pt;" width="43" height="17">Week</td><td class="xl25" style="width: 114pt;" width="152">Factory</td><td class="xl25" style="width: 128pt;" width="170">EM</td><td class="xl25" style="width: 58pt;" width="77">SKU</td><td class="xl25" style="width: 255pt;" width="340">SKU Description</td><td class="xl25" style="width: 53pt;" width="70">Changes?</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" align="right" height="17">5
</td><td>xyz</td><td>123</td><td align="right">SKU1</td><td>Stuff</td><td>NO</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" align="right" height="17">5</td><td>xyz</td><td>456</td><td align="right">SKU2
</td><td>Stuff
</td><td>NO</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" align="right" height="17">5</td><td>xyz</td><td>123</td><td align="right">SKU3</td><td>Stuff</td><td>NO</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" align="right" height="17">5</td><td>xyz</td><td>123</td><td align="right">SKU4</td><td>More Stuff
</td><td>YES</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" align="right" height="17">5</td><td>abc</td><td>666</td><td align="right">SKU5</td><td>Even More Stuff</td><td>YES</td></tr></tbody></table>
Cheers,

James

 

[GlennUK]

You're saying 2590 cells, but the formula is referencing 64271 cells ... please explain.

 

[Norie]

James

Isn't that formula running on a little more than 2590 cells?

Something like 64271 rows maybe?

 

[JamesW]

Sorry, yes it's 64271 rows but the actual sumproduct is repeated on 2590 cells (as in I have the same formula on 2590 cells looking at different references).

I can easily do this in VBA, but my boss insists that I don't use it... *sigh*

 

[GlennUK]

Can you do a concatenate in the data sheet? Is there actually 64271 rows of data?

 

[JamesW]

Hi Glenn, I guess I could do a concatenate, but then would have no idea how to change the formula.

Yes, there are 64271 rows (on one sheet, theres another sheet with about 30k on there - and more to come as the year progresses!).