Hi everyone
I know there are other similar threads around (I feel like I have tried all of them!), but I can't seem to get them to work for me when I do try them.
I have a large sheet (several thousand rows and growing) - see a small cut of the data. The columns following on from this have a list of people's names, hence why a lot of the data repeats (as a number of people attended each program).
Program Clinic Title Start Date Manager Skills Clinics PRD - Staff 5/06/2012 Manager Skills Clinics PRD - Staff 5/06/2012 Manager Skills Clinics PRD - Staff 8/06/2012 Manager Skills Clinics PRD - Managers 8/06/2012 Manager Skills Clinics PRD - Managers 8/06/2012 Manager Skills Clinics PRD - Managers 8/06/2012 Manager Skills Clinics PRD - Managers 12/06/2012 Manager Skills Clinics PRD - Managers 12/06/2012 Manager Skills Clinics PRD - Managers 13/06/2012 Manager Skills Clinics PRD - Staff 10/07/2012 Manager Skills Clinics PRD - Staff 10/07/2012 Manager Skills Clinics PRD - Staff 10/07/2012 Fundamentals of Management Fundamentals of Management 14/02/2013 Fundamentals of Management Fundamentals of Management 15/02/2013 Fundamentals of Management Fundamentals of Management 15/02/2013 Coaching Conversations Coaching Conversations 28/02/2013 Coaching Conversations Coaching Conversations 25/02/2013 Coaching Conversations Coaching Conversations 28/02/2013 Coaching Conversations Coaching Conversations 25/02/2013 Coaching Conversations Coaching Conversations 23/02/2013
I also have a table (on a separate sheet) with a space I can input my own dates. See example here...
Date Range From Date 1/1/2012 To Date 31/12/2013
I want to run a count on the values in column A with the following Criteria:
-If the Start Date in Column C is Greater than or equal to the "From Date"
-If the Start Date in Column C is Less than or equal to the "To Date"
-If the values in Columns A and C repeat (eg. the first 2 lines of data are the same, but the 3rd line is different), then the value is only counted once.
If I manually work this out using the data above, I come up with:
Program Number of times run Fundamentals of management 2 Manager Skills Clinic 6 Coaching Conversations 3
Can anyone help me with a formula that will automatically give me that answer?
I've been trying to use an array formula that I've seen on other posts, but can't get it to work for me.
Thanks
Jess.
Bookmarks