Results 1 to 8 of 8

SUMPRODUCT Numbers only in a filtered range

Threaded View

  1. #4
    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

    Hi.

    I have never seen a file with so little in it that is so laggy. I assumed it was the whole-column SUMPRODUCTS, but killing them in the salmon pink cells made little difference. I have fixed the two formulae in yellow.

    =SUMPRODUCT(--($S$10:$S$20<=20)*($S$10:$S$20<>""),SUBTOTAL(103,OFFSET($S$10:$S$20,ROW($S$10:$S$20)-ROW($S$10),0,1)))

    =SUMPRODUCT(--($S$10:$S$20>20)*($S$10:$S$20<>""),SUBTOTAL(103,OFFSET($S$10:$S$20,ROW($S$10:$S$20)-ROW($S$10),0,1)))

    Change the ranges, but don't go mad.

    I saved as an XLSX to get rid of the VBA to see if that helped, but no. There is something seriously wrong with your file. It takes a second to calculate the simplest of formulae. It should take microseconds. I will fiddle with it for a little longer...
    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