Fun with SUMPRODUCT

[rweiss]

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"),--(D2345<>"Closed" OR "Accepted", OR
"Testing")--(J2:J345="XL"),--(I2:I345 CONTAINS "rpt"))

Thanks in advance,
Rik

 

[Ashish Mathur]

Hi,

Try this

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

A400:A402 contains Closed, Accepted and Testing

I have not tried this

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Jacob Skaria]

Try

=SUMPRODUCT((C2:C345="Must")*(D2345={"Closed","Accepted","Testing"})*
(J2:J345="XL")*(LEFT(I2:I345,3)="rpt"))

If this post helps click Yes

 

[rweiss]

This didn't seem to work, returned 0.

Hi,

Try this

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

A400:A402 contains Closed, Accepted and Testing

I have not tried this

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Ashish Mathur]

Hi,

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

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


--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[rweiss]

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

 

[rweiss]

BINGO!
Much obliged!!

Hi,

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

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


--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Jacob Skaria]

The earlier formula will count if col I starts with rpt.. If you have rpt
anywhere in the text then try the below version

=SUMPRODUCT((C2:C345="Must")*(D2345={"Closed","Accepted","Testing"})*
(J2:J345="XL")*(ISNUMBER(SEARCH("rpt",I2:I345))))

If this post helps click Yes

 

[Ashish Mathur]

You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[T. Valko]

This partially works... seems I'd have to divide

the product by 3 to get the actual result...

D2345={"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(D2345,{"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(D2345,X1:X3,0)))

Note that an empty cell will meet that condition.

 

[rweiss]

This still seems to produce the same result which is 3x more than it should
be. I tested by one instance of the data and it impacted the product of this
formula by 3.

 

[rweiss]

The set of three variables in the D2345 range (Closed, Accepted, Testing)
is causing the variation... if i remove one of them, the product becomes 2x
larger than actual, and when I remove another so there is just one left, the
product is accurate.

 

[Jacob Skaria]

Yes Biff. I thought D2345 should be one of {"Closed","Accepted","Testing"})

Rik, to ignore count of blank ColD add one more condition...

=SUMPRODUCT((C2:C345="Must")*(ISNA(MATCH(
D2345,{"Closed","Accepted","Testing"},0)))*(J2:J345="XL")*
(D2345<>"")*(ISNUMBER(SEARCH("rpt",I2:I345))))

If this post helps click Yes

 

[MeowSayTongue]

Did you replace the A400:A402 reference with your own corrected
reference(s)?

Meow

 

[rweiss]

Substituting (ISNA(MATCH(D2345,{"Closed","Accepted","Testing"},0))) for
(D2345<>{"Closed","Accepted","Testing"}) resolved the 3x issue.

And for the record, there are no blanks in D2345, but there are other
values besides Closed, Accepted, Testing...

Thanks all for your help!!