+ Reply to Thread
Results 1 to 3 of 3

Sumifs is really slow

  1. #1
    Forum Contributor
    Join Date
    06-08-2012
    Location
    Lagos, Nigeria
    MS-Off Ver
    Excel 2007, 2010, 2016
    Posts
    102

    Sumifs is really slow

    Dear Experts,

    I am working on a data set that has about 40k+ rows and 20 columns. This data will continue to grow till the end of the year.

    Naturally i used a sumifs to aggregate my data into the layout (see the summarized sheet) that i wanted to report on. However, i noticed that the sumifs formula was taking huge amount of time to calculate and had to resort first to turning off calculation mode and then click on the calculate button to update the sheet. i have done all what i think i know to make it faster but its not working out for me.

    Hence i am wondering if somebody can help:

    Take a look at my sumifs formula to see if i goofed it
    Help write a application.workfunction.sumifs version to see if this can speed up the process or
    simply have a better way i can use in aggregating this data so that i can continue with my project. (using pivot will add lots of other steps to the process and i am trying to avoid that.


    Please find attached a desensitized version of my data. Sumifs_ish.xlsx

    Cross Link: http://www.ozgrid.com/forum/showthread.php?t=197213

    Many thanks for your time,

    Kay

  2. #2
    Registered User
    Join Date
    08-26-2014
    Location
    Iran,tehran
    MS-Off Ver
    2010
    Posts
    10

    Re: Sumifs is really slow

    Dear kay007

    I think the best way is pivot.
    i can't understand why you avoid it
    lest's talk about it.
    i make it in 30 seconds:
    \1

    what is wrong with that?
    Attached Files Attached Files
    Last edited by hosna; 10-20-2015 at 04:51 AM.

  3. #3
    Forum Contributor
    Join Date
    06-08-2012
    Location
    Lagos, Nigeria
    MS-Off Ver
    Excel 2007, 2010, 2016
    Posts
    102

    Re: Sumifs is really slow

    Thanks Hosna. I skipped the use of pivots so that i will not have to write extra codes to clean up the data.

    I might eventually use the pivot if this formula is not working.

+ 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. sumifs slow
    By ammartino44 in forum Excel General
    Replies: 4
    Last Post: 08-05-2015, 08:23 AM
  2. [SOLVED] Looking for something simpler than SUMIFS - SUMIFS
    By YAbdelaal in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-27-2014, 10:38 AM
  3. Slow on transposed sumifs formula
    By eing in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-01-2014, 11:38 PM
  4. Macro is running real slow and makes navigating the worksheet really slow after execution.
    By MichWolverines in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-03-2013, 04:29 PM
  5. [SOLVED] slow computer (slow clipboard) breaks my macro
    By twilsonco in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-05-2013, 09:16 PM
  6. SUMIFS Puzzle - Trying to avoid adding multiple SUMIFS to get valid result
    By haldavid in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-09-2013, 03:42 PM
  7. [SOLVED] Sumifs, problem with the formula: =sumifs(c10:c200,<=today(),0)
    By Faustocruz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-08-2012, 04:26 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