+ Reply to Thread
Results 1 to 6 of 6

Sumproduct with subtotal with offset with multiple criteria - trying to "reversengineer"

  1. #1
    Registered User
    Join Date
    07-15-2019
    Location
    Sydney, Australia
    MS-Off Ver
    Office365
    Posts
    6

    Red face Sumproduct with subtotal with offset with multiple criteria - trying to "reversengineer"

    Hi All,

    my sales manager has been playing and usually I'm able to then take his formulas (which I usually only semi-fathom) and reverse engineer to actually do what he wants them to do.

    =SUMPRODUCT((SUBTOTAL(3,OFFSET($M$7:$M$19,ROW($M$7:$M$19)-MIN(ROW($M$7:$M$19)),,1)))*(($M$7:$M$19)=$M$5)*($D$7:$D$19))

    I need to change the part of the function that looks at $M$5. Currently it's a fixed entry of 'high' in another cell, looking at a column M entries.

    I need it to look for variables

    1. Forecast = columns G & H are both >/= 75%
    2. High Potential = The sum of G plus H = 120% to 149%
    3. Potential = The sum of G plus H = 52% to 119% Conditionally formatted light green

    I can get conditional formatting to work with
    =AND($G7>=75,$H7>=75); and
    =AND('Sum G H'!$A7>=120,'Sum G H'!$A7<=149)
    ...

    but can't work out how to integrate those into the sumproduct formula.

    Any charitable souls on here who can work some miracles for me?
    Attached Files Attached Files
    Last edited by KIGeorge; 06-15-2020 at 09:53 PM. Reason: upload revised sample

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Sumproduct with subtotal with offset with multiple criteria - trying to "reversenginee

    Rather than tell us what you are trying to do with a formula, explain to us exactly what you are trying to do. That may not even be the best way to do what you want.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    07-15-2019
    Location
    Sydney, Australia
    MS-Off Ver
    Office365
    Posts
    6

    Re: Sumproduct with subtotal with offset with multiple criteria - trying to "reversenginee

    Hi FDibbins,

    yes I'm pretty sure it probably could be done much more elegantly.

    The aim:

    there are 3 categories based on Customer Probability (G)/Our Probability (H)
    Values stipulated are:
    1. Forecast = columns G & H are both >/= 75%
    2. High Potential = The sum of G plus H = 120% to 149%
    3. Potential = The sum of G plus H = 52% to 119%

    It needs to 'add up' based on for each category that fulfills both columns

    In sample currently cells D4 (potential which would be 2. above) and E5 (Remaining Forecast which would be 1. above); haven't created Potential yet.

    But it also needs to subtotal, still reflecting forecast/high potential/potential when my manager filters by e.g. Factory

    So e.g. if columns G & H are both >=75% it should total the Amount column in E5 depending on which filters are or are not applied.

    Hope this makes sense.

    Thank you

  4. #4
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Sumproduct with subtotal with offset with multiple criteria - trying to "reversenginee

    first off, if your file stores real-date - remove it - re-post with masked values.

    in terms of your query, and assuming no other changes:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    note: given your ranges don't cover 0% I've assumed you cannot use net of total less 2 others to generate 3rd.

    on a final aside, your life (and calc performance) would probably benefit from

    1. storing the category field on the table
    2. storing an 'is filtered' field on the table (e.g. =SUBTOTAL(103,A3) copied down, can be hidden)

    with 1 + 2 in place you could then use basic SUMIFS which will be simpler (syntax wise), and non-volatile (i.e. no OFFSET requirement)

  5. #5
    Registered User
    Join Date
    07-15-2019
    Location
    Sydney, Australia
    MS-Off Ver
    Office365
    Posts
    6

    Re: Sumproduct with subtotal with offset with multiple criteria - trying to "reversenginee

    [QUOTE=XLent;5349847]first off, if your file stores real-date - remove it - re-post with masked values.

    in terms of your query, and assuming no other changes:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    note: given your ranges don't cover 0% I've assumed you cannot use net of total less 2 others to generate 3rd.


    Thank you very much! Definitely a step closer. Though I noticed that the 'Potential' didn't add up correctly and then it dawned on me why ... column M only gives Medium and High as that's the system export, whereas we are trying to reflect 3 categories (that I can't get the system export to give me).
    Is there a way more elegant way to tweak the formula so it doesn't refer to column M?
    I replaced M with G:H and the formula still works though that gave me double which I countered with a /2 at the end - though that's probably the 'pedestrian way' of doing it? :D

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Sumproduct with subtotal with offset with multiple criteria - trying to "reversenginee

    Quote Originally Posted by KIGeorge View Post
    Is there a way more elegant way to tweak the formula so it doesn't refer to column M?
    The formula does not refer to value in column M at all. It use column M to establish an ordered number list, i.e, {0,1,2,3,...,12}. Change column M to any other column, if needed.
    Quang PT

+ 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. Replies: 2
    Last Post: 07-24-2017, 02:19 AM
  3. [SOLVED] how to use VBA to write " SUMPRODUCT(SUBTOTAL(3,OFFSET"....
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-23-2016, 06:05 AM
  4. [SOLVED] "IF" and "SumProduct" with multiple criteria
    By JESSHOR60 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-04-2015, 07:00 PM
  5. Replies: 3
    Last Post: 04-16-2014, 10:00 AM
  6. [SOLVED] Find multiple "text" criteria and return as ""Yes" in Matrix
    By bertrand82 in forum Excel General
    Replies: 11
    Last Post: 04-30-2012, 09:20 AM
  7. Sumproduct-multiple criteria for same range "OR"
    By Deeds in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-18-2006, 11:50 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