Please help

[Joe]

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.

 

[Domenic]

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!

 

[Guest]

Try
=SUMPRODUCT(--(A2:A8="Nick"),--(MONTH(B2:B8)=6),--(C2:C8="PWP"))

 

[Joe]

Thank you, both formulas work great!

Try
=SUMPRODUCT(--(A2:A8="Nick"),--(MONTH(B2:B8)=6),--(C2:C8="PWP"))

 

[Joe]

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

 

[Roger Govier]

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"))

 

[Joe]

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

 

[Joe]

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

 

[Joe]

My apologies, after I revalidated there was a typo on my part. Formula
works great! Thank you all!!!!

Regards,
Joe