Sumproduct Multiple <> Conditions

[undrline via OfficeKB.com]

So, normally I would use this format for my conditional formulas:

=SUMPRODUCT(--((X2:X500="foo")+(X2:X500="oof")>0),--(Y2:Y500<100),(Z2:Z500))

Which gets me the sum of column Z, if column Y is less than 100, and column X
is "foo" OR "oof"

I'm keep hitting a problem, though, with one type of conditional . . . what
if I want every thing that is NEITHER "foo" NOR "oof" ?

=SUMPRODUCT(--((X2:X500<>"foo")+(X2:X500<>"oof")>0),--(Y2:Y500<100),(Z2:Z500))


is wrong . . . because it's telling it NOT "foo" OR NOT "oof" . . .

thank you for any help.

 

[Dave Peterson]

How about:

=SUMPRODUCT(--(X2:X500<>"foo"),--(X2:X500<>"oof"),--(Y2:Y500<100),(Z2:Z500))

And I'd be careful with your original formula. I think you're missing a pair of
()'s.

=SUMPRODUCT(--((X2:X500="foo")+(X2:X500="oof")>0),--(Y2:Y500<100),(Z2:Z500))
should be:
=SUMPRODUCT(--(((X2:X500="foo")+(X2:X500="oof"))>0),--(Y2:Y500<100),(Z2:Z500))

(foo+oof)>0, right?

 

[Guest]

Try this:

=SUMPRODUCT((X2:X500<>"foo")*(X2:X500<>"oof")*(Y2:Y500<100)*(Z2:Z500))

 

[undrline via OfficeKB.com]

Hmm, normally pointing to two conditions in the same column is bad . . . but
maybe the <> changes it . . . I think I found a different solution, though.

About the >0 . . . it's what I was told to do in this thread:
http://www.officekb.com/Uwe/Forum.a...e-a-function-Countif-that-operates-like-Sumif


Here's why the >0 doesn't make sense to me, and the formula seems to always
work without it at all:
My understanding is that an array is turned into a set of ones-and-zeros,
representing true or false, which are then multiplied against the
corresponding rows in the other arrays . . . if any zeros appear, then the
whole thing is zero, and only if every array is one, for that same row, will
they stay one. If one was not a conditional array with a "--" then one
multiplied by that number is that number. Then, the new (resulting) array is
totalled, either giving a count if all were conditional arrays, or a sum, if
it had that one non-conditional. So, in order to have two options from one
column, it might confuse the function as to what row it's testing in the
arrays: so the thought is that one cell would not hold two separate values.
It would be one or the other. Therefore, one of the multiple conditions
("foo" or "oof") has to be false for that cell, or zero. So, the sum of the
two conditions should be 0+1, 0+0, or 1+0. I don't see any way that you
would ever have a negative number. Maybe empty cells? But empty cells are
an empty string value in Excel by default, and would still evaluate to true
or false. Of the three options, there is one that would throws a fit when
you wrap parenthesis around the whole thing . . . (0+0)>0 . . . you're
evaluating false>false, and it doesn't want to evaluate to false. Every time
I've tried it, it doesn't seem to work.

How about:

=SUMPRODUCT(--(X2:X500<>"foo"),--(X2:X500<>"oof"),--(Y2:Y500<100),(Z2:Z500))

And I'd be careful with your original formula. I think you're missing a pair of
()'s.

=SUMPRODUCT(--((X2:X500="foo")+(X2:X500="oof")>0),--(Y2:Y500<100),(Z2:Z500))
should be:
=SUMPRODUCT(--(((X2:X500="foo")+(X2:X500="oof"))>0),--(Y2:Y500<100),(Z2:Z500))

(foo+oof)>0, right?

So, normally I would use this format for my conditional formulas:

[quoted text clipped - 14 lines]

 

[undrline via OfficeKB.com]

I really prefer the double unary operator, mostly because it makes it easier
to read, and discern conditions from sums. But using * as AND, and + as OR
makes sense:

I think this would work:
=SUMPRODUCT(--((X2:X500<>"foo")*(X2:X500<>"oof")),--(Y2:Y500<100),(Z2:Z500))

Thanks.

Thank you both for helping out.

Try this:

So, normally I would use this format for my conditional formulas:

[quoted text clipped - 11 lines]

thank you for any help.