Hi Greg,
I reviewed your last post and all the SP functions you have are correct. I'm
a little unclear how you want me to list each (SP1 to SP8) and the
condition-specific arguments. Isn't that what you have under the "Where"
section of your last post?

"Greg Wilson" wrote:

Summary of Previous Post:

As per my formula, the idea is to extract all arguments that are common to
each of the Sumproduct functions and list them only once as arguments in the
Sum function (array version). Also included in the Sum function is a nested
IF structure that returns the correct conditioin-specific argument from a
list of options.

In my previous post, I refered to the repeating arguments as (AAA) to (DDD)
and the condition-specific arguments as (EEE) to (GGG).

Current Strategy:

We need to confirm which arguments repeat for all conditions and which
arguments are condition-specific. As per my first formula, we will list the
arguments that repeat in the Sum function and multiply them together with an
IF structure that returns the correct condition-specific argument.

Current Logic Structu

The logic you describe is captured in the following formula:

=IF(Cond1, IF(Cond2, IF(Cond3, SP1, SP2), IF(Cond3, SP3, SP4)), IF(Cond2,
IF(Cond3, SP5, SP6), IF(Cond3, SP7, SP8)))

Translation:

If Cond1 and Cond2 and Cond3 Then SP1
If Cond1 and Cond2 and Not Cond3 Then SP2
If Cond1 and Not Cond2 and Cond3 Then SP3
If Cond1 and Not Cond2 and Not Cond3 Then SP4
If Not Cond1 and Cond2 and Cond3 Then SP5
If Not Cond1 and Cond2 and Not Cond3 Then SP6
If Not Cond1 and Not Cond2 and Cond3 Then SP7
If Not Cond1 and Not Cond2 and Not Cond3 Then SP8

Whe

SP1 is a Sumproduct function that returns all rows
SP2 returns all rows where M3 equals Column M on Details tab
SP3 returns all rows where Column L equals €œF€�
SP4 returns all rows where Column L equals €œF€� on Details tab And where M3
equals Column M on Details tab
SP5 returns all rows where B3 equals Column B on Details tab
SP6 returns all rows where B3 equals Column B on Details tab And then return
all rows where M3 equals Column M on Details tab
SP7 returns all rows where B3 equals Column B on Details tab And where
Column L equals €œF€�
SP8 returns all rows where B3 equals Column B on Details tab And where
Column L equals €œF€� And where M3 equals Column M on Details tab

*** Required from You:

1. To obtain the above results, please confirm the arguments that repeat
for each of SP1 to SP8. I assume these are still what I refered to as (AAA)
to (DDD) in my previous post.

2. List for each (SP1 to SP8) the condition-specific arguments that (when
multiplied by the repeating arguments) will return the desired results.

*** My Assignment:

Devise the formula. In other words, devise an IF structure that returns the
correct condition-specific argument from the different options and multiply
it together with the repeating arguments inside the Sum function.

Greg

Sorry if my last post was overly complicated. I was trying to clarify the
whole picture and probably achieved the opposite.

I was hoping not to have to take the time to figure out what set of
arguments are required to return each of the specified results listed in the
"Where" section of my last post. For instance, what set of arguments would be
required in a Sumproduct function to achieve this:

Return all rows where B3 equals Column B on Details tab And where
Column L equals €œF€� And where M3 equals Column M on Details tab

I was thinking something like this. These are just examples of arguments
from your original post that I copied and pasted:

SP1:
--(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0),--(Detail!$L$2:$L$49706="F")...

SP2: ...... etc.

You already know this. It will make it easier for me if you sort this out.

Greg

I understand what you mean now. Here are all the conditions with the
arguments below them:

SP1 is a Sumproduct function that returns all rows

--(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0)

If($B$3=€�All€�,$B$2:$B$49706<€�All€�)
If($I$3=€�W/ Blanket Orders€�,$L$2:$L$49706)€¦.will not equal anything since I
want all rows
If($M$3=€�All€�,$M$2:$M$49706<€�All€�)


SP2 returns all rows where M3 equals Column M on Details tab

--(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0)

