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

[Guest]

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!

 

[Guest]

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

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

Dave

 

[via135 via OfficeKB.com]

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

 

[Bernard Liengme]

Add (SUM) or COUNT?

=SUMPRODUCT(--(C1:C100)="X"),--(D1100="Y"),--(E1:E100<>"Z"))
Or if you prefer
=SUMPRODUCT((C1:C100)="X")*(D1100="Y")*(E1:E100<>"Z"))
The pair of symbols <> (less than followed by greater than) make the Excel
NOT EQUAL operator
best wishes