Formula to count the number of unique dates

[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

 

[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

 

[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 C16 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

 

[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

 

[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

 

[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. Valko]

Assume you have the unique names listed in a range, say, D26.

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.

 

[Herbert Seidenberg]

Excel 2007
Since you mentioned Pivot Table
and automatic update...
http://www.mediafire.com/file/n2mmhyytn2n/04_27_09a.xlsx