Is there a more efficient way to write this SUMPRODUCT
 

Hi
I have tried to change this to make it more readable, but evey time I do
this I get an error. Is there a more efficient way to write this. It is
very Messy

What it is. if col C = b3 and col D = c3 and col E = d3 then SUM Col O on
sheet ATB etc AND if col C = B3 and col D = E3 and Col E does NOT = A then
SUM Col O on sheet ATB AND if Col C = B3 and Col D = E3 and Col E is BLANK
then MINUS Col O on sheet ATB

=SUMPRODUCT(--('ATB DEC 2009'!$C$2:$C$5000=$B$3)*--('ATB DEC
2009'!$D$2:$D$5000=$C$3)*--('ATB DEC 2009'!$E$2:$E$5000=$D$3)*('ATB DEC
2009'!$O$2:$O$5000))
+SUMPRODUCT(--('ATB DEC 2009'!$C$2:$C$5000=$B$3)*--('ATB DEC
2009'!$D$2:$D$5000=$E$3),--('ATB DEC 2009'!$E$2:$E$5000<"A")*('ATB DEC
2009'!$O$2:$O$5000))
-SUMPRODUCT(--('ATB DEC 2009'!$C$2:$C$5000=$B$3)*--('ATB DEC
2009'!$D$2:$D$5000=$E$3)*--('ATB DEC 2009'!$E$2:$E$5000="")*('ATB DEC
2009'!$O$2:$O$5000))
Thank you

Is there a more efficient way to write this SUMPRODUCT
 

Hi

I assume that you only want to sum on Col O if *all* conditions are true.

Try this:

=SUMPRODUCT(--('ATB DEC 2009'!$C$2:$C$5000=$B$3),--('ATB DEC
2009'!$D$2:$D$5000=$C$3),--('ATB DEC 2009'!$E$2:$E$5000=$D$3),--('ATB DEC
2009'!$E$2:$E$5000<"A"),--('ATB DEC 2009'!$E$2:$E$5000<""),'ATB DEC
2009'!$O$2:$O$5000)

Regards,
Per

"enna49" skrev i meddelelsen
...
Hi
I have tried to change this to make it more readable, but evey time I do
this I get an error. Is there a more efficient way to write this. It is
very Messy

What it is. if col C = b3 and col D = c3 and col E = d3 then SUM Col O on
sheet ATB etc AND if col C = B3 and col D = E3 and Col E does NOT = A then
SUM Col O on sheet ATB AND if Col C = B3 and Col D = E3 and Col E is BLANK
then MINUS Col O on sheet ATB

=SUMPRODUCT(--('ATB DEC 2009'!$C$2:$C$5000=$B$3)*--('ATB DEC
2009'!$D$2:$D$5000=$C$3)*--('ATB DEC 2009'!$E$2:$E$5000=$D$3)*('ATB DEC
2009'!$O$2:$O$5000))
+SUMPRODUCT(--('ATB DEC 2009'!$C$2:$C$5000=$B$3)*--('ATB DEC
2009'!$D$2:$D$5000=$E$3),--('ATB DEC 2009'!$E$2:$E$5000<"A")*('ATB DEC
2009'!$O$2:$O$5000))
-SUMPRODUCT(--('ATB DEC 2009'!$C$2:$C$5000=$B$3)*--('ATB DEC
2009'!$D$2:$D$5000=$E$3)*--('ATB DEC 2009'!$E$2:$E$5000="")*('ATB DEC
2009'!$O$2:$O$5000))
Thank you

Is there a more efficient way to write this SUMPRODUCT
 

Hi

Yes that is correct. Only require to SUM Col O if all else is met.

Thanks

Anne

"Per Jessen" wrote:

Hi

I assume that you only want to sum on Col O if *all* conditions are true.

Try this:

=SUMPRODUCT(--('ATB DEC 2009'!$C$2:$C$5000=$B$3),--('ATB DEC
2009'!$D$2:$D$5000=$C$3),--('ATB DEC 2009'!$E$2:$E$5000=$D$3),--('ATB DEC
2009'!$E$2:$E$5000<"A"),--('ATB DEC 2009'!$E$2:$E$5000<""),'ATB DEC
2009'!$O$2:$O$5000)

Regards,
Per

"enna49" skrev i meddelelsen
...
Hi
I have tried to change this to make it more readable, but evey time I do
this I get an error. Is there a more efficient way to write this. It is
very Messy

What it is. if col C = b3 and col D = c3 and col E = d3 then SUM Col O on
sheet ATB etc AND if col C = B3 and col D = E3 and Col E does NOT = A then
SUM Col O on sheet ATB AND if Col C = B3 and Col D = E3 and Col E is BLANK
then MINUS Col O on sheet ATB

=SUMPRODUCT(--('ATB DEC 2009'!$C$2:$C$5000=$B$3)*--('ATB DEC
2009'!$D$2:$D$5000=$C$3)*--('ATB DEC 2009'!$E$2:$E$5000=$D$3)*('ATB DEC
2009'!$O$2:$O$5000))
+SUMPRODUCT(--('ATB DEC 2009'!$C$2:$C$5000=$B$3)*--('ATB DEC
2009'!$D$2:$D$5000=$E$3),--('ATB DEC 2009'!$E$2:$E$5000<"A")*('ATB DEC
2009'!$O$2:$O$5000))
-SUMPRODUCT(--('ATB DEC 2009'!$C$2:$C$5000=$B$3)*--('ATB DEC
2009'!$D$2:$D$5000=$E$3)*--('ATB DEC 2009'!$E$2:$E$5000="")*('ATB DEC
2009'!$O$2:$O$5000))
Thank you

.

Is there a more efficient way to write this SUMPRODUCT
 

Hi

Thank you for that, it works well if all are additions, but what I require
si to subtract the last statement, how do I do this.

-SUMPRODUCT(--('ATB DEC 2009'!$C$2:$C$5000=$B$3)*--('ATB DEC
2009'!$D$2:$D$5000=$E$3)*--('ATB DEC 2009'!$E$2:$E$5000="")*('ATB DEC
2009'!$O$2:$O$5000))

Thanking you


"enna49" wrote:

Hi

Yes that is correct. Only require to SUM Col O if all else is met.

Thanks

Anne

"Per Jessen" wrote:

Hi

I assume that you only want to sum on Col O if *all* conditions are true.

Try this:

=SUMPRODUCT(--('ATB DEC 2009'!$C$2:$C$5000=$B$3),--('ATB DEC
2009'!$D$2:$D$5000=$C$3),--('ATB DEC 2009'!$E$2:$E$5000=$D$3),--('ATB DEC
2009'!$E$2:$E$5000<"A"),--('ATB DEC 2009'!$E$2:$E$5000<""),'ATB DEC
2009'!$O$2:$O$5000)

Regards,
Per

"enna49" skrev i meddelelsen
...
Hi
I have tried to change this to make it more readable, but evey time I do
this I get an error. Is there a more efficient way to write this. It is
very Messy

What it is. if col C = b3 and col D = c3 and col E = d3 then SUM Col O on
sheet ATB etc AND if col C = B3 and col D = E3 and Col E does NOT = A then
SUM Col O on sheet ATB AND if Col C = B3 and Col D = E3 and Col E is BLANK
then MINUS Col O on sheet ATB

=SUMPRODUCT(--('ATB DEC 2009'!$C$2:$C$5000=$B$3)*--('ATB DEC
2009'!$D$2:$D$5000=$C$3)*--('ATB DEC 2009'!$E$2:$E$5000=$D$3)*('ATB DEC
2009'!$O$2:$O$5000))
+SUMPRODUCT(--('ATB DEC 2009'!$C$2:$C$5000=$B$3)*--('ATB DEC
2009'!$D$2:$D$5000=$E$3),--('ATB DEC 2009'!$E$2:$E$5000<"A")*('ATB DEC
2009'!$O$2:$O$5000))
-SUMPRODUCT(--('ATB DEC 2009'!$C$2:$C$5000=$B$3)*--('ATB DEC
2009'!$D$2:$D$5000=$E$3)*--('ATB DEC 2009'!$E$2:$E$5000="")*('ATB DEC
2009'!$O$2:$O$5000))
Thank you

.

Is there a more efficient way to write this SUMPRODUCT
 

Hi

Well, your statement subtract E:E="", in my statement, I only add where
E:E<"", so you should get the correct result.

Only if $D$3 condition cell has no value, the subtraction part will come
into action, further if $D$3 is *always* < "A" then this condition can be
left out too, and your formula can be reduced to this:

=SUMPRODUCT(--(€˜ATB DEC 2009!$C$2:$C$5000=$B$3),--(€˜ATB DEC 2009!$D$2:$D$5000=$C$3),--(€˜ATB
DEC 2009!$E$2:$E$5000=$D$3),ATB DEC 2009!$O$2:$O$5000)

I have done a little testing comparing your original formula with the above,
and they return equal results...

Best regards,
Per

"enna49" skrev i meddelelsen
...
Hi

Thank you for that, it works well if all are additions, but what I require
si to subtract the last statement, how do I do this.

-SUMPRODUCT(--('ATB DEC 2009'!$C$2:$C$5000=$B$3)*--('ATB DEC
2009'!$D$2:$D$5000=$E$3)*--('ATB DEC 2009'!$E$2:$E$5000="")*('ATB DEC
2009'!$O$2:$O$5000))

Thanking you


"enna49" wrote:

Hi

Yes that is correct. Only require to SUM Col O if all else is met.

Thanks

Anne

"Per Jessen" wrote:

Hi

I assume that you only want to sum on Col O if *all* conditions are
true.

Try this:

=SUMPRODUCT(--('ATB DEC 2009'!$C$2:$C$5000=$B$3),--('ATB DEC
2009'!$D$2:$D$5000=$C$3),--('ATB DEC 2009'!$E$2:$E$5000=$D$3),--('ATB
DEC
2009'!$E$2:$E$5000<"A"),--('ATB DEC 2009'!$E$2:$E$5000<""),'ATB DEC
2009'!$O$2:$O$5000)

Regards,
Per

"enna49" skrev i meddelelsen
...
Hi
I have tried to change this to make it more readable, but evey time I
do
this I get an error. Is there a more efficient way to write this.
It is
very Messy

What it is. if col C = b3 and col D = c3 and col E = d3 then SUM Col
O on
sheet ATB etc AND if col C = B3 and col D = E3 and Col E does NOT = A
then
SUM Col O on sheet ATB AND if Col C = B3 and Col D = E3 and Col E is
BLANK
then MINUS Col O on sheet ATB

=SUMPRODUCT(--('ATB DEC 2009'!$C$2:$C$5000=$B$3)*--('ATB DEC
2009'!$D$2:$D$5000=$C$3)*--('ATB DEC 2009'!$E$2:$E$5000=$D$3)*('ATB
DEC
2009'!$O$2:$O$5000))
+SUMPRODUCT(--('ATB DEC 2009'!$C$2:$C$5000=$B$3)*--('ATB DEC
2009'!$D$2:$D$5000=$E$3),--('ATB DEC 2009'!$E$2:$E$5000<"A")*('ATB
DEC
2009'!$O$2:$O$5000))
-SUMPRODUCT(--('ATB DEC 2009'!$C$2:$C$5000=$B$3)*--('ATB DEC
2009'!$D$2:$D$5000=$E$3)*--('ATB DEC 2009'!$E$2:$E$5000="")*('ATB DEC
2009'!$O$2:$O$5000))
Thank you

.