Performance of SUMIFS, COUNTIFs etc with large ranges

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

COUNTIF/IFS and SUMIF/IFS etc. do have the intelligence to search only the used rows, so there should be no drop-off in performance. However, you should check what the used range is by doing CTRL-End - if the cursor goes way beyond where your data ends, then you need to delete the empty rows and save the file.

These type of functions are not usually classed as "array" functions, as they do not need to be entered using Ctrl-Shift-Enter. If you are using array functions (and this includes SUMPRODUCT), then you do need to be careful about the ranges you use, as they will check every cell in the range, regardless.

Hope this helps.

Pete

It seems to generally agreed that you can use full columns with SUMIFS/COUNTIFS but should be avoided if using SUMPRODUCT where defined ranges are the rule.

A compromise is the use of dynamic named ranges where the range is adjusted as rows are added/deleted.

Try to avoid use of INDIRECT/OFFSET which are volatile and SEARCH using array formulae on large volumes will impact performance.

Consider using "helper" columns which can mitigate the use of complex formula.

It's interesting that joeu2004 uses the range A2:A1048576 in that post, rather than A:A, which I would normally use.

Pete

I have just run joeu2004's macro and found there is NO significant difference between the two COUNTIFS formula. ??!!!