+ Reply to Thread
Results 1 to 3 of 3

Counting Unique/Different Values based on multiple Criteria

  1. #1
    Registered User
    Join Date
    12-07-2012
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2007
    Posts
    10

    Counting Unique/Different Values based on multiple Criteria

    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.

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Counting Unique/Different Values based on multiple Criteria

    hi Jess. it'll be so much better if you uploaded a sample excel file instead. it's troublesome when we provide a solution where you don't know how to apply to your own file where the cell reference are different, and there'll be lots of to and fro. so i hope i won't have to do that.

    assuming you have your 3rd table in D1 onwards, try this array formula in E2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Attached Files Attached Files

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    12-07-2012
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Counting Unique/Different Values based on multiple Criteria

    Hi Ben

    First off - mega apologies for not attaching a spreadsheet! You're right, would have been so much easeir! But thank you for attaching a sample one for me.
    The formula worked perfectly! Thanks so much.

    Cheers
    Jess.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Counting Unique Values on Multiple Criteria
    By buhwheet in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-10-2014, 01:32 AM
  2. [SOLVED] Counting Unique Values with Multiple Criteria
    By smwbuddy in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-15-2013, 08:35 PM
  3. Counting Unique Values based on several criteria
    By GowHow in forum Excel General
    Replies: 2
    Last Post: 04-25-2012, 10:16 PM
  4. Counting Unique Values with Multiple Criteria
    By CELIA.NEFF in forum Excel General
    Replies: 10
    Last Post: 02-15-2012, 11:31 AM
  5. Replies: 6
    Last Post: 10-18-2011, 07:34 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1