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 |
#1
|
|||
|
|||
Fun with SUMPRODUCT
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 |
#2
|
|||
|
|||
Fun with SUMPRODUCT
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 |
#3
|
|||
|
|||
Fun with SUMPRODUCT
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 |
#4
|
|||
|
|||
Fun with SUMPRODUCT
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 |
#5
|
|||
|
|||
Fun with SUMPRODUCT
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(D2345,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(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 |
#6
|
|||
|
|||
Fun with SUMPRODUCT
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 |
#7
|
|||
|
|||
Fun with SUMPRODUCT
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(D2345,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(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 |
#8
|
|||
|
|||
Fun with SUMPRODUCT
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 |
#9
|
|||
|
|||
Fun with SUMPRODUCT
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(D2345,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(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 |
#10
|
|||
|
|||
Fun with SUMPRODUCT
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 | |
|
|