| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 . |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 . |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to write an Or statement inside Sumproduct? | Excel Worksheet Functions | |||
| What's themost efficient way | Excel Worksheet Functions | |||
| Efficient linking | Excel Discussion (Misc queries) | |||
| IF and VLOOKUP - how efficient? | Excel Worksheet Functions | |||
| What is more efficient | Excel Discussion (Misc queries) | |||
Linear Mode
