Multiple columns/sheets - count,sumproduct - who knows??

[cjg1901]

Hi All

I have 2 sheets:

Sheet 1 - all employee information
Sheet 2 - list of select employees that failed to attend training course last week


Sheet 1:
Pay # / Employee Name / Attended?
12345 / John Smith / Yes
12345 / John Smith / No
12345 / John Smith / No
12345 / John Smith / Yes
45678 / Mike Jones / No
45678 / Mike Jones / No
45678 / Mike Jones / No
45678 / Mike Jones / Yes etc etc

Sheet 2:
Pay # / Employee Name / # of times not attended
12345 / John Smith / 2
45678 / Mike Jones / 3


I need to have the number of times an employee has not attended thier training courses - in column 3 on sheet 2. I have thousands of employees so I need a formula that will automatically calculate this figure.

Please help?

Thanks
cjg1901

 

[VoG]

Try in Sheet2 C2 and copied down

=SUMPRODUCT(--(Sheet1!A$1:A$100=A2),--(Sheet1!C$1:C$100="No"))