Fun with SUMPRODUCT

R

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

Thanks in advance,
Rik
 
A

Ashish Mathur

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
 
J

Jacob Skaria

Try

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

If this post helps click Yes
 
R

rweiss

This didn't seem to work, returned 0.

Ashish Mathur said:
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
 
A

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(D2:D345,A400:A402,0))))


--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
R

rweiss

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

rweiss

BINGO!
Much obliged!!

Ashish Mathur said:
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(D2:D345,A400:A402,0))))


--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
J

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")*(D2:D345={"Closed","Accepted","Testing"})*
(J2:J345="XL")*(ISNUMBER(SEARCH("rpt",I2:I345))))

If this post helps click Yes
 
T

T. Valko

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.
 
R

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.
 
R

rweiss

The set of three variables in the D2:D345 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.
 
J

Jacob Skaria

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

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

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

If this post helps click Yes
 
M

MeowSayTongue

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

Meow
 
R

rweiss

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

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

Thanks all for your help!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top