+ Reply to Thread
Results 1 to 8 of 8

SUMPRODUCT Numbers only in a filtered range

  1. #1
    Registered User
    Join Date
    02-05-2021
    Location
    Lancashire, UK
    MS-Off Ver
    365
    Posts
    3

    SUMPRODUCT Numbers only in a filtered range

    Hello,

    I am trying to obtain how many cells contain values greater than 20 in a range depending on whether it is filtered or not, but I think the command is also counting cells that do not have numbers. The cells in the range (column S) containing the numbers are generated using an =if formula to only display the figures when a condition is met in a relating cell, or nothing using "". The formula works counting numbers less than 20 used in another cell, but when I try to count entries greater than 20 the problem occurs.

    The formula I am using is as follows: =SUMPRODUCT(SUBTOTAL(3,OFFSET($S:$S,ROW($S:$S)-MIN(ROW($S:$S)),,1))*($S:$S>20)). where column S contains the figures.

    Can anyone help me with this one.

    Thank you.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: SUMPRODUCT Numbers only in a filtered range

    1. Unless you have 1,000,000+ rows, do not use SUMPRODUCT with whole column references. It will be slow.

    A sample sheet showing the problem would help hugely (see yellow banner - top of page) because I can not reproduce your problem.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    02-05-2021
    Location
    Lancashire, UK
    MS-Off Ver
    365
    Posts
    3

    Re: SUMPRODUCT Numbers only in a filtered range

    Thank you Glenn, good point about the column selection.

    I have attached the file, apologies for it being very amateurish, it's been a steep learning curve from leaving college to my first job, so there are probably loads of improvements on the sheet that can be done as it does take time sometimes to recalculate.

    The cell that is giving me the headache is P6.

    Thank you again for your help.
    Attached Files Attached Files

  4. #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 2403
    Posts
    43,900

    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

  5. #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 2403
    Posts
    43,900

    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

  6. #6
    Registered User
    Join Date
    02-05-2021
    Location
    Lancashire, UK
    MS-Off Ver
    365
    Posts
    3

    Re: SUMPRODUCT Numbers only in a filtered range

    Thank you for this Glenn, you have helped so much more than I expected. When I was building the register it got laggy when I started to add the conditional formatting so I think I will try and be careful with that in the future.

    I have sent you the password to unlock the sheets in a private message.

    Thanks again Glenn, you have been great.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: SUMPRODUCT Numbers only in a filtered range

    OK. I will take a look a bit later. Some shelves need drilling/screwing!!

    CF is a nightmare if applied to whole columns/rows. I haven't opened your sheet today, but as far as I remember.. there wasn't too much CF in it.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: SUMPRODUCT Numbers only in a filtered range

    There seems to be nothing of consequence in the PW protected sheet that would slow you up.

+ 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. 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