Formula to count the number of unique dates

A

Access Joe

Hey everyone,

Excel 2007

I have a set of data listed like such:

Column A COLUMN B
(DATES) (NAME)
4/1/2009 Joe
4/1/2009 Joe
4/1/2009 Mary
4/2/2009 Mike
4/2/2009 Ryan
4/2/2009 Mike
4/2/2009 Mike
4/3/2009 Larry
4/3/2009 Michelle
4/4/2009 Joe
4/4/2009 Ryan
4/4/2009 Joe

ETC.

This represents a schedule whereby each name is sometimes at different
locations through the course of one single day. What I want to do is figure
out how many DAYS the students are working. I can do an easy count of the
dates, but because a date is listed multiple times for different locations
and/or names, I can't figure out how to get the summary I need, which is
something like this:

Joe - 2 days
Mary - 1 day
Mike - 1 day
Michelle - 1 day
Ryan - 2 days

I don't want to run an Advanced Filter on Unique records. What I'm looking
for is a formula / Pivot Table feature that looks at each Date (not the
number of locations) uniquely for each name and updates automatically as new
data is entered. Any suggestions would be greatly appreciated!

Joe
 
M

Mike H

Try this array formula where D1 is the name (say) Joe

=COUNT(1/FREQUENCY(IF(B$1:B$100=D1,MATCH(A$1:A$100,A$1:A$100,0)),ROW(A$1:A$100)-ROW(A$1)+1))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.


Mike
 
B

Bernd P

Hello Joe,

That's exactly what I programmed my UDF Pfreq for.

If your data is in A1:B12, select a range of 6 rows and 2 columns -
say C1:D6 and array-enter (enter with CTRL + SHIFT + ENTER, not only
ENTER):
=pfreq(pfreq(B1:B12,A1:A12))

My UDF you will find here:
http://www.sulprobil.com/html/pfreq.html

Regards,
Bernd
 
B

Bernard Liengme

Assuming the data is in A2:B13 with labels in row 1
In formulas before change '13' to last row of your data
In C1 enter =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)
Copy down to last row
In G2:G6 enter a list of unique names (Joe, Mary, Mike, ...)
In H2 enter =SUMPRODUCT(--($B$2:$B$13=G2),--($C$2:$C$13=1))
Copy this down to last name in column G

best wishes
 
T

Teethless mama

Try this:

=SUM(N(FREQUENCY(IF(Name=D2,MATCH(Date&"",Date&"",)),MATCH(Date&"",Date&"",))>0))

ctrl+shift+enter, not just enter
copy down
 
A

Access Joe

Thank you ALL for getting back to me so quickly. I'll give your suggestions
a try and will let you know if I have any questions. Thanks again!
 
T

T. Valko

Assume you have the unique names listed in a range, say, D2:D6.

Array entered** in E2 and copied down to E6:

This assumes there are no empty date cells associated with a name.

=COUNT(1/FREQUENCY(IF(B$2:B$13=D2,A$2:A$13),A$2:A$13))

If there might be empty date cells associated with a name:

=COUNT(1/FREQUENCY(IF((B$2:B$13=D2)*(A2:A13<>""),A$2:A$13),A$2:A$13))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top