If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
Fun with SUMPRODUCT
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 |
#13
|
|||
|
|||
Fun with SUMPRODUCT
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 . |
#14
|
|||
|
|||
Fun with SUMPRODUCT
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 |
#15
|
|||
|
|||
Fun with SUMPRODUCT
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 . |
|
Thread Tools | |
Display Modes | |
|
|