|
Sum product help needed with an extra variable please and thankyou
=(SUMPRODUCT(--('Cattle Mvts'!$E$29:'Cattle Mvts'!$E$41='Cattle
Budget'!D7),('Cattle Mvts'!$C$29:'Cattle Mvts'!$C$41)*'Feedlot Assumptions'!$B$3)) The existing formula basically says if date at D7 equals a date in the array E29 to E41 then multiply the corresponding figure in array C29 to C41 by B3. i now need to expand it to include the concept to do that if the contents of array B29 to B41 equal the contents of either A9 or A18, otherwise leave blank. Hope someone can help Anthony |
Hi Anthony,
Try this: =(SUMPRODUCT(--('Cattle Mvts'!$E$29:$E$41='Cattle Budget'!D7),('Cattle Mvts'!$C$29:$C$41)*'Feedlot Assumptions'!$B$3)) Regards, KL "Anthony" wrote in message ... =(SUMPRODUCT(--('Cattle Mvts'!$E$29:'Cattle Mvts'!$E$41='Cattle Budget'!D7),('Cattle Mvts'!$C$29:'Cattle Mvts'!$C$41)*'Feedlot Assumptions'!$B$3)) The existing formula basically says if date at D7 equals a date in the array E29 to E41 then multiply the corresponding figure in array C29 to C41 by B3. i now need to expand it to include the concept to do that if the contents of array B29 to B41 equal the contents of either A9 or A18, otherwise leave blank. Hope someone can help Anthony |
Hi KL
Could you check reply all I got was my formula relating to E and C cell arrays not the extra help I need for the B array Thanks anthony "KL" wrote: Hi Anthony, Try this: =(SUMPRODUCT(--('Cattle Mvts'!$E$29:$E$41='Cattle Budget'!D7),('Cattle Mvts'!$C$29:$C$41)*'Feedlot Assumptions'!$B$3)) Regards, KL "Anthony" wrote in message ... =(SUMPRODUCT(--('Cattle Mvts'!$E$29:'Cattle Mvts'!$E$41='Cattle Budget'!D7),('Cattle Mvts'!$C$29:'Cattle Mvts'!$C$41)*'Feedlot Assumptions'!$B$3)) The existing formula basically says if date at D7 equals a date in the array E29 to E41 then multiply the corresponding figure in array C29 to C41 by B3. i now need to expand it to include the concept to do that if the contents of array B29 to B41 equal the contents of either A9 or A18, otherwise leave blank. Hope someone can help Anthony |
=(SUMPRODUCT(--('Cattle Mvts'!$E$29:$E$41='Cattle Budget'!D7),--(('Cattle
Mvts'!$B$29:$B$41='Cattle Budget'!A9)+('Cattle Mvts'!$B$29:$B$41='Cattle Budget'!A18)),('Cattle Mvts'!$C$29:$C$41)*'Feedlot Assumptions'!$B$3)) -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Hi KL Could you check reply all I got was my formula relating to E and C cell arrays not the extra help I need for the B array Thanks anthony "KL" wrote: Hi Anthony, Try this: =(SUMPRODUCT(--('Cattle Mvts'!$E$29:$E$41='Cattle Budget'!D7),('Cattle Mvts'!$C$29:$C$41)*'Feedlot Assumptions'!$B$3)) Regards, KL "Anthony" wrote in message ... =(SUMPRODUCT(--('Cattle Mvts'!$E$29:'Cattle Mvts'!$E$41='Cattle Budget'!D7),('Cattle Mvts'!$C$29:'Cattle Mvts'!$C$41)*'Feedlot Assumptions'!$B$3)) The existing formula basically says if date at D7 equals a date in the array E29 to E41 then multiply the corresponding figure in array C29 to C41 by B3. i now need to expand it to include the concept to do that if the contents of array B29 to B41 equal the contents of either A9 or A18, otherwise leave blank. Hope someone can help Anthony |
sorry, how about this:
=SUMPRODUCT(--($E$29:$E$41=$D$7),($B$29:$B$41=$A$9)+($B$29:$B$41 =$A$18),$C$29:$C$41*$B$3) Regards, KL "Anthony" wrote in message ... Hi KL Could you check reply all I got was my formula relating to E and C cell arrays not the extra help I need for the B array Thanks anthony "KL" wrote: Hi Anthony, Try this: =(SUMPRODUCT(--('Cattle Mvts'!$E$29:$E$41='Cattle Budget'!D7),('Cattle Mvts'!$C$29:$C$41)*'Feedlot Assumptions'!$B$3)) Regards, KL "Anthony" wrote in message ... =(SUMPRODUCT(--('Cattle Mvts'!$E$29:'Cattle Mvts'!$E$41='Cattle Budget'!D7),('Cattle Mvts'!$C$29:'Cattle Mvts'!$C$41)*'Feedlot Assumptions'!$B$3)) The existing formula basically says if date at D7 equals a date in the array E29 to E41 then multiply the corresponding figure in array C29 to C41 by B3. i now need to expand it to include the concept to do that if the contents of array B29 to B41 equal the contents of either A9 or A18, otherwise leave blank. Hope someone can help Anthony |
Thanks bob
Having people like you that are willing to take the time help people like me acheive the impossible with excel anthony "Bob Phillips" wrote: =(SUMPRODUCT(--('Cattle Mvts'!$E$29:$E$41='Cattle Budget'!D7),--(('Cattle Mvts'!$B$29:$B$41='Cattle Budget'!A9)+('Cattle Mvts'!$B$29:$B$41='Cattle Budget'!A18)),('Cattle Mvts'!$C$29:$C$41)*'Feedlot Assumptions'!$B$3)) -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Hi KL Could you check reply all I got was my formula relating to E and C cell arrays not the extra help I need for the B array Thanks anthony "KL" wrote: Hi Anthony, Try this: =(SUMPRODUCT(--('Cattle Mvts'!$E$29:$E$41='Cattle Budget'!D7),('Cattle Mvts'!$C$29:$C$41)*'Feedlot Assumptions'!$B$3)) Regards, KL "Anthony" wrote in message ... =(SUMPRODUCT(--('Cattle Mvts'!$E$29:'Cattle Mvts'!$E$41='Cattle Budget'!D7),('Cattle Mvts'!$C$29:'Cattle Mvts'!$C$41)*'Feedlot Assumptions'!$B$3)) The existing formula basically says if date at D7 equals a date in the array E29 to E41 then multiply the corresponding figure in array C29 to C41 by B3. i now need to expand it to include the concept to do that if the contents of array B29 to B41 equal the contents of either A9 or A18, otherwise leave blank. Hope someone can help Anthony |
Thanks KL
My reply to bob also applies to you anthony "KL" wrote: sorry, how about this: =SUMPRODUCT(--($E$29:$E$41=$D$7),($B$29:$B$41=$A$9)+($B$29:$B$41 =$A$18),$C$29:$C$41*$B$3) Regards, KL "Anthony" wrote in message ... Hi KL Could you check reply all I got was my formula relating to E and C cell arrays not the extra help I need for the B array Thanks anthony "KL" wrote: Hi Anthony, Try this: =(SUMPRODUCT(--('Cattle Mvts'!$E$29:$E$41='Cattle Budget'!D7),('Cattle Mvts'!$C$29:$C$41)*'Feedlot Assumptions'!$B$3)) Regards, KL "Anthony" wrote in message ... =(SUMPRODUCT(--('Cattle Mvts'!$E$29:'Cattle Mvts'!$E$41='Cattle Budget'!D7),('Cattle Mvts'!$C$29:'Cattle Mvts'!$C$41)*'Feedlot Assumptions'!$B$3)) The existing formula basically says if date at D7 equals a date in the array E29 to E41 then multiply the corresponding figure in array C29 to C41 by B3. i now need to expand it to include the concept to do that if the contents of array B29 to B41 equal the contents of either A9 or A18, otherwise leave blank. Hope someone can help Anthony |
| All times are GMT +1. The time now is 02:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com