IF THIS AND THIS BUT NOT THIS!?!? HELP!
 

I would like to set up a formula that would read something like... add
c1:c100 if they equal X and d1:d100=Y but e1:e100 does not equal Z. I am
familiar with SUMPRODUCT to get the first two parts of the equation, but
don't know how to make it do a "not this". Any help would be VERY MUCH
appreciated!! Thanks!

IF THIS AND THIS BUT NOT THIS!?!? HELP!
 

Try something like
=IF(AND(SUM(C1:C100)=X,SUM(D1:D100)=Y,FALSE(SUM(E1 :E100)=Z)),SUM(C1:C100)+SUM(D1:D100),"")

There may be a more concise way to do that, but I think it's what you're
looking for.

Dave
--
Brevity is the soul of wit.


"Leslie M" wrote:

I would like to set up a formula that would read something like... add
c1:c100 if they equal X and d1:d100=Y but e1:e100 does not equal Z. I am
familiar with SUMPRODUCT to get the first two parts of the equation, but
don't know how to make it do a "not this". Any help would be VERY MUCH
appreciated!! Thanks!

IF THIS AND THIS BUT NOT THIS!?!? HELP!
 

may be
=SUMPRODUCT(--(C1:C5="X"),--(D1:D5="Y"),--(E1:E5<"Z"),--(F1:F5))
-via135

Leslie M wrote:
I would like to set up a formula that would read something like... add
c1:c100 if they equal X and d1:d100=Y but e1:e100 does not equal Z. I am
familiar with SUMPRODUCT to get the first two parts of the equation, but
don't know how to make it do a "not this". Any help would be VERY MUCH
appreciated!! Thanks!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200611/1

IF THIS AND THIS BUT NOT THIS!?!? HELP!
 

Add (SUM) or COUNT?

=SUMPRODUCT(--(C1:C100)="X"),--(D1:D100="Y"),--(E1:E100<"Z"))
Or if you prefer
=SUMPRODUCT((C1:C100)="X")*(D1:D100="Y")*(E1:E100< "Z"))
The pair of symbols < (less than followed by greater than) make the Excel
NOT EQUAL operator
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email


"Leslie M" wrote in message
...
I would like to set up a formula that would read something like... add
c1:c100 if they equal X and d1:d100=Y but e1:e100 does not equal Z. I am
familiar with SUMPRODUCT to get the first two parts of the equation, but
don't know how to make it do a "not this". Any help would be VERY MUCH
appreciated!! Thanks!