Formula Help

[arvarr]

Can someone kindly assist with the formula?

I am trying to obtain the unique number of counts in the range.
For example, desired outcome for Peter is 3. There are 4 week ending dates for Peter but 1 is a similar date value.


A1 Persons Name
A2 Peter
A3 David
A4 John

A4 Count of Week Ending Dates
A5 Peter
A6 David
A7 John


On another worksheet, I have the following data.


Persons Name ID Week Ending Date
Peter 1000 2009-10-01
Michael 2000 2009-02-05
John 3000 2009-03-07
David 4000 2009-04-06
Peter 1000 2009-10-01
David 4000 2010-03-02
John 3000 2010-08-10
Peter 1000 2010-03-02
Michael 2000 2010-08-09
Michael 2000 2010-06-05
Peter 1000 2010-05-03
John 3000 2010-08-10
David 4000 2009-04-06

Thanks

 

[SkipVought]

hi,

First remove duplicates. In 2007, there is a command for that. In earlier versions, you must use the Advanced Filter to get Unique Values.

Then it's just a matter of using the COUNTIF function.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arvarr]

Hi skip
Thanks for your reply.
Other than the advanced filter feature, is there a formula that will work?
Thanks.

 

[SkipVought]

If I were doing it, I'd use MS Query to remove duplicates and do the summarization.

No formula that I can imagine.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GlennUK]

You could do it with formulas if you could put a couple of helper columns to the right of the data. Helper column 1 would be a concatenation of the name and the weekending date, and helper column 2 would be a countif of each item in helper column 1 ( like: =COUNTIF($D$2:$D$14,D2) )

The count of week ending dates for Peter would then be an array formula ( entered using Ctrl-Shift-Enter ):

=SUM(IF(Sheet1!$A$2:$A$14="Peter",1,0)/IF(Sheet1!$A$2:$A$14="Peter",Sheet1!$E$2:$E$14,1))

... assuming that helper column 2 is in column E of the data sheet ( and the data sheet is called Sheet1 ).

Cheers, Glenn.

Beauty is in the eye of the beerholder.