+ Reply to Thread
Results 1 to 6 of 6

Performance of SUMIFS, COUNTIFs etc with large ranges

  1. #1
    Forum Contributor
    Join Date
    05-26-2004
    Location
    Halifax, UK
    MS-Off Ver
    Office 2016
    Posts
    246

    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

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,273

    Re: Performance of SUMIFS, COUNTIFs etc with large ranges

    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

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,104

    Re: Performance of SUMIFS, COUNTIFs etc with large ranges

    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.

  4. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Performance of SUMIFS, COUNTIFs etc with large ranges

    It's a very good question, the answer to which I used to believe was that, for all functions within the SUMIF(S), COUNTIF(S), AVERAGEIF(S) family, implicit detection of the last-used cell within the range passed was incorporated, thus meaning that referencing of entire columns within these functions could be made without detriment to calculation performance.

    If true, this would imply that these functions hold a nice advantage over others which perform similar tasks, e.g. SUMPRODUCT, AGGREGATE, and any function requiring CSE, which do not incorporate implicit detection of the last-used cell in the range passed, and so must process every single cell passed (in the case of an entire column more than one million).

    It appears that this may still be the case with SUMIF(S) and others within that family, though this excellent post by joeu2004 seems to suggest that COUNTIF and COUNTIFS do not benefit in the same way.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,273

    Re: Performance of SUMIFS, COUNTIFs etc with large ranges

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

    Pete

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,104

    Re: Performance of SUMIFS, COUNTIFs etc with large ranges

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Improving PivotTable Slicer Performance With Large Data Sets
    By ncalenti in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-17-2019, 05:27 AM
  2. SUMIFS/COUNTIFS for multiple conditions/ranges
    By BoudeyCall in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-31-2017, 04:35 PM
  3. SUMIFS/COUNTIFS for multiple conditions/ranges
    By BoudeyCall in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-09-2017, 09:11 PM
  4. Performance Issues on Large Spreadsheet
    By dipique in forum Excel General
    Replies: 10
    Last Post: 06-18-2014, 09:24 AM
  5. [SOLVED] AverageIFS (or even SUMIFS/COUNTIFS) on large data set
    By natetheblade in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-13-2013, 01:37 AM
  6. Performance issue on large spreadsheet
    By edwar368 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-14-2011, 12:44 PM
  7. Large data....slow excel performance..!!
    By Aryaa Dixit in forum Excel General
    Replies: 9
    Last Post: 01-07-2009, 03:39 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1