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

1. ## 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.

2. ## 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

3. ## 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. ## 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. ## 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. ## 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. ## Re: Count Unique Text & blanks in one column prior to Today's date

Thanks!!!

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

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