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)...\