Array or SumProduct or other?

[TMK]

This is my issue:

Excel 2007:
Formula in question
=SUMPRODUCT((Unique!$E:$E>=C$2)*(Unique!$E:$E<D$2)*(Unique!$J:$J=$A3)*(Unique!$G:$G="Closed"))

or

{=SUM((Unique!$E:$E>=C$2)*(Unique!$E:$E<D$2)*(Unique!$J:$J=$A3)*(Unique!$G:$G="Closed"))}

Scenario: I have somewhere around 100 similar formulas traversing about 60K
unique values. When I hit calculate my AMD 64 x2 Processor hits 100% on both
processors making the machine virtually unusable until the calculation
completes. Is there a more efficient way to get at my data? Is there a more
efficient formula that I could/should be using?

Please let me know if you need more specifics about the data, but it's
really a basic count of instances of entries with conditions to be satisfied
in order to be counted.

TIA

Cheers!

 

[T. Valko]

Do you need to reference the entire columns? In Excel 2007 that's 1,048,576
cells per referenced range * 4 referenced ranges per formula * 100
formulas.

So, your formulas are processing 419,430,400 cells.

Use the smallest range that you can get away with.

See this for efficiency tips:

http://www.decisionmodels.com/

 

[Harlan Grove]

This is my issue:

Excel 2007:
Formula in question:
=SUMPRODUCT((Unique!$E:$E>=C$2)*(Unique!$E:$E<D$2)
*(Unique!$J:$J=$A3)*(Unique!$G:$G="Closed"))

or

{=SUM((Unique!$E:$E>=C$2)*(Unique!$E:$E<D$2)
*(Unique!$J:$J=$A3)*(Unique!$G:$G="Closed"))}

Scenario: I have somewhere around 100 similar formulas traversing
about 60K unique values. When I hit calculate my AMD 64 x2 Processor
hits 100% on both processors making the machine virtually unusable
until the calculation completes. Is there a more efficient way to
get at my data? Is there a more efficient formula that I
could/should be using?

....

I'll assume Unique!J:J is sorted in ascending order. If not, the basic
step in making anything like this more efficient is sorting into
convenient groups.

Limit your conditional counts to just the rows that matter. Try this
array formula.

=SUM(--(MMULT(--(INDEX(Unique!G:G,MATCH(TRUE,Unique!E:E>=C$2,0))
:INDEX(Unique!J:J,MATCH(TRUE,Unique!E:E>=D$2,0)-1)={"Closed","","",""}
&LEFT($A3,{0,0,0,32267})),{1;0;0;1})=2))

Better still, don't use entire column ranges. Yes, Excel 2007 LETS you
use them, but you've now discovered why that's not necessarily a good
thing.

 

[TMK]

Thanks guys, I was being lazy trying to keep my formula generic so I
wouldn't have to modify it if it grew outside of a static peramiter, but lost
site that I could still do that just with a smaller "buffer". Much faster now.

Thanks again.