How can I count unique values
 

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

How can I count unique values
 

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")="Oct200 8",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.

--
Biff
Microsoft Excel MVP


"Irelandabroad" wrote in message
...
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

How can I count unique values
 

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
---------------
Jacob Skaria


"Irelandabroad" wrote:

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

How can I count unique values
 

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
---------------
Jacob Skaria


"T. Valko" wrote:

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")="Oct200 8",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.

--
Biff
Microsoft Excel MVP


"Irelandabroad" wrote in message
...
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


.

How can I count unique values
 

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!

--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
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
---------------
Jacob Skaria


"T. Valko" wrote:

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")="Oct200 8",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.

--
Biff
Microsoft Excel MVP


"Irelandabroad" wrote in
message
...
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


.

How can I count unique values
 

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
---------------
Jacob Skaria


"T. Valko" wrote:

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!

--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
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
---------------
Jacob Skaria


"T. Valko" wrote:

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")="Oct200 8",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.

--
Biff
Microsoft Excel MVP


"Irelandabroad" wrote in
message
...
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


.



.

How can I count unique values
 

On Tue, 10 Nov 2009 19:31:35 -0800, Irelandabroad
wrote:

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

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

How can I count unique values
 

Excel 2007 PivotTable
Count unique names.
Accommodates both interpretations.
Nice too...
http://www.mediafire.com/file/wgiwzagqfq0/11_11_09.xlsx