|
Please help
I am looking for a formula that will help me capture all the PWP error
codes Nick had for the month of June. (A) (B) (C) Name Date Error Code Nick 06/20/06 PWP Anna 06/21/06 PWP Sal 05/25/06 PWP Nick 06/20/06 IBU Nick 05/22/06 PWP Joe 06/23/06 PWP Nick 06/17/06 PWP The result of this example is 2 Any assistance is greatly appreciated. |
Please help
Assuming that A2:C8 contains the data, try...
=SUMPRODUCT(--(A2:A8="Nick"),--(B2:B8-DAY(B2:B8)+1=E2),--(C2:C8="PWP")) ....where E2 contains the first day of the month and year of interest, such 6/1/2006 for the month of June in 2006. Hope this helps! In article .com, "Joe" wrote: I am looking for a formula that will help me capture all the PWP error codes Nick had for the month of June. (A) (B) (C) Name Date Error Code Nick 06/20/06 PWP Anna 06/21/06 PWP Sal 05/25/06 PWP Nick 06/20/06 IBU Nick 05/22/06 PWP Joe 06/23/06 PWP Nick 06/17/06 PWP The result of this example is 2 Any assistance is greatly appreciated. |
Please help
Try
=SUMPRODUCT(--(A2:A8="Nick"),--(MONTH(B2:B8)=6),--(C2:C8="PWP")) "Joe" wrote: I am looking for a formula that will help me capture all the PWP error codes Nick had for the month of June. (A) (B) (C) Name Date Error Code Nick 06/20/06 PWP Anna 06/21/06 PWP Sal 05/25/06 PWP Nick 06/20/06 IBU Nick 05/22/06 PWP Joe 06/23/06 PWP Nick 06/17/06 PWP The result of this example is 2 Any assistance is greatly appreciated. |
Please help
Thank you, both formulas work great! :-)
JMB wrote: Try =SUMPRODUCT(--(A2:A8="Nick"),--(MONTH(B2:B8)=6),--(C2:C8="PWP")) "Joe" wrote: I am looking for a formula that will help me capture all the PWP error codes Nick had for the month of June. (A) (B) (C) Name Date Error Code Nick 06/20/06 PWP Anna 06/21/06 PWP Sal 05/25/06 PWP Nick 06/20/06 IBU Nick 05/22/06 PWP Joe 06/23/06 PWP Nick 06/17/06 PWP The result of this example is 2 Any assistance is greatly appreciated. |
Please help
OK, what do I need to do to the formula if I wanted to put the amount
of PWP errors Nick had for the month of June in sheet 2? Thanks again for your help, Joe Joe wrote: Thank you, both formulas work great! :-) JMB wrote: Try =SUMPRODUCT(--(A2:A8="Nick"),--(MONTH(B2:B8)=6),--(C2:C8="PWP")) "Joe" wrote: I am looking for a formula that will help me capture all the PWP error codes Nick had for the month of June. (A) (B) (C) Name Date Error Code Nick 06/20/06 PWP Anna 06/21/06 PWP Sal 05/25/06 PWP Nick 06/20/06 IBU Nick 05/22/06 PWP Joe 06/23/06 PWP Nick 06/17/06 PWP The result of this example is 2 Any assistance is greatly appreciated. |
Please help
Hi Joe
I assume you mean you want the formula result to appear on sheet2. If so, then just include the Sheet name plus an exclamation in your range referencing. =SUMPRODUCT(--(Sheet1!A2:A8="Nick"),--(MONTH(Sheet1!B2:B8)=6),--(Sheet1!C2:C8="PWP")) -- Regards Roger Govier "Joe" wrote in message oups.com... OK, what do I need to do to the formula if I wanted to put the amount of PWP errors Nick had for the month of June in sheet 2? Thanks again for your help, Joe Joe wrote: Thank you, both formulas work great! :-) JMB wrote: Try =SUMPRODUCT(--(A2:A8="Nick"),--(MONTH(B2:B8)=6),--(C2:C8="PWP")) "Joe" wrote: I am looking for a formula that will help me capture all the PWP error codes Nick had for the month of June. (A) (B) (C) Name Date Error Code Nick 06/20/06 PWP Anna 06/21/06 PWP Sal 05/25/06 PWP Nick 06/20/06 IBU Nick 05/22/06 PWP Joe 06/23/06 PWP Nick 06/17/06 PWP The result of this example is 2 Any assistance is greatly appreciated. |
Please help
This suggestion does not appear to be working. I am getting a #VALUE!
when I use the proposed formula below in the field I am trying to calculate. =SUMPRODUCT(--(Sheet1!A2:A8="Nick"),--(MONTH(Sheet1!B2:B8)=6),--(Sheet1!C2:C8="PWP")) Your continued help is truly appreciated. Regards, Joe Roger Govier wrote: Hi Joe I assume you mean you want the formula result to appear on sheet2. If so, then just include the Sheet name plus an exclamation in your range referencing. =SUMPRODUCT(--(Sheet1!A2:A8="Nick"),--(MONTH(Sheet1!B2:B8)=6),--(Sheet1!C2:C8="PWP")) -- Regards Roger Govier "Joe" wrote in message oups.com... OK, what do I need to do to the formula if I wanted to put the amount of PWP errors Nick had for the month of June in sheet 2? Thanks again for your help, Joe Joe wrote: Thank you, both formulas work great! :-) JMB wrote: Try =SUMPRODUCT(--(A2:A8="Nick"),--(MONTH(B2:B8)=6),--(C2:C8="PWP")) "Joe" wrote: I am looking for a formula that will help me capture all the PWP error codes Nick had for the month of June. (A) (B) (C) Name Date Error Code Nick 06/20/06 PWP Anna 06/21/06 PWP Sal 05/25/06 PWP Nick 06/20/06 IBU Nick 05/22/06 PWP Joe 06/23/06 PWP Nick 06/17/06 PWP The result of this example is 2 Any assistance is greatly appreciated. |
Please help
This suggestion does not appear to be working. I am getting a #VALUE!
when I use the proposed formula below in the field I am trying to calculate. =SUMPRODUCT(--(Sheet1!A2:A8="Nick"),--(MONTH(Sheet1!B2:B8)=6),--(Sheet1!C2:C8="PWP")) Your continued help is truly appreciated. Regards, Joe Roger Govier wrote: Hi Joe I assume you mean you want the formula result to appear on sheet2. If so, then just include the Sheet name plus an exclamation in your range referencing. =SUMPRODUCT(--(Sheet1!A2:A8="Nick"),--(MONTH(Sheet1!B2:B8)=6),--(Sheet1!C2:C8="PWP")) -- Regards Roger Govier "Joe" wrote in message oups.com... OK, what do I need to do to the formula if I wanted to put the amount of PWP errors Nick had for the month of June in sheet 2? Thanks again for your help, Joe Joe wrote: Thank you, both formulas work great! :-) JMB wrote: Try =SUMPRODUCT(--(A2:A8="Nick"),--(MONTH(B2:B8)=6),--(C2:C8="PWP")) "Joe" wrote: I am looking for a formula that will help me capture all the PWP error codes Nick had for the month of June. (A) (B) (C) Name Date Error Code Nick 06/20/06 PWP Anna 06/21/06 PWP Sal 05/25/06 PWP Nick 06/20/06 IBU Nick 05/22/06 PWP Joe 06/23/06 PWP Nick 06/17/06 PWP The result of this example is 2 Any assistance is greatly appreciated. |
Please help
My apologies, after I revalidated there was a typo on my part. Formula
works great! Thank you all!!!! Regards, Joe Roger Govier wrote: Hi Joe I assume you mean you want the formula result to appear on sheet2. If so, then just include the Sheet name plus an exclamation in your range referencing. =SUMPRODUCT(--(Sheet1!A2:A8="Nick"),--(MONTH(Sheet1!B2:B8)=6),--(Sheet1!C2:C8="PWP")) -- Regards Roger Govier "Joe" wrote in message oups.com... OK, what do I need to do to the formula if I wanted to put the amount of PWP errors Nick had for the month of June in sheet 2? Thanks again for your help, Joe Joe wrote: Thank you, both formulas work great! :-) JMB wrote: Try =SUMPRODUCT(--(A2:A8="Nick"),--(MONTH(B2:B8)=6),--(C2:C8="PWP")) "Joe" wrote: I am looking for a formula that will help me capture all the PWP error codes Nick had for the month of June. (A) (B) (C) Name Date Error Code Nick 06/20/06 PWP Anna 06/21/06 PWP Sal 05/25/06 PWP Nick 06/20/06 IBU Nick 05/22/06 PWP Joe 06/23/06 PWP Nick 06/17/06 PWP The result of this example is 2 Any assistance is greatly appreciated. |
| All times are GMT +1. The time now is 02:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com