If($B$3=€�All€�,$B$2:$B$49706<€�All€�)
If($I$3=€�W/ Blanket Orders€�,$L$2:$L$49706)€¦.will not equal anything since I
want all rows
If($M$2:$M$49706=LeadTimes!$M$3)


SP3 returns all rows where Column L equals €œF€�

--(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0)

If($B$3=€�All€�,$B$2:$B$49706<€�All€�)
If($I$3=€�No Blanket Orders€�,$L$2:$L$49706=€�F€�)
If($M$3=€�All€�,$M$2:$M$49706<€�All€�)


SP4 returns all rows where Column L equals €œF€� on Details tab And where M3
equals Column M on Details tab

--(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0)

If($B$3=€�All€�,$B$2:$B$49706<€�All€�)
If($I$3=€�No Blanket Orders€�,$L$2:$L$49706=€�F€�)
If($M$2:$M$49706=LeadTimes!$M$3)

SP5 returns all rows where B3 equals Column B on Details tab

--(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0)

If($B$2:$B$49706=LeadTimes!$B$3)
If($I$3=€�W/ Blanket Orders€�,$L$2:$L$49706)€¦.will not equal anything since I
want all rows
If($M$3=€�All€�,$M$2:$M$49706<€�All€�)


SP6 returns all rows where B3 equals Column B on Details tab And then return
all rows where M3 equals Column M on Details tab

--(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0)

If($B$2:$B$49706=LeadTimes!$B$3)
If($I$3=€�W/ Blanket Orders€�,$L$2:$L$49706)€¦.will not equal anything since I
want all rows
If($M$2:$M$49706=LeadTimes!$M$3)



SP7 returns all rows where B3 equals Column B on Details tab And where
Column L equals €œF€�

--(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0)

If($B$2:$B$49706=LeadTimes!$B$3)
If($I$3=€�No Blanket Orders€�,$L$2:$L$49706=€�F€�)
If($M$3=€�All€�,$M$2:$M$49706<€�All€�)


SP8 returns all rows where B3 equals Column B on Details tab And where
Column L equals €œF€� And where M3 equals Column M on Details tab

--(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0)

If($B$2:$B$49706=LeadTimes!$B$3)
If($I$3=€�No Blanket Orders€�,$L$2:$L$49706=€�F€�)
If($M$2:$M$49706=LeadTimes!$M$3)


The first part will be the same for each condition since I'll always be
looking at the month and year and the next two arguments. I have another
column on my worksheet that uses different criterias for the last two
arguments on the top section. I can just change those once the formula is
created for this current column we are working on. I don't want to confuse
you more so I will leave it at that.

Let me know if what I wrote about for the conditions makes sense to you.


"Greg Wilson" wrote:

Sorry if my last post was overly complicated. I was trying to clarify the
whole picture and probably achieved the opposite.

I was hoping not to have to take the time to figure out what set of
arguments are required to return each of the specified results listed in the
"Where" section of my last post. For instance, what set of arguments would be
required in a Sumproduct function to achieve this:

Return all rows where B3 equals Column B on Details tab And where
Column L equals €œF€� And where M3 equals Column M on Details tab

I was thinking something like this. These are just examples of arguments
from your original post that I copied and pasted:

SP1:
--(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0),--(Detail!$L$2:$L$49706="F")...

SP2: ...... etc.

You already know this. It will make it easier for me if you sort this out.

Greg

Theoretical Array Formula :

= SUM(A*B*C*D*IF(Cond1, E, J) * IF(Cond2, F, I) * IF(Cond3, G, H))

Conditions List:

Cond1 : $B$3 = "All"
Cond2 : $I$3 = "W/ BLANKET ORDERS"
Cond3 : $M$3 = "All"

Arguments List:

A : (MONTH(Detail!$J$2:$J$49706)=MONTH(B15))
B : (YEAR(Detail!$J$2:$J$49706)=YEAR(B15))
C : (Detail!$N$2:$N$49706=1)
D : (Detail!$Q$2:$Q$49706=0)

E : (Detail!$B$2:$B$49706<€�All€�)
F : (Detail!$L$2:$L$49706)
G : (Detail!$M$2:$M$49706<€�All€�)
H : (Detail!$M$2:$M$49706=LeadTimes!$M$3)
I : (Detail!$L$2:$L$49706=€�F€�)
J : (Detail!$B$2:$B$49706=LeadTimes!$B$3)

