Counting Unique values from specific date ranges

**SMDNovice** · 12-09-2011, 01:59 PM

I have an excel sheet with hundreds of lines.
Column A has dates; Column B has text (some duplicate, some unique).
What I want is a way to sum the unique values found in column B that also match the date from column A with the dates specified in another worksheet.

An Example:

A B
11/11 T123456
11/11 T123456
11/11 T555555
12/5 T444444
12/5 T456789
12/5 T567890
12/5 T123789

In another worksheet there are dates in column A and I want the count of unique values corresponding to that date to go into column B:

A B
11/11 2
12/5 4

So, I'd like to be able to enter this formula into cell B2 and drag it down as far as my dates in column A go and have the formula look up in the other worksheet how many unique values I have for that date.

**NBVC** · 12-09-2011, 02:04 PM

Try:

=COUNT(1/FREQUENCY(IF('Sheet1'!$A$1:$A$7=A2,IF('Sheet1'!$B$1:$B$7<>"",MATCH('Sheet1'!$B$1:$B$7,'Sheet1'!$B$1:$B$7,0))),ROW('Sheet1'!$B$1:$B$7)-ROW('Sheet1'!$B$1)+1))

confirmed with CTRL+SHIFT+ENTER and copied down

Where A2 contains date in your active sheet to look for in column A of Sheet1, and count unique items in column B

**SMDNovice** · 12-09-2011, 03:52 PM

Thanks - but I keep getting "1" for my answer. I tried using the data set I gave in my example and still get "1" with that too.
Also, if this was not clear...if there is a duplicate, I would like it to count that value but only count the first occurrence.

**NBVC** · 12-09-2011, 04:42 PM

After you type in the formula and the cell is still active, make sure to hold the CTRL and SHIFT keys down.... then press ENTER. You should see a pair of { } brackets appear around the formula.