Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 . |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 . . |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Unique Values but not Filtered or Hidden Values | Excel Worksheet Functions | |||
Count Unique Values | Excel Worksheet Functions | |||
Count Unique Values | Excel Worksheet Functions | |||
count unique values, if ... | Excel Worksheet Functions | |||
Count unique values and create list based on these values | Excel Worksheet Functions |