Need a formula to count dates

[Shazza]

I have a spradsheet in Excel 2007 that contains a list of company names,
course names, delegate names and dates that they attended courses.

I need to do a count of the courses but can not figure out how to do it.
there are lots of dates that are the same and lots of names that are the same
so i want it to count them only if they are different which is why i though
the date would be better to work on

Not sure if this makes any sense. Let me know if you need more info

 

[Ron Rosenfeld]

I have a spradsheet in Excel 2007 that contains a list of company names,
course names, delegate names and dates that they attended courses.

I need to do a count of the courses but can not figure out how to do it.
there are lots of dates that are the same and lots of names that are the same
so i want it to count them only if they are different which is why i though
the date would be better to work on

Not sure if this makes any sense. Let me know if you need more info

Post some sample data and desired results.
--ron

 

[Shazza]

This is some sample data of what informaion i am using
A1 A2 A3 A4
A5
Hunter's, Charles, Smith, Abrasive Wheels Regs 1/2-day course, 26-May-09
Hunter's, David Jones, Abrasive Wheels Regs 1/2-day course, 26-May-09
Leslie Ltd, Nigel Will Abrasive Wheels Regs 1/2-day course,
10-Nov-09
Johnston Ltd, James, Smith, Asbestos Awareness 1/2-day course 05-Nov-09
Johnston Ltd, Ivor, Smith Asbestos Awareness 1/2-day course 05-Nov-09
Amenity, Ross Jones Asbestos Awareness 1/2-day course 17-Dec-09
Garriock, Bruce, Willia Basic First Aid (1 day course)
30-Jan-09
Garriock, Debbie Smith Basic First Aid (1 day course)
30-Jan-09

I need a formula that will count all the courses that have run and as you
can see from the dates there can be several names set to one particular date.
I do not want it to pick up on the same date. ie Asbestos Awareness was run
twice so i need it to count it as 2. Basically i do not want to count how
many people attended the course i want to know how many courses run.

 

[Ron Rosenfeld]

This is some sample data of what informaion i am using
A1 A2 A3 A4
A5
Hunter's, Charles, Smith, Abrasive Wheels Regs 1/2-day course, 26-May-09
Hunter's, David Jones, Abrasive Wheels Regs 1/2-day course, 26-May-09
Leslie Ltd, Nigel Will Abrasive Wheels Regs 1/2-day course,
10-Nov-09
Johnston Ltd, James, Smith, Asbestos Awareness 1/2-day course 05-Nov-09
Johnston Ltd, Ivor, Smith Asbestos Awareness 1/2-day course 05-Nov-09
Amenity, Ross Jones Asbestos Awareness 1/2-day course 17-Dec-09
Garriock, Bruce, Willia Basic First Aid (1 day course)
30-Jan-09
Garriock, Debbie Smith Basic First Aid (1 day course)
30-Jan-09

I need a formula that will count all the courses that have run and as you
can see from the dates there can be several names set to one particular date.
I do not want it to pick up on the same date. ie Asbestos Awareness was run
twice so i need it to count it as 2. Basically i do not want to count how
many people attended the course i want to know how many courses run.

Is it the case that only one course will taught on any given date? Or might
you have two different courses on the same date?
--ron

 

[Ron Rosenfeld]

This is some sample data of what informaion i am using
A1 A2 A3 A4
A5
Hunter's, Charles, Smith, Abrasive Wheels Regs 1/2-day course, 26-May-09
Hunter's, David Jones, Abrasive Wheels Regs 1/2-day course, 26-May-09
Leslie Ltd, Nigel Will Abrasive Wheels Regs 1/2-day course,
10-Nov-09
Johnston Ltd, James, Smith, Asbestos Awareness 1/2-day course 05-Nov-09
Johnston Ltd, Ivor, Smith Asbestos Awareness 1/2-day course 05-Nov-09
Amenity, Ross Jones Asbestos Awareness 1/2-day course 17-Dec-09
Garriock, Bruce, Willia Basic First Aid (1 day course)
30-Jan-09
Garriock, Debbie Smith Basic First Aid (1 day course)
30-Jan-09

I need a formula that will count all the courses that have run and as you
can see from the dates there can be several names set to one particular date.
I do not want it to pick up on the same date. ie Asbestos Awareness was run
twice so i need it to count it as 2. Basically i do not want to count how
many people attended the course i want to know how many courses run.

If you are willing to use a Pivot Table, it won't matter whether there are
multiple courses on the same date.

For example, with your data in Columns A

Company Student Course Dates

add a fifth column and label it something like NumCourses

E2: =IF(SUMPRODUCT(($C$2:C2=C2)*($D$22=D2))>1,0,1)

Then, with your cursor in the data, Insert/Pivot Table (for Excel 2007; I don't
recall which menu Pivot Tables are on in the earlier versions; probably the
Data menu).

In the Pivot Table, drag "Course" to the Row area; and NumCourses to the Values
or Data area.

Format to taste.
--ron