Closed Thread
Results 1 to 18 of 18

VBA to enter 50,000 SUMIF's faster?

  1. #1
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    VBA to enter 50,000 SUMIF's faster?

    I have a simple report I do every week, it only needs one formula;

    =SUMIF(A:A,A2,B:B)



    Which is fine, a nice simple formula. Takes no time at all to enter....until I copy it down 50,000+ rows, and it takes about 3-4 minutes to calculate it all making my computer a laggy space heater while it calculates...

    Entering absolute references instead of entire column references, SUMIF($A$2:$A$50000,A2,$B$2:$B$50000), does not speed it up in any noticeable way (actually it seemed a little slower)
    .
    Can this be done (significantly) faster with VBA, or a different method I haven't thought of? A 3.24% faster method is of no use to me, I'm looking for 50%+!

    If you need a sample just
    =RANDBETWEEN(10,99) in A1:A50000
    and
    =RANDBETWEEN(50,500000) in B1:B50000
    Then in C1
    =SUMIF(A:A,A1,B:B)
    And copy to the end

    that goes about twice as fast as my actual data, but it still crawls along pretty slowly.


    A warning on calculation speed, I don't know how much these details play into calculation speeds but if you are running on significantly lower hardware (like a Pentium M or something), it may take a really long time to calculate and excel might lock up, close any important spreadsheets just in case! I am on a Core i5 3320M with 8GB Ram and have Office Professional Plus 2010 64 bit.

    Thanks!
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: VBA to enter 50,000 SUMIF's faster?

    Pivot table, in the blink of an eye.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VBA to enter 50,000 SUMIF's faster?

    SUMIF is an array formula, it's doing individual math on every used row in your range. So if there are 50,000 rows of data, that's 50k calcs PER CELL. In small doses, that's fine

    But even with vba, it still takes a long time to do 50k cells with 50k calcs in each cell (2,500,000,000 calcs)

    Consider using a Pivot Table instead to give you the SUM of each unique value in your table, should be significantly faster for you.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: VBA to enter 50,000 SUMIF's faster?

    Unfortunately that won't work. There are a total of 4 columns, and it needs to remain in a upload-able format for our system, I don't think the system would be able to digest a pivot table. Maybe I can pivot just the two columns and do a VLOOKUP, but I don't know if that would be faster.

    I'm going home so I won't be able to reply on this until tomorrow morning, appreciate the reply and I will check again tomorrow!

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: VBA to enter 50,000 SUMIF's faster?

    What would be regarded as an uploadable format?

    I can create a pivot table with your sample formulas copied down colums A and B for 50000 rows in a split second.

    The pivot table simply consists of two columns, one for the ID (column A) and one for the sum of column B for each ID.

    There is a grand total row but that can easily be removed.
    If posting code please use code tags, see here.

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: VBA to enter 50,000 SUMIF's faster?

    Quote Originally Posted by Speshul View Post
    Maybe I can pivot just the two columns and do a VLOOKUP, but I don't know if that would be faster.
    It would be infinitely faster.

  7. #7
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: VBA to enter 50,000 SUMIF's faster?

    if your formulas are in column e maybe sort on column a then use this in e2
    =if(a2=a1,e1,if(a2=a3,SUMIF(A:A,A2,B:B),b2))
    and fill down
    Last edited by JosephP; 09-02-2014 at 05:50 PM.
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: VBA to enter 50,000 SUMIF's faster?

    Great suggestion, Joseph.

  9. #9
    Valued Forum Contributor
    Join Date
    03-21-2013
    Location
    cyberia
    MS-Off Ver
    Excel 2007
    Posts
    457

    Re: VBA to enter 50,000 SUMIF's faster?

    @Speshul

    How fast depends on whether you want the result to show the sumif formulas in each cell or just their resulting values.

    Entering formulas is, as you have found out, likely to be quite slow with a lot of rows. Although I do think the times you give could be speeded up somewhat (needs a bit of a jack-up) if you still require the formulas in each cell.

    If you want the entries in (say) Col E to be the values, VBA can do it a lot faster.

    On your test data suggestion (values of Randbetweens down to row 50,000) try this code and check how fast
    Please Login or Register  to view this content.

  10. #10
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: VBA to enter 50,000 SUMIF's faster?

    Quote Originally Posted by JosephP View Post
    if your formulas are in column e maybe sort on column a then use this in e2
    =if(a2=a1,e1,if(a2=a3,SUMIF(A:A,A2,B:B),b2))
    and fill down
    This solution works great for me. It is significantly faster (calculation 9% per second, instead of 1% per 4 seconds). I had not thought of making the sumif a condition, that significantly reduces the number of calculations the sheet has to calculate, makes perfect sense


    kalak, you are correct in your assumption that I do not need the formulas, only the result. Unfortunately your VBA gives me a type mismatch error on line 12
    u(i, 1) = d(a(i, 1))
    VBA arrays are still a magical mystery to me so I am not sure why the error is occurring. However the formula mentioned by JosephP will work fine.

    Thanks everyone for the help.
    Last edited by Speshul; 09-03-2014 at 08:19 AM.

  11. #11
    Valued Forum Contributor
    Join Date
    03-21-2013
    Location
    cyberia
    MS-Off Ver
    Excel 2007
    Posts
    457

    Re: VBA to enter 50,000 SUMIF's faster?

    @ Speshul,

    Good that you now have an adequate method and thanks for the feedback.

    Just to tidy up.
    The type mismatch error occurs if there are non-numeric values in Col B. i.e they can't be added.
    The most likely cause is non-numeric column headers. I only tested the code on your suggested test data in your post#1, which was non-numeric all the way down, without non-numeric headers. Hence no error.

    It took my average spec laptop about a quarter of a second to give the same (value) result as the formula approach for your 50000 rows test data.
    Allowing for non-numeric data is actually very easy. In red below
    Please Login or Register  to view this content.
    Incidentally, you can likely make the formula approach somewhat faster by having it consider only the used parts of columns A and B, rather than the entire columns.
    i.e. =SUMIF(A$2:A$50000,A2,B$2:B$50000) instead of your =SUMIF(A:A,A2,B:B)
    can make change manually or through VBA.

  12. #12
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,315

    Re: VBA to enter 50,000 SUMIF's faster?

    Just a quick thought.

    Since you are sorting it anyway - it should be even faster if you calculate the maximum number of repeats in column A using mode and match, and set the sumif to only look at that many cells down from the current row (to avoid it having to look at the whole range).

  13. #13
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,988

    Re: VBA to enter 50,000 SUMIF's faster?

    I still think the given solution in #5 of Norie (pivot table) did not get the attention it deserved.
    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.

  14. #14
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: VBA to enter 50,000 SUMIF's faster?

    Speshul

    You really should consider a pivot table.

    If you have a problem with the format you could always copy/paste special.

  15. #15
    Valued Forum Contributor
    Join Date
    03-21-2013
    Location
    cyberia
    MS-Off Ver
    Excel 2007
    Posts
    457

    Re: VBA to enter 50,000 SUMIF's faster?

    pivot table can be somewhat slow in larger datasets. if large enough sometimes doesn't seem to work at all
    OP was concerned (it seems) primarily about slowness
    So just run a fast(er) macro
    with OP's suggested test data in post#1
    If you need a sample just
    =RANDBETWEEN(10,99) in A1:A50000
    and
    =RANDBETWEEN(50,500000) in B1:B50000
    code below does, on my average spec laptop, the equivalent SUMIF calculations in well under 0.1 seconds.
    Please Login or Register  to view this content.
    How long would a pivot table take? (From evidence, not conjecture.)

  16. #16
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: VBA to enter 50,000 SUMIF's faster?

    I have no idea of how long it might take with the dataset/setup the OP actually has because I know very little about that dataset/setup.

    With the formulas the OP suggested in post #1, extended down to approx. 100,000 rows, a pivot table took a few seconds.

    Mind you if I had that amount of data I'd probably be thinking of moving to a database where a simple query could produce the required result.

  17. #17
    Registered User
    Join Date
    02-13-2019
    Location
    Perth, Australia
    MS-Off Ver
    2016
    Posts
    1

    Re: VBA to enter 50,000 SUMIF's faster?

    Applogies for asking a question on such an old thread :-)
    Yuor code: n = Cells(Rows.Count, 1).End(3).Row
    finds the last used cell in column 1 (A) - great. But what is the End(3) about - I can see it woks - but why?

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,350

    Re: VBA to enter 50,000 SUMIF's faster?

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Faster way to categorize data (Faster than nested-if)
    By excel1985 in forum Excel General
    Replies: 6
    Last Post: 03-05-2014, 07:00 AM
  2. Faster way to categorize data (Faster than nested-if)
    By excel1985 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-05-2014, 01:56 AM
  3. time:enter time faster for instance
    By darkbearpooh1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-15-2006, 12:19 PM
  4. enter doesn't work with sumif
    By Infrascanner in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-27-2006, 12:35 PM
  5. [SOLVED] Can faster CPU+larger/faster RAM significantly speed up recalulati
    By jmk_li in forum Excel General
    Replies: 2
    Last Post: 09-28-2005, 06:05 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