Is there a more efficient way to write this SUMPRODUCT

[enna49]

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

 

[Per Jessen]

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]

Hi

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

Thanks

Anne

 

[enna49]

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
Thanking you

 

[Per Jessen]

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