Fun with SUMPRODUCT

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.

"Jacob Skaria" wrote:

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","A ccepted","Testing"})*
(J2:J345="XL")*(ISNUMBER(SEARCH("rpt",I2:I345))))

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


"rweiss" wrote:

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

Thanks in advance,
Rik

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" wrote:

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","A ccepted","Testing"})*
(J2:J345="XL")*(ISNUMBER(SEARCH("rpt",I2:I345))))

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


"rweiss" wrote:

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

Thanks in advance,
Rik

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:J 345="XL")*
(D2345"")*(ISNUMBER(SEARCH("rpt",I2:I345))))

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


"T. Valko" wrote:

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.

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

Thanks in advance,
Rik


.

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

Meow



On Tue, 17 Nov 2009 22:37:02 -0800, rweiss
wrote:

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

Thanks in advance,
Rik

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!!

"T. Valko" wrote:

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.

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

Thanks in advance,
Rik


.