Results 1 to 8 of 8

SUMPRODUCT Numbers only in a filtered range

Threaded View

  1. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: SUMPRODUCT Numbers only in a filtered range

    Found most of the cause of the problem... Replace the formula in File Library D2 with:

    =IFERROR(INDEX(Register!E:E,AGGREGATE(15,6,ROW(Register!$T$10:$T$200)/(Register!$T$10:$T$200=$D$1),ROWS(D$2:D2))),"")

    adjust the bits in red, but don't go mad... and in F2:

    =IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$2:$A$26)/($B$2:$B$26=$F$1),ROWS(F$2:F2))),"")

    it's still a bit laggy, but vastly improved. the reports sheet is PW protected, so I couldn't troublesheet it.

    I'm more than happy to take a look at it, if you wish. But I'd need access to it!!

    IMHO, you need to look at Dynamic Named Ranges as a way of improving formula performance. These enable Excel to auto-adjust ranges to suit data length. Also, move away from array formulae where possible. ABSOLUTELY away from ones referencing whole columns.

    And to repeat, NEVER use SP with whole columns. it evaluates as an array formula, all 1,048,576 rows, about 5 times for each cell that the formula is in. Also, you have NOW() in your sheet. This is volatile. Every time anything changes, it recalculates. Use whole columns with care and volatile functions as if they were dynamite... with caution.

    file returned. Filter at the yellow cells in J. Much faster... but should be much faster still.
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 05-07-2017, 02:53 PM
  2. [SOLVED] Extracting numbers from text in cell range and summing them up using SUMPRODUCT
    By Jakub2 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-14-2017, 07:41 AM
  3. Compare for consecutive row numbers in a filtered range
    By jomili in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-23-2016, 11:23 AM
  4. [SOLVED] Calculate SUBTOTAL of a SUMPRODUCT within a filtered range
    By Lucille Boshoff in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-15-2016, 08:45 AM
  5. [SOLVED] Excel VBA - Issue in Naming Filtered Range on a Filtered List.
    By Vinod Krishna.C in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-06-2014, 01:17 PM
  6. Random Selection of Row Numbers from a Filtered Range
    By Saarang84 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-14-2010, 09:07 AM
  7. Replies: 5
    Last Post: 02-28-2005, 01:51 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