Hi all,
I am building a spreadsheet that summarises a large data table. For reasons I won't go into I can't use a pivot table to summarise the data so am using Excel's built-in array formulas such as SUMIFS, COUNTIFS etc. I also do not know the row ranges I will have to search, all I know is that data will be added / taken out of the large table.
Normally I would just use whole columns for the range references, e.g. =COUNTIF(AC:AC, AD5) instead of a more precise =COUNTIF(AC5:AC1000, AD5). I have noticed this affects performance to some extent but was wondering if anyone knows by how much and why? For instance if I use whole column refs, does Excel have to check every cell in the column (>1,000,000 rows)? Or does it "know" the data extents and not check beyond them? For instance if my data is in rows 1-1000, would it have the intelligence to stop at row 1000 or does it check all 1 million rows?
If the latter then I can see that performance could get really bad once a lot of formulas are entered, so should I avoid using whole columns as search terms where possible? What do other people do when they have to use array-type formulas?
Thanks in advance
-Rob
Bookmarks