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

[KIGeorge]

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?

[FDibbins]

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.

[KIGeorge]

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

[XLent]

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:

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)

[KIGeorge]

[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:

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

[bebo021999]

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.