+ Reply to Thread
Results 1 to 4 of 4

Any alternatives to SUMIFS and COUNTIFS to speed up a workbook's calc time?

  1. #1
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    2016
    Posts
    624

    Any alternatives to SUMIFS and COUNTIFS to speed up a workbook's calc time?

    I've got a large workbook with about 10,000 rows and 58 columns. Most of the cells have static data, however a number of columns use formulas/functions which include SUMIFS, COUNTIFS, INDEX, and MATCH.

    (The purpose of the workbook is to evaluate a variety of trader's track records and determine which ones might contribute toward an optimal portfolio. By changing column B from 0 to 1 allows me to turn on or off a particular trader's history).

    The problem is that making any change in the workbook, requires about 12-14 seconds to compute.

    I've attached a small section of the workbook, highlighting some of the columns which include the formulas I believe are slowing down the workbook.

    One which I suspect might be the culprit is this:

    Please Login or Register  to view this content.
    The reason the array goes down 40,000 rows is so that I don't have to change the formula every time I add another 5,000 rows or so of data. Would it just complicate things if I counted the rows and used the ADDRESS function to tell the formula how many rows to search down?

    My computer is reasonably fast: 16Gig RAM and an AMD Ryzan 3 2200U CPU. (it doesn't have a SSD though. Could that slow down the workbook?).

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Any alternatives to SUMIFS and COUNTIFS to speed up a workbook's calc time?

    You can add the data in a table, then your formula will use the end of the table.
    In that case you have always all data in your database.

    Have you tried Pivot Table for this kind of work?
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,883

    Re: Any alternatives to SUMIFS and COUNTIFS to speed up a workbook's calc time?

    Firstly i don't understand your data structure. In your sheet some columns are blank. Also not mentioned in first row column wise name each columns what is indicate.


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,900

    Re: Any alternatives to SUMIFS and COUNTIFS to speed up a workbook's calc time?

    Indirect is Volatile function

    Maybe try at
    BL2
    =IF(AN2<>0,ROW(),BL1)

    BM3
    =IFERROR(IF(AN3<>0,0,AS3/MAX(AS2:INDEX(AS:AS,BL2))-1)*AM3,0)
    Attached Files Attached Files

+ 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. [SOLVED] Countifs, Averageifs, Sumifs for a closed workbook
    By jomili in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 04-06-2020, 04:01 PM
  2. Referencing separate workbook within SUMIFS / COUNTIFS
    By philfry in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-28-2019, 09:26 PM
  3. Speed up/Streamline countifs/sumifs code/formula
    By nickytraps in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-03-2018, 05:16 PM
  4. Replies: 1
    Last Post: 11-22-2016, 01:54 AM
  5. Array/Sumifs Alternatives?
    By bchilme in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-10-2014, 11:09 AM
  6. COUNTIFS and SUMIFS and AVERAGEIFS workbook linking
    By PJR in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-07-2014, 01:24 PM
  7. Loop alternatives or improvement for speed
    By TommyN in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-18-2013, 06:00 PM

Tags for this Thread

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