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...!

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...!

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!

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...!