| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
