It increases the speed by reducing the size of the arrays that each
sumproduct formula calculates.
I certainly get what you're saying, but I'm using about 250 sumproduct
formulas for each of 6 groups (1500 total) with 5 criteria each.
Adding 12 formulas to minimize the range for the sumproduct formulas makes
little difference in the worst case scenario, and a lot of difference in the
best.
I have 10,000+ rows of data (so far). If I want to compare 6 time periods,
the sumproduct formulas only have to look at about 1,7000 rows for each time
period, instead each one looking at 10,000 rows. Of course I have to keep
the data sorted in a logical way to best reduce the number of rows in each
group.
For example, the user could define the report to compare 6 salespeople over
the the entire time, in which case the speed is not increased unless the
data is sorted on salesperson.
However, there is usually a limited time period specified for each group, so
as long as the data is sorted on time period (quarter), region, and class of
customer, the speed for most desired reports will be improved.
"Tom Ogilvy" wrote in message
...
Looks like an array formula - don't think you can speed up an array
formula
by using another array formula to make the first array formula do less
work.
If you don't think sumproduct is an array formula, think again.
you can add more conditions quite easily
And more processing time <g
--
Regards.
Tom Ogilvy
"daddylonglegs"
wrote in message
news:daddylonglegs.22k59n_1138809305.0206@excelfor um-nospam.com...
I'm not sure if it would actually speed things up for you but you could
find the first match for 2 conditions something like this
=MATCH(1,INDEX((A1:A1000="apple")*(B1:B1000="banan a"),0),0)
and the last with
=MATCH(2,INDEX(1/(A1:A1000="apple")*(B1:B1000="banana"),0))
you can add more conditions quite easily
--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile:
http://www.excelforum.com/member.php...o&userid=30486
View this thread:
http://www.excelforum.com/showthread...hreadid=507249