+ Reply to Thread
Results 1 to 7 of 7

VBA to replicate Excel ranking formula which uses sumproduct and crashing Excel

  1. #1
    Forum Contributor
    Join Date
    09-29-2011
    Location
    Cape Town
    MS-Off Ver
    Excel 2010
    Posts
    106

    VBA to replicate Excel ranking formula which uses sumproduct and crashing Excel

    Hi, I have a formula which ranks values using a sumproduct. For example

    =SUMPRODUCT(($A$2:$A$5815=A2)*(FY2>$FY$2:$FY$5815))+1

    Problem is that it is very computational and its crashing my excel. Any chance somebody could assist with a VBA function or macro?

    I have attached an Excel file with the output. Column D is what I'm looking to "fix"

    Thanks a million!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    15,351

    Re: VBA to replicate Excel ranking formula which uses sumproduct and crashing Excel

    Hope you can adjust the code.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    09-29-2011
    Location
    Cape Town
    MS-Off Ver
    Excel 2010
    Posts
    106

    Re: VBA to replicate Excel ranking formula which uses sumproduct and crashing Excel

    Thanks for the feedback, the code works on a small data set but I have 150 000 rows...any thoughts on this?

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    15,351

    Re: VBA to replicate Excel ranking formula which uses sumproduct and crashing Excel

    How is it not working?

    Any error?

  5. #5
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: VBA to replicate Excel ranking formula which uses sumproduct and crashing Excel

    I think COUNTIFS will be better than sumproduct:
    =countifs($A$2:$A$5815, A2,$FY$2:$FY$5815, "<"&FY2)+1
    • Please remember to mark threads Solved with Thread Tools link at top of page.
    • Please use code tags when posting code: [code]Place your code here[/code]
    • Please read Forum Rules

  6. #6
    Forum Contributor
    Join Date
    09-29-2011
    Location
    Cape Town
    MS-Off Ver
    Excel 2010
    Posts
    106

    Re: VBA to replicate Excel ranking formula which uses sumproduct and crashing Excel

    No error just takes a very long time. Also the VBA isnt dynamic so had to move columns around to get it working. Thanks though

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    15,351

    Re: VBA to replicate Excel ranking formula which uses sumproduct and crashing Excel

    Try change to
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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