Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula to count dates
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 -- Thank you for reading my post. Hopefully you can answer my querie |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula to count dates
On Wed, 7 Apr 2010 03:27:02 -0700, Shazza
wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula to count dates
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. -- Thank you for reading my post. Hopefully you can answer my querie "Ron Rosenfeld" wrote: On Wed, 7 Apr 2010 03:27:02 -0700, Shazza wrote: 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 . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula to count dates
On Wed, 7 Apr 2010 05:42:01 -0700, Shazza
wrote: 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. -- Thank you for reading my post. Hopefully you can answer my querie 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula to count dates
On Wed, 7 Apr 2010 05:42:01 -0700, Shazza
wrote: 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:D Company Student Course Dates add a fifth column and label it something like NumCourses E2: =IF(SUMPRODUCT(($C$2:C2=C2)*($D$2:D2=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to count no. of months between 2 dates | Excel Discussion (Misc queries) | |||
Sum if or Count if with dates or any other formula that would work | Excel Discussion (Misc queries) | |||
Formula to count cells between dates. | Excel Worksheet Functions | |||
Formula that will count between dates ranges | Excel Worksheet Functions | |||
Formula that will count between dates ranges | Excel Worksheet Functions |