Putting it Together:

=
SUM((MONTH(Detail!$J$2:$J$49706)=MONTH(B15))*(YEAR (Detail!$J$2:$J$49706)=YEAR(B15))*(Detail!$N$2:$N$ 49706=1)*(Detail!$Q$2:$Q$49706=0)*IF(Detail!$B$3
= "All", (Detail!$B$2:$B$49706<€�All€�),
(Detail!$B$2:$B$49706=LeadTimes!$B$3)) * IF($I$3 = "W/ BLANKET ORDERS",
(Detail!$L$2:$L$49706), (Detail!$L$2:$L$49706=€�F€�)) * IF($M$3 = "All",
(Detail!$M$2:$M$49706<€�All€�), (Detail!$M$2:$M$49706=LeadTimes!$M$3)))

Notes:

1. Commit the formula with Ctrl + Shift + Enter
2. I took the liberty to add "Detail!" to some of the ranges you gave me. I
might have it wrong. Check the Arguments list.
3. As mentioned earlier, I expect the performance to be a bit disapointing.
4. I'm working on a project at this time and can't give it my undivided
attention. Have my own post: http://tinyurl.com/yuc362

Hope it works !!!

Greg

Hi Greg,

The code looks great but for some reason I'm getting the old #VALUE! error
when I put it into my spreadsheet. I checked, rechecked, and checked it again
for errors but can't seem to find any. Can you see anything that might be
causing this error? I'm committing the formula with Ctrl + Shift + Enter.
Here is what I put into my file:

