+ Reply to Thread
Results 1 to 8 of 8

Count Distinct Values in One Column Based on Value in A Different Column

  1. #1
    Registered User
    Join Date
    01-25-2012
    Location
    Tucson, AZ, USA
    MS-Off Ver
    Excel 2007
    Posts
    12

    Count Distinct Values in One Column Based on Value in A Different Column

    I need to count how many times an event occurred for each month of the year. In column F, I have the months listed as 1, 2, 3, 4 etc. instead of Jan, Feb, Mar, Apr, etc. They may repeat and aren't sorted. In column H, I have the event date. This date may repeat. Dates, also, aren't sorted. I need to count the number of events that occur in a certain month. I looked up on this forum and found a formula which works to count each distinct date, however I haven't been able to modify it correctly to give me the counts for just a particular month

    =SUMPRODUCT(($H$14:$H$1014<>"")/COUNTIF($H$14:$H$1014,$H$14:$H$1014&""))

    I forgot to mention, some of the cells in column H may be blank (not contain a date). Here's a sample:

    Column F Column H
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    2
    2
    2 2/13/12
    2 2/13/12
    2 2/13/12
    2 2/17/12
    2 2/17/12
    2 2/13/12
    2 2/13/12
    3 3/15/12
    3 3/15/12
    4 4/9/12
    4 4/9/12
    3 3/15/12
    3 3/21/12


    Any suggestions are welcomed.

  2. #2
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: Count Distinct Values in One Column Based on Value in A Different Column

    See attachment:
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    08-14-2006
    Location
    USA
    MS-Off Ver
    2019
    Posts
    686

    Re: Count Distinct Values in One Column Based on Value in A Different Column

    =SUMPRODUCT(--(F1:F100=2),--(H1:H100>0))


    change rows as necessary
    change 2 (feb) to 3, 4, 5, etc

  4. #4
    Registered User
    Join Date
    01-25-2012
    Location
    Tucson, AZ, USA
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Count Distinct Values in One Column Based on Value in A Different Column

    Quote Originally Posted by WHER View Post
    See attachment:
    Thank-you. I downloaded your file, it worked on my computer. However, when I copied the formula (changing references to be accurate) it wouldn't work. I've triple checked the references and the spaces, commas, etc. I'm at work and unfortunately, I cannot upload a file to attach. Any suggestions?

  5. #5
    Registered User
    Join Date
    01-25-2012
    Location
    Tucson, AZ, USA
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Count Distinct Values in One Column Based on Value in A Different Column

    Quote Originally Posted by carsto View Post
    =SUMPRODUCT(--(F1:F100=2),--(H1:H100>0))


    change rows as necessary
    change 2 (feb) to 3, 4, 5, etc
    [QUOTE=carsto;2765543]=SUMPRODUCT(--(F1:F100=2),--(H1:H100>0))


    Thank-you for your help. This works partially for what I need. However, it is counting each line/date for that month. So if the same date occurs 15 times, it results in 15. I need Excel to count each unique date for each month. So if there were 3 different dates (events) in February, say 2/13/12, 2/15/12 and 2/27/12 but those dates are listed on 150 lines (because there were 150 participants), I need Excel to count the unique occurrences, which would be (3) for February.

    I could do this manually each month. However, the worksheets often have 5,000-10,000 lines sometimes. I could also copy/paste date column only and remove duplicates but I'm trying to come up with a formula that would save me from having to do that each time I need to report.

  6. #6
    Registered User
    Join Date
    01-25-2012
    Location
    Tucson, AZ, USA
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Count Distinct Values in One Column Based on Value in A Different Column

    Quote Originally Posted by carsto View Post
    =SUMPRODUCT(--(F1:F100=2),--(H1:H100>0))


    change rows as necessary
    change 2 (feb) to 3, 4, 5, etc
    =SUMPRODUCT(--($F$14:$F$1014=1),(($H$14:$H$1014<>"")/COUNTIF($H$14:$H$1014,$H$14:$H$1014&"")))

    This is what I ended up with and it actually works. It actually ended up in a very, very lengthy IF statement. But it works so all is wonderful in my world of reporting numbers to others. Thanks so much for your quick responses (carsto and WHER). I appreciate the help!!!!!!!

  7. #7
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: Count Distinct Values in One Column Based on Value in A Different Column

    The formula in cel L2 of my attachment is an array-formula. These need to be confirmed with "ctrl+shift+enter" instead of simply "enter".

  8. #8
    Registered User
    Join Date
    01-29-2015
    Location
    Chicago
    MS-Off Ver
    2007
    Posts
    1

    Re: Count Distinct Values in One Column Based on Value in A Different Column

    You all are awesome, I tried this formula on my data and it worked perfectly. I do have another question, if anyone can help me. I need to add a second condition into this formula.

    Basically I want to check unique employee IDs by month and region

+ 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