Multiple Criteria to return count of 1 item in a column.

[cjg1901]

Hi

I have 2 spreadsheets relating to empoyee records etc.

Sheet 1 holds all the data on every employee. Sheet 2 contains a select few employees' details. I need to calculate their attendance at management meetings

Sheet 1:
Employee # / Date of meeting / Attended?
12345 / 10/12/2010 / Yes
23446 / 10/12/2010 / Yes
34567 / 10/12/2010 / Yes
34567 / 12/05/2010 / No
23456 / 10/12/2010 / Yes
12366 / 10/12/2010 / Yes
12366 / 05/01/2011 / No
12366 / 02/02/2011 / No
56789 / 10/12/2010 / Yes
89907 / 10/12/2010 / No

Sheet 2 hold to the data on the employees that did not attend:
Employee # / Number of times Not attended?
34567 / ?
12366 / ?

I need a formula that will automatically calculate the number of "No" entries from the "Attended?" column in sheet 1 as my spreadsheet is more complicated than my example.

Please help?

Thanks
cjg1901

 

[Colin Legg]

Hi,

XL2007 or later

=COUNTIFS(Sheet1!$A$2:$A$11,A2,Sheet1!$C$2:$C$11,"No")

XL2003 or earlier

=SUMPRODUCT(--(Sheet1!$A$2:$A$11=A2),--(Sheet1!$C$2:$C$11="No"))