You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"rweiss" wrote in message
...
BINGO!
Much obliged!!

"Ashish Mathur" wrote:

Hi,

Try this. I missed the last 0 in the match function

SUMPRODUCT((C2:C345="Must")*(J2:J345="XL")*(left(I 2:I345,3)="RPT")*(iserror(match(D2:D345,A400:A402, 0))))


--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"rweiss" wrote in message
...
This didn't seem to work, returned 0.

"Ashish Mathur" wrote:

Hi,

Try this

=SUMPRODUCT((C2:C345="Must")*(J2:J345="XL")*(left( I2:I345,3)="RPT")*(iserror(match(D2:D345,A400:A402 ))))

A400:A402 contains Closed, Accepted and Testing

I have not tried this

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"rweiss" wrote in message
...
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