=SUM((MONTH(Detail!$J$2:$J$12000)=MONTH(B15))*(YEA R(Detail!$J$2:$J$12000)=YEAR(B15))*(Detail!$N$2:$N $12000=1)*(Detail!$Q$2:$Q$12000=0)*IF($B$3="All",( Detail!$B$2:$B$12000<"All"),(Detail!$B$2:$B$12000 =LeadTimes!$B$3))*IF($I$3="W/
BLANKET
ORDERS",(Detail!$L$2:$L$12000),(Detail!$L$2:$L$120 00="F"))*IF($M$3="All",(Detail!$M$2:$M$12000<"All "),(Detail!$M$2:$M$12000=LeadTimes!$M$3)))

"Greg Wilson" wrote:

Theoretical Array Formula :

= SUM(A*B*C*D*IF(Cond1, E, J) * IF(Cond2, F, I) * IF(Cond3, G, H))

Conditions List:

Cond1 : $B$3 = "All"
Cond2 : $I$3 = "W/ BLANKET ORDERS"
Cond3 : $M$3 = "All"

Arguments List:

A : (MONTH(Detail!$J$2:$J$49706)=MONTH(B15))
B : (YEAR(Detail!$J$2:$J$49706)=YEAR(B15))
C : (Detail!$N$2:$N$49706=1)
D : (Detail!$Q$2:$Q$49706=0)

E : (Detail!$B$2:$B$49706<€�All€�)
F : (Detail!$L$2:$L$49706)
G : (Detail!$M$2:$M$49706<€�All€�)
H : (Detail!$M$2:$M$49706=LeadTimes!$M$3)
I : (Detail!$L$2:$L$49706=€�F€�)
J : (Detail!$B$2:$B$49706=LeadTimes!$B$3)

Putting it Together:

=
SUM((MONTH(Detail!$J$2:$J$49706)=MONTH(B15))*(YEAR (Detail!$J$2:$J$49706)=YEAR(B15))*(Detail!$N$2:$N$ 49706=1)*(Detail!$Q$2:$Q$49706=0)*IF(Detail!$B$3
= "All", (Detail!$B$2:$B$49706<€�All€�),
(Detail!$B$2:$B$49706=LeadTimes!$B$3)) * IF($I$3 = "W/ BLANKET ORDERS",
(Detail!$L$2:$L$49706), (Detail!$L$2:$L$49706=€�F€�)) * IF($M$3 = "All",
(Detail!$M$2:$M$49706<€�All€�), (Detail!$M$2:$M$49706=LeadTimes!$M$3)))

Notes:

1. Commit the formula with Ctrl + Shift + Enter
2. I took the liberty to add "Detail!" to some of the ranges you gave me. I
might have it wrong. Check the Arguments list.
3. As mentioned earlier, I expect the performance to be a bit disapointing.
4. I'm working on a project at this time and can't give it my undivided
attention. Have my own post: http://tinyurl.com/yuc362

Hope it works !!!

Greg

Hi Greg,
I found the problem. In this part of the formula is where it was causing the
error:

IF($I$3 = "W/ BLANKET ORDERS",
(Detail!$L$2:$L$49706), (Detail!$L$2:$L$49706=€�F€�))

Apparently the "true" condition needed more than just the cell range. I put
in this to replace it and call out all records since it will never = All

(Detail!$L$2:$L$49706<"All")

I haven't finished testing it yet but early results show that it works
perfectly. Once I test it a bit more I will give you a final result.

Thanks Greg!

"Greg Wilson" wrote:

Theoretical Array Formula :

= SUM(A*B*C*D*IF(Cond1, E, J) * IF(Cond2, F, I) * IF(Cond3, G, H))

Conditions List:

Cond1 : $B$3 = "All"
Cond2 : $I$3 = "W/ BLANKET ORDERS"
Cond3 : $M$3 = "All"

Arguments List:

A : (MONTH(Detail!$J$2:$J$49706)=MONTH(B15))
B : (YEAR(Detail!$J$2:$J$49706)=YEAR(B15))
C : (Detail!$N$2:$N$49706=1)
D : (Detail!$Q$2:$Q$49706=0)

E : (Detail!$B$2:$B$49706<€�All€�)
F : (Detail!$L$2:$L$49706)
G : (Detail!$M$2:$M$49706<€�All€�)
H : (Detail!$M$2:$M$49706=LeadTimes!$M$3)
I : (Detail!$L$2:$L$49706=€�F€�)
J : (Detail!$B$2:$B$49706=LeadTimes!$B$3)

Putting it Together:

=
SUM((MONTH(Detail!$J$2:$J$49706)=MONTH(B15))*(YEAR (Detail!$J$2:$J$49706)=YEAR(B15))*(Detail!$N$2:$N$ 49706=1)*(Detail!$Q$2:$Q$49706=0)*IF(Detail!$B$3
= "All", (Detail!$B$2:$B$49706<€�All€�),
(Detail!$B$2:$B$49706=LeadTimes!$B$3)) * IF($I$3 = "W/ BLANKET ORDERS",
(Detail!$L$2:$L$49706), (Detail!$L$2:$L$49706=€�F€�)) * IF($M$3 = "All",
(Detail!$M$2:$M$49706<€�All€�), (Detail!$M$2:$M$49706=LeadTimes!$M$3)))

Notes:

1. Commit the formula with Ctrl + Shift + Enter
2. I took the liberty to add "Detail!" to some of the ranges you gave me. I
might have it wrong. Check the Arguments list.
3. As mentioned earlier, I expect the performance to be a bit disapointing.
4. I'm working on a project at this time and can't give it my undivided
attention. Have my own post: http://tinyurl.com/yuc362

Hope it works !!!

Greg

Hi Greg,
Everything is working great now! I thank you very much for all your work you
put into this and helping me solve my problem. I greatly appreciate it. I
wish you luck with your current project.

I'm sure I'll see you on the board again.

Take Care
Larry

"Greg Wilson" wrote:

Theoretical Array Formula :

= SUM(A*B*C*D*IF(Cond1, E, J) * IF(Cond2, F, I) * IF(Cond3, G, H))

Conditions List:

Cond1 : $B$3 = "All"
Cond2 : $I$3 = "W/ BLANKET ORDERS"
Cond3 : $M$3 = "All"

Arguments List:

A : (MONTH(Detail!$J$2:$J$49706)=MONTH(B15))
B : (YEAR(Detail!$J$2:$J$49706)=YEAR(B15))
C : (Detail!$N$2:$N$49706=1)
D : (Detail!$Q$2:$Q$49706=0)

E : (Detail!$B$2:$B$49706<€�All€�)
F : (Detail!$L$2:$L$49706)
G : (Detail!$M$2:$M$49706<€�All€�)
H : (Detail!$M$2:$M$49706=LeadTimes!$M$3)
I : (Detail!$L$2:$L$49706=€�F€�)
J : (Detail!$B$2:$B$49706=LeadTimes!$B$3)

Putting it Together:

=
SUM((MONTH(Detail!$J$2:$J$49706)=MONTH(B15))*(YEAR (Detail!$J$2:$J$49706)=YEAR(B15))*(Detail!$N$2:$N$ 49706=1)*(Detail!$Q$2:$Q$49706=0)*IF(Detail!$B$3
= "All", (Detail!$B$2:$B$49706<€�All€�),
(Detail!$B$2:$B$49706=LeadTimes!$B$3)) * IF($I$3 = "W/ BLANKET ORDERS",
(Detail!$L$2:$L$49706), (Detail!$L$2:$L$49706=€�F€�)) * IF($M$3 = "All",
(Detail!$M$2:$M$49706<€�All€�), (Detail!$M$2:$M$49706=LeadTimes!$M$3)))

Notes:

1. Commit the formula with Ctrl + Shift + Enter
2. I took the liberty to add "Detail!" to some of the ranges you gave me. I
might have it wrong. Check the Arguments list.
3. As mentioned earlier, I expect the performance to be a bit disapointing.
4. I'm working on a project at this time and can't give it my undivided
attention. Have my own post: http://tinyurl.com/yuc362

Hope it works !!!

Greg

Thanks for the feedback. I didn't think it would work on first try. This sort
of thing is just too difficult for that. Glad you were able to figure it out.

All the best.

Greg

"Secret Squirrel" wrote:

Hi Greg,
Everything is working great now! I thank you very much for all your work you
put into this and helping me solve my problem. I greatly appreciate it. I
wish you luck with your current project.

I'm sure I'll see you on the board again.

Take Care
Larry

"Greg Wilson" wrote:

Theoretical Array Formula :

= SUM(A*B*C*D*IF(Cond1, E, J) * IF(Cond2, F, I) * IF(Cond3, G, H))

Conditions List:

Cond1 : $B$3 = "All"
Cond2 : $I$3 = "W/ BLANKET ORDERS"
Cond3 : $M$3 = "All"

Arguments List:

A : (MONTH(Detail!$J$2:$J$49706)=MONTH(B15))
B : (YEAR(Detail!$J$2:$J$49706)=YEAR(B15))
C : (Detail!$N$2:$N$49706=1)
D : (Detail!$Q$2:$Q$49706=0)

E : (Detail!$B$2:$B$49706<€�All€�)
F : (Detail!$L$2:$L$49706)
G : (Detail!$M$2:$M$49706<€�All€�)
H : (Detail!$M$2:$M$49706=LeadTimes!$M$3)
I : (Detail!$L$2:$L$49706=€�F€�)
J : (Detail!$B$2:$B$49706=LeadTimes!$B$3)

Putting it Together:

=
SUM((MONTH(Detail!$J$2:$J$49706)=MONTH(B15))*(YEAR (Detail!$J$2:$J$49706)=YEAR(B15))*(Detail!$N$2:$N$ 49706=1)*(Detail!$Q$2:$Q$49706=0)*IF(Detail!$B$3
= "All", (Detail!$B$2:$B$49706<€�All€�),
(Detail!$B$2:$B$49706=LeadTimes!$B$3)) * IF($I$3 = "W/ BLANKET ORDERS",
(Detail!$L$2:$L$49706), (Detail!$L$2:$L$49706=€�F€�)) * IF($M$3 = "All",
(Detail!$M$2:$M$49706<€�All€�), (Detail!$M$2:$M$49706=LeadTimes!$M$3)))

Notes:

1. Commit the formula with Ctrl + Shift + Enter
2. I took the liberty to add "Detail!" to some of the ranges you gave me. I
might have it wrong. Check the Arguments list.
3. As mentioned earlier, I expect the performance to be a bit disapointing.
4. I'm working on a project at this time and can't give it my undivided
attention. Have my own post: http://tinyurl.com/yuc362

Hope it works !!!

Greg