+ Reply to Thread
Results 1 to 5 of 5

Nest SUMPRODUCT with SUBTOTAL with 3 criteria

  1. #1
    Forum Contributor
    Join Date
    06-20-2007
    Location
    Buckeye, AZ
    MS-Off Ver
    365
    Posts
    277

    Nest SUMPRODUCT with SUBTOTAL with 3 criteria

    In the attached workbook I'm trying to get the average, filtered, that match three criteria. I removed the opening "=" to preserve what I have so far.

    My best guess at this is "SUMPRODUCT(SUBTOTAL(101,OFFSET(M$1:M$2000,ROW(M$1:M$2000)-ROW(M$1),0,1,1)),--(H$1:H$2000=$A2),(M$2:M$2000,"<20"),(M$2:M$2000,">0"))".
    Any help would be greatly appreciated!
    Sick
    Attached Files Attached Files
    Last edited by sick stigma; 06-26-2019 at 01:21 PM.

  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 2406
    Posts
    44,557

    Re: Nest SUMPRODUCT with SUBTOTAL with 3 criteria

    Your sample is impossible to check manually, so I deleted thousands of rows. is this it??

    Total
    =SUMPRODUCT(($H$2:$H$20=A2)*($M$2:$M$20<20)*($M$2:$M$20>0)+0,SUBTOTAL(109,OFFSET($M$2:$M$20,ROW($M$2:$M$20)-MIN(ROW($M$2:$M$20)),0,1,1)))

    Count
    =SUMPRODUCT(($H$2:$H$20=A2)*($M$2:$M$20<20)*($M$2:$M$20>0)+0,SUBTOTAL(103,OFFSET($M$2:$M$20,ROW($M$2:$M$20)-MIN(ROW($M$2:$M$20)),0,1,1)))

    Average is then easily calculated...
    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

  3. #3
    Forum Contributor
    Join Date
    06-20-2007
    Location
    Buckeye, AZ
    MS-Off Ver
    365
    Posts
    277

    Re: Nest SUMPRODUCT with SUBTOTAL with 3 criteria

    Got it. Thank you! I'm getting an error but I'm sure the problem is in my data.
    Sick

  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 2406
    Posts
    44,557

    Re: Nest SUMPRODUCT with SUBTOTAL with 3 criteria

    Post a cut down sample containing the error. If you have text in there, it can muck it all up...

  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 2406
    Posts
    44,557

    Re: Nest SUMPRODUCT with SUBTOTAL with 3 criteria

    I'm away for the night, but will be back in about 18 hours (travelling... not 18 hours sleepy!!).

+ 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. [SOLVED] sumproduct subtotal offset with multiple criteria
    By reneevic in forum Excel General
    Replies: 10
    Last Post: 07-02-2019, 04:57 PM
  2. [SOLVED] Nest SumIFS inside subtotal
    By billrogers184 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-16-2017, 04:02 PM
  3. How to use subtotal in sumproduct with multiple criteria
    By Daniel Tou in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-11-2017, 05:11 AM
  4. [SOLVED] How to nest formula MATCH , ROW to display multiples criteria
    By Franky alta in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 12-13-2015, 10:20 PM
  5. Replies: 6
    Last Post: 08-23-2014, 10:14 PM
  6. [SOLVED] Trying to nest SUMPRODUCT
    By soberguy in forum Excel Formulas & Functions
    Replies: 25
    Last Post: 05-29-2014, 09:53 PM
  7. SubTotal function. How do I keep reuse Subtotal criteria.
    By davidthegolfer in forum Excel General
    Replies: 0
    Last Post: 10-10-2006, 03:28 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