+ Reply to Thread
Results 1 to 7 of 7

Count Unique Text & blanks in one column prior to Today's date

  1. #1
    Registered User
    Join Date
    11-21-2011
    Location
    Virginia Beach, VA
    MS-Off Ver
    Excel 2007
    Posts
    11

    Count Unique Text & blanks in one column prior to Today's date

    Hello,

    First time poster. I am using EXECL 2007 and have one column for event description (some are duplicated, some are blank) and another column for start date. I am building an event summary at the top of the worksheet and would like to count the unique events & blanks in the event column prior to Today's date. I have tried several differnt iterations of IF, COUNT(A),COUNT(IF), and SUMPRODUCT, but I can't seem to put it all together for one solution.

    Thanks.
    Last edited by VBSK8R; 11-21-2011 at 01:25 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Count Unique Text & blanks in one column prior to Today's date

    Perhaps something like:

    =COUNT(1/FREQUENCY(IF(B1:B100<TODAY(),MATCH(A1:A100&" ",A1:A100&" ",0)),ROW(A1:A100)-ROW(A1)+1))

    adjust ranges to suit... and confirm with CTRL+SHIFT+ENTER not just ENTER
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    11-21-2011
    Location
    Virginia Beach, VA
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Count Unique Text & blanks in one column prior to Today's date

    It comes up with zero:

    =COUNT(1/FREQUENCY(IF(I11:I20000<TODAY(),MATCH(H11:H200000&" ",I11:I2000&" ",0)),ROW(I11:I20000)-ROW(I11)+1))

    Column I is the designation (some events have the same designation, some are blank, want to count each unique designation and every blank), Column H is the start date and want to count each instance of Column I prior to today's date.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Count Unique Text & blanks in one column prior to Today's date

    Should it be?

    =COUNT(1/FREQUENCY(IF(H11:H20000<TODAY(),MATCH(I11:I20000&" ",I11:I20000&" ",0)),ROW(I11:I20000)-ROW(I11)+1))

    and confirm with CTRL+SHIFT+ENTER keys to get { } brackets around the formula.

  5. #5
    Registered User
    Join Date
    11-21-2011
    Location
    Virginia Beach, VA
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Count Unique Text & blanks in one column prior to Today's date

    It is now coming up with seven(7): here is the list of data:

    Designation Start Date
    11-01 18-Oct-10
    11-01 18-Oct-10
    11-01 18-Oct-10
    11-01 18-Oct-10
    11-02 29-Nov-10
    11-02 29-Nov-10
    11-02 29-Nov-10
    14-Feb-11
    11-03 4-Apr-11
    11-04 16-May-11
    11-04 16-May-11
    11-04 16-May-11
    11-04 16-May-11
    18-Jul-11
    11-05 18-Jul-11
    11-06 23-Aug-11
    11-06 23-Aug-11
    11-06 23-Aug-11
    19-Sep-11
    11-07 19-Sep-11
    11-07 19-Sep-11
    17-Oct-11
    12-01 31-Oct-11
    12-01 31-Oct-11
    12-01 31-Oct-11
    12-01 31-Oct-11
    12-02 28-Nov-11


    Based on the uniques and blanks on Designation, it should read twelve(12).

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Count Unique Text & blanks in one column prior to Today's date

    Okay assuming that list is in A2:B28, try:

    =COUNT(1/FREQUENCY(IF(B2:B28<TODAY(),MATCH(A2:A28&B2:B28,A2:A28&B2:B28,0)),ROW(A2:A28)-ROW(A2)+1))

    CSE confirmed.

  7. #7
    Registered User
    Join Date
    11-21-2011
    Location
    Virginia Beach, VA
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Count Unique Text & blanks in one column prior to Today's date

    Thanks!!!

    Marked as solved and bumped your rep, thanks again!!

+ 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