Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
take the green pill or take the SUMPRODUCT?
I have this function, bestowed upon me from beyond excel purgatory...
=SUMPRODUCT(--(Sheet2!$G$2:$G$65536=$E$7),--(Sheet2!$L$2:$L $65536=1),--(Sheet2!$L$2:$L$65536<=3999),Sheet2!$M$2:$M$65536) Column L is full of numbers from 0 to 9999, the second expression in the formula needs to call up all numbers in Column L from 1 to 3999. What if? What if... I still need 1 to 3999, but need to exclude two numbers, 2000 and 2010 from the set? Please, please...please....thanks for any assistance given...! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
take the green pill or take the SUMPRODUCT?
=SUMPRODUCT(--(Sheet2!$G$2:$G$65535=$E$7),--(Sheet2!$L$2:$L$65535=1),--(Sheet2!$L$2:$L$65535<=3999),
--(Sheet2!$L$2:$L$65535<2000),--(Sheet2!$L$2:$L$65535<2010),Sheet2!$M$2:$M$65535) but a dynamic range might work better her rather than huge ranges -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message oups.com... I have this function, bestowed upon me from beyond excel purgatory... =SUMPRODUCT(--(Sheet2!$G$2:$G$65536=$E$7),--(Sheet2!$L$2:$L $65536=1),--(Sheet2!$L$2:$L$65536<=3999),Sheet2!$M$2:$M$65536) Column L is full of numbers from 0 to 9999, the second expression in the formula needs to call up all numbers in Column L from 1 to 3999. What if? What if... I still need 1 to 3999, but need to exclude two numbers, 2000 and 2010 from the set? Please, please...please....thanks for any assistance given...! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
take the green pill or take the SUMPRODUCT?
On Jul 25, 10:54 am, "Bob Phillips" wrote:
=SUMPRODUCT(--(Sheet2!$G$2:$G$65535=$E$7),--(Sheet2!$L$2:$L$65535=1),--(Sh*eet2!$L$2:$L$65535<=3999), --(Sheet2!$L$2:$L$65535<2000),--(Sheet2!$L$2:$L$65535<2010),Sheet2!$M$2:$*M$65535 ) but a dynamic range might work better her rather than huge ranges -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message oups.com... I have this function, bestowed upon me from beyond excel purgatory... =SUMPRODUCT(--(Sheet2!$G$2:$G$65536=$E$7),--(Sheet2!$L$2:$L $65536=1),--(Sheet2!$L$2:$L$65536<=3999),Sheet2!$M$2:$M$65536) Column L is full of numbers from 0 to 9999, the second expression in the formula needs to call up all numbers in Column L from 1 to 3999. What if? What if... I still need 1 to 3999, but need to exclude two numbers, 2000 and 2010 from the set? Please, please...please....thanks for any assistance given...!- Hide quoted text - - Show quoted text - Many Thanks Bob, will look into the dynamic range for the future! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
take the green pill or take the SUMPRODUCT?
another way
=SUMPRODUCT(--(Sheet2!$G$2:$G$65536=$E$7),--(Sheet2!$L$2:$L $65536=1),--(Sheet2!$L$2:$L$65536<=3999),Sheet2!$M$2:$M$65536)- SUMPRODUCT(--(Sheet2!$G$2:$G$65536=$E$7),--or(Sheet2!$L$2:$L $65536=2000,Sheet2!$L$2:$L$65536=2010),Sheet2!$M$2 :$M$65536) " wrote: I have this function, bestowed upon me from beyond excel purgatory... =SUMPRODUCT(--(Sheet2!$G$2:$G$65536=$E$7),--(Sheet2!$L$2:$L $65536=1),--(Sheet2!$L$2:$L$65536<=3999),Sheet2!$M$2:$M$65536) Column L is full of numbers from 0 to 9999, the second expression in the formula needs to call up all numbers in Column L from 1 to 3999. What if? What if... I still need 1 to 3999, but need to exclude two numbers, 2000 and 2010 from the set? Please, please...please....thanks for any assistance given...! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
very green at this formula stuff? | Excel Worksheet Functions | |||
Turning green | Excel Worksheet Functions | |||
Green corner in cell?????? | Excel Discussion (Misc queries) | |||
Green Tick Marks | Excel Discussion (Misc queries) | |||
how 'come green isn't green? | Excel Discussion (Misc queries) |