How can I count unique values

  • Thread starter Thread starter Irelandabroad
  • Start date Start date
I

Irelandabroad

I have a worksheet with several columns:

Column F Column K
21/10/2008 Smith
27/10/2008 Jones
28/10/2008 Smith
25/11/2008 Jones
3/12/2008 Johns
10/02/2009 Johns
2/06/2009 Ryan
4/02/2009 Ryan

I need to prepare a formula which will count the number of times each unique
name appears in a month. (i have several years of data)

Thanks
 
Based on your sample data, to get the unique count of names in a specific
month/year...

Array entered** :

=SUM(IF(FREQUENCY(IF(TEXT(F2:F9,"mmmyyyy")="Oct2008",MATCH(K2:K9,K2:K9,0)),ROW(K2:K9)-ROW(K2)+1),1))

** 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.

Assumes no empty cells in the name range.

The formula above will count for Oct 2008.
 
Try the below. (You can create a list of names and change the name to a cell
reference...)
=SUMPRODUCT((TEXT(F2:F100,"mmyyyy")="112009")*(K2:K100="Smith"))


If this post helps click Yes
 
Biff, I was about to respond this way but when I re-read the query it says
"count the number of times each unique name appears"

If this post helps click Yes
 
If you're replying to my post then that means my posts are finally starting
to show up in the web interface. The "ngs" have been broken for about a
week. Only messages made through the website were being posted.

Anyhow....

The formula I suggested is based on my interpretation of the post. I have a
50% chance of being right!
 
Yes finally they are (just 4 hours back)..and if you lookback all the
respones (for the last week) are being shown in the web interface but with
the current date 11/10/2009..Im going through them now..

If this post helps click Yes
 
I have a worksheet with several columns:

Column F Column K
21/10/2008 Smith
27/10/2008 Jones
28/10/2008 Smith
25/11/2008 Jones
3/12/2008 Johns
10/02/2009 Johns
2/06/2009 Ryan
4/02/2009 Ryan

I need to prepare a formula which will count the number of times each unique
name appears in a month. (i have several years of data)

Thanks
Click to expand...

Instead of a formula, consider a Pivot Table.

Put Titles at the top of each column. e.g. Dates | Names
Insert/Pivot table

Drag Dates to Rows
Drag Names to Columns
Drag Names to Data (or Values)

Right-click in the Date area and select to Group Months (or Months and Years if
appropriate).

Apply some formats to make it look nice.
--ron
 
Back
Top