+ Reply to Thread
Results 1 to 10 of 10

Count unique entries with criteria

  1. #1
    Registered User
    Join Date
    12-03-2012
    Location
    Ecuador
    MS-Off Ver
    Excel 2010
    Posts
    46

    Count unique entries with criteria

    Hi,

    Can someone please help me with a formula that allows me to count unique entries in a column according to two specified criteria.

    Specifically, I am trying to count the number of days in a month a supplier has a visited.

    Below is an example taken from the excel:

    COLUMN A COLUMN B
    Date Supplier
    01-ago-12 Mr X
    01-ago-12 Mr X
    01-ago-12 Mr X
    01-ago-12 Mr Y
    03-ago-12 Mr Y
    06-ago-12 Mr X
    08-ago-12 Mr X
    08-ago-12 Mr X
    09-ago-12 Mr Y
    09-ago-12 Mr Y
    14-ago-12 Mr X
    14-ago-12 Mr X
    15-ago-12 Mr Y
    17-ago-12 Mr X
    17-ago-12 Mr Y
    01-sep-12 Mr Y
    01-sep-12 Mr X
    01-sep-12 Mr X
    02-sep-12 Mr X
    02-sep-12 Mr X

    I want a formula that allows me to see how different dates Mr X has visited in each month. So in August he has visited 8 times BUT only on 5 different dates. I am looking for a formula that gives 5 as the answer.

    In the case of Mr Y I want the formula to give the answer 5 (even though he has visited 6 times in total, he has visited on only 5 different dates).

    In the case of Mr X in September, the answer to the formula would be 2, since has visited on 2 different dates in September.
    Would really appreciate the help!

    Thanks,

    Leopold2000

  2. #2
    Forum Contributor
    Join Date
    09-11-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2003, Excel 2007, Excel 2013
    Posts
    103

    Re: Count unique entries with criteria

    @ Leopold2000

    Welcome to the forum.

    Your request could be easily solved by using formula. However, some minor issues I need to clarify.

    For the date in the data, is it using xx-ago- for current month? Could the date in the data range be set as the actual date?
    Click * to reward me...
    Thank you...

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

    Re: Count unique entries with criteria

    hi Leopold2000, welcome to the forum. try this array formula:
    Please Login or Register  to view this content.
    you have to paste inside the formula bar, & press CTRL + SHIFT + ENTER to confirm. you can also refer the references in red to a cell reference instead

    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

  4. #4
    Registered User
    Join Date
    12-03-2012
    Location
    Ecuador
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: Count unique entries with criteria

    Hi,

    Sorry the excel is in Spanish. 'ago' is august. So 12-ago-12 is actually 12-08-2012.

    Yes there will be cases when the date is today's date.

    The data is actually:

    Date Supplier
    1-8-12 Mr X
    1-8-12 Mr X
    1-8-12 Mr X
    1-8-12 Mr Y
    3-8-12 Mr Y
    6-8-12 Mr X
    8-8-12 Mr X
    8-8-12 Mr X
    9-8-12 Mr Y
    9-8-12 Mr Y
    14-8-12 Mr X
    14-8-12 Mr X
    15-8-12 Mr Y
    17-8-12 Mr X
    17-8-12 Mr Y
    1-9-12 Mr Y
    1-9-12 Mr Y
    1-9-12 Mr X
    2-9-12 Mr X
    2-9-12 Mr X

    Thanks!

  5. #5
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Count unique entries with criteria

    With your sample data in A1:B21
    AND
    E2: a supplier to find....Mr X
    F2: a month_year........Ago_2012

    This regular formula returns the number of unique days that the supplier visited
    Please Login or Register  to view this content.
    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Count unique entries with criteria

    Hi Leopold2000 and welcome to the forum,

    This is an easy problem if you use Pivot Tables... See my example of what I think you have and want.

    No Formulas Needed!!! I now have the correct file attached... My Bad. Look at it again.
    Attached Files Attached Files
    Last edited by MarvinP; 12-03-2012 at 11:38 PM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Count unique entries with criteria

    add a helper column in C and use this, copied down...
    =IF(COUNTIF(A2:$A$21,A2)>1,"",$B$2)

    you could then use countifs to give you what you want
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  8. #8
    Registered User
    Join Date
    12-03-2012
    Location
    Ecuador
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: Count unique entries with criteria

    I'm still having trouble with the formula... The excel is quite complicated but I have attached a simplified version. Could you help put the formula in, to get the results for A, B and C for the various different months. I would prefer not to use a pivot table. Thank so much.
    Attached Files Attached Files

  9. #9
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Count unique entries with criteria

    [QUOTE=benishiryo;3032135]hi Leopold2000, welcome to the forum. try this array formula:
    Please Login or Register  to view this content.
    Hi benishiryo, Since column A are date values. You can shorten up your formula by doing this

    =SUM(IF(FREQUENCY(IF(B$2:B$21="Mr X",IF(TEXT(A$2:A$21,"mmyy")="0812",A$2:A$21)),A$2:A$21),1))

    Array formula: Ctrl+Shift+Enter, not just Enter

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

    Re: Count unique entries with criteria

    attaching the solutions of our Ron's, FDibbin's & my formula

    Edit:
    @Teethless mama: ahhh nice catch
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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