My old formula worked fine for finding the number based on two criteria,
=SUMPRODUCT(-(J124:J1111=0),-(AV124:AV1111="yes"))

Now I want to add a third criteria,
=SUMPRODUCT(--(AV124:AV1111="yes"),--(E123:E1111"Half Off"),--(J124:J1111=0),J124:J1111)

But now I get a #VALUE error....any ideas?

--(E123:E1111"Half Off")

Unless those are typos:

That array isn't the same size as the others and you're missing the = sign.

Biff

"Mike B" wrote in message
...

My old formula worked fine for finding the number based on two
criteria,
=SUMPRODUCT(-(J124:J1111=0),-(AV124:AV1111="yes"))

Now I want to add a third criteria,
=SUMPRODUCT(--(AV124:AV1111="yes"),--(E123:E1111"Half
Off"),--(J124:J1111=0),J124:J1111)

But now I get a #VALUE error....any ideas?


--
Mike B

First, all your ranges need to be the same size. Probably need to change
E123 to E124.

Second, since you're multiplying by the range J124:J1111, but only if
J124:J1111=0, you can replace the whole formula with

0

In article ,
Mike B wrote:

My old formula worked fine for finding the number based on two
criteria,
=SUMPRODUCT(-(J124:J1111=0),-(AV124:AV1111="yes"))

Now I want to add a third criteria,
=SUMPRODUCT(--(AV124:AV1111="yes"),--(E123:E1111"Half
Off"),--(J124:J1111=0),J124:J1111)

But now I get a #VALUE error....any ideas?

Sorry, couple really stupid errors in my original post. How do I add a third criteria to this formula:
=SUMPRODUCT(-(J124:J1111=1),-(AV124:AV1111="yes"))

This just doesn't work:
=SUMPRODUCT(-(J124:J1111=1),-(AV124:AV1111="yes"),-(V124:V1111="Half Off"))

Thanks

Hard to tell without knowing what "just doesn't work" means.
Sytactically, it's fine, though if you're counting, you'll get a
negative number - add another negation to make it positive.



In article ,
Mike B wrote:

Sorry, couple really stupid errors in my original post. How do I add a
third criteria to this formula:
=SUMPRODUCT(-(J124:J1111=1),-(AV124:AV1111="yes"))

This just doesn't work:
=SUMPRODUCT(-(J124:J1111=1),-(AV124:AV1111="yes"),-(V124:V1111="Half
Off"))

Thanks for your help JE. I had some caffine, retyped my formula, added another negation, and it worked fine.

Thanks again
mike