Counting Cells in a column depending on another column but excluding duplicates

R

rwenger

Hello,
I would like to count the total in a column depending on another
column, but exclude duplicates.
I my case I would like to know how many employees (column A) have taken
a course in 2010 (column B). Column A has duplicate names. Column B has
2009 and 2010 as the year the course was taken. I want to count 2009
and 2010 separately. I named the range of column A course_attendees and
column B course_taken

Name Course Year
Wilbert Bugay 2009
Wilbert Bugay 2009
Zahid Gul 2009
Zin Minn Lwin 2009
Zin Minn Lwin 2009
Shivanand Sampengi 2010
P S Rajesh 2010
C P Susheendran 2010
Raghavan Santosh 2010
Shibith Koran 2010
Vavakassim Azeez 2010
Shivanand Sampengi 2010
Rachel Padre 2010
P S Rajesh 2010
V U Radhakrishnan 2010


I have tried the following formula, but it gives me the incorrect
answer

=SUMPRODUCT(--(course_year=2010)*(Course_attendees<>"")/COUNTIF(Course_attendees,Course_attendees&""))

Can anyone help me?

Thank you.

Rene


+-------------------------------------------------------------------+
|Filename: Book2.pdf |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=134|
+-------------------------------------------------------------------+
 
J

JB

=COUNT(1/
FREQUENCY(IF(course_year=2010,MATCH(Course_attendees,Course_attendees,
0)),ROW(INDIRECT(""1:""&ROWS(Course_attendees)))))
Valid with shift+ctrl+enter

JB
 
T

T. Valko

Try this array formula**.

Assumes no empty cells in course_attendees.

D2 = 2009
D3 = 2010

Array entered** in E2 and copied down to E3:

=SUM(IF(FREQUENCY(IF(course_taken=D2,MATCH(course_attendees,course_attendees,0)),ROW(course_attendees)-MIN(ROW(course_attendees))+1),1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
Top