This partially works... seems I'd have to divide
the product by 3 to get the actual result...

D2:D345={"Closed","Accepted","Testing"})
2) Status < "Closed", "Accepted", or "Testing"

I don't think that meets the criteria.

For "is not equal" better to use a MATCH function for multiple criteria.

Something like:

(ISNA(MATCH(D2:D345,{"Closed","Accepted","Testing" },0)))

And when doing that it's better to use cells to hold the criteria.

X1:X3 = Closed, Accepted, Testing

(ISNA(MATCH(D2:D345,X1:X3,0)))

Note that an empty cell will meet that condition.

--
Biff
Microsoft Excel MVP


"rweiss" wrote in message
...
This partially works... seems I'd have to divide the product by 3 to get
the
actual result... thoughts?

"Jacob Skaria" wrote:

Try

=SUMPRODUCT((C2:C345="Must")*(D2:D345={"Closed","A ccepted","Testing"})*
(J2:J345="XL")*(LEFT(I2:I345,3)="rpt"))

If this post helps click Yes
---------------
Jacob Skaria


"rweiss" wrote:

Trying to solve for the following:
Count if
1) Priority = "Must"
AND
2) Status < "Closed", "Accepted", or "Testing"
AND
3) Functional Area begins with "RPT"
AND
4) Estimated Effort = "XL"

Wondering how best to modify the formula below to represent parts 2 and
3
above. How to represent contains "rpt" or begins with "rpt" (if
wildcards
were permitted this would be too easy!) and accommodate the variable
status
values we wish not to include.

SUMPRODUCT(--(C2:C345="Must"),--(J2:J345="XL"),--(I2:I345="RPT"))

in a perfectly intuitive world, I'd write something like:

SUMPRODUCT(--(C2:C345="Must"),--(D2:D345<"Closed" OR "Accepted", OR
"Testing")--(J2:J345="XL"),--(I2:I345 CONTAINS "rpt"))

Thanks in advance,
Rik