Prev: Number of characters to be displayed in a cell
Next: How to use 3 axis data to create 3d box
From: rwenger on 11 Apr 2010 08:06

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|
+-------------------------------------------------------------------+



--
rwenger
From: JB on 11 Apr 2010 09:27

=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


On 11 avr, 14:06, rwenger <rwenger.6077...(a)excelbanter.com> wrote:
> 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_atte­ndees,Course_attendees&""))
>
> Can anyone help me?
>
> Thank you.
>
> Rene
>
> +-------------------------------------------------------------------+
> |Filename: Book2.pdf                                                |
> |Download:http://www.excelbanter.com/attachment.php?attachmentid=134|
> +-------------------------------------------------------------------+
>
> --
> rwenger

From: T. Valko on 11 Apr 2010 09:57
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.

--
Biff
Microsoft Excel MVP


"rwenger" <rwenger.6077a5a(a)excelbanter.com> wrote in message
news:rwenger.6077a5a(a)excelbanter.com...
>
> 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|
> +-------------------------------------------------------------------+
>
>
>
> --
> rwenger


 | 
Pages: 1
Prev: Number of characters to be displayed in a cell
Next: How to use 3 axis data to create 3d box