Counting Unique values from specific date ranges
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.
Try:
confirmed with CTRL+SHIFT+ENTER and copied down=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))
Where A2 contains date in your active sheet to look for in column A of Sheet1, and count unique items in column B
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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.
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.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks