Fastest solution for functions that use variable amount of data (indirect...

[Jovica]

I have a lot SUMPRODUCT & SUMIF functions in my workbook. Since amount of data that is going to be used in this functions varies
(Data-values is imported via VBA from two xls files that have between 2000 and 90000 rows-amount of data in those files is changing daily) my question is :
Is it faster to put formula like this : =SUMPRODUCT((PROMGL!I3:I100000="023")*(PROMGL!K3:K100000=0),PROMGL!E3:E100000) to cover all
100.000 potential rows (even there are only few thousands from time to time) or it is faster to counta all rows of data and to use indirect
function for ranges in every sumproduct, sumif function. Of course there are around 70 to 100 sumproduct functions in this workbook.


...maybe the best solution is defined ranges, but since imported amount of data changes and I don't know how would that affect defined
range (also never used them) ... number of columns that have data never changes (7 columns)...\

[canapone]

Hi,

here you can find some good advices.

http://www.decisionmodels.com/optspeedk.htm

I'm not a real expert, but I would not use INDIRECT (volatile function) where it's possible to use SUMPRODUCT or faster SUMIF.

Just for sharing a first idea.

Regards
Hope it helps

[:) Sixthsense :)]

Don't use Indirect() Function.
Use Sumif instead of Sumproduct.
Think about Pivot Table.

[bebo021999]

Have you tried SUMIFS instead of SUMPRODUCT:
=SUMIFS(PROMGL!E3:E100000,PROMGL!I3:I100000,"023",PROMGL!K3:K100000,0)

[Jovica]

Thanks guys for advice about sumif didn't know that, but actually I was asking about the range problem. Range that I use in formula above mostly covers all the values with e.g. E3:E15000, but sometimes I have 40.000, or 80.000 rows of data.
Is it faster to just put 100.000 rows in every formula just to cover all possible scenarios or there is alternative. I suspect that using range of 100.000 cells(times 3 - in formula above) in almost 100 formulas is not very efficient.

[:) Sixthsense :)]

You can use whole column reference when the data range is unpredictable like increase/decrease.

[Jovica]

If it is more efficient to use whole columns...I'll try. Problem is that I don't see calculations and recalculations at home since my PC is pretty fast, but at work...every changes it takes 5-10 seconds with this workbook that I've been working on.
Also I am trying to use defined ranges in sumif, and it doesn't work (=OFFSET(PROMGL!$C$3,0,0,PROMGL!H6,1) - where H6 is countA) ... I don't know if they are fast enough to pursuit why the ref error.