Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count unique entries
I am trying to count the number of unique entries in a column of data but
based on data in other columns. I need this to be automated versus filtering the data and cut/pasting, etc. For example, if I want to know how many unique entries in Column C are "2005, FEB" the answer I'm looking for is: 1 COL A COL B COL C 2006 JAN 3 2006 FEB 4 2005 FEB 5 2005 FEB 5 2005 JAN 5 Can one of your smart folks help me out? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count unique entries
I created a fourth column to concatenate the year and the month together and
then used a CountIf to get the value I wanted. The formula I used to concatenate the dates is =A2&"/"&B2 Which is copied down the column to capture all your data. The following COUNTIF function counts the number of 2005/Feb entries: =COUNTIF(D1:D5,"=2005/Feb") Hope this helps -- Kevin Backmann "Cash" wrote: I am trying to count the number of unique entries in a column of data but based on data in other columns. I need this to be automated versus filtering the data and cut/pasting, etc. For example, if I want to know how many unique entries in Column C are "2005, FEB" the answer I'm looking for is: 1 COL A COL B COL C 2006 JAN 3 2006 FEB 4 2005 FEB 5 2005 FEB 5 2005 JAN 5 Can one of your smart folks help me out? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count unique entries
See if this works for you....
Example with data in A1:C100 Col_A contains years Col_B contains months Col_C contains numeric values D1: 2005 E1: JAN F1: =SUM(((A1:A101=D1)*(B1:B101=E1))*(FREQUENCY((A1:A1 00=D1)*(B1:B100=E1)*C1:C100,(A1:A100=D1)*(B1:B100= E1)*C1:C100)0)) That array formula returns the count of Unique combination from Col_C where Col_A=2005 and Col_B=JAN. In this formula if 3 items contain 2005,Jan,6....they count as ONE unique combination Note 1: To commit array formulas, hold down [Ctrl] and [Shift] when you press [Enter]. Note 2: some of the references point down to row 101 (one row beyond the data range). That is not an error. Is that what you're looking for? *********** Regards, Ron XL2002, WinXP-Pro "Cash" wrote: I am trying to count the number of unique entries in a column of data but based on data in other columns. I need this to be automated versus filtering the data and cut/pasting, etc. For example, if I want to know how many unique entries in Column C are "2005, FEB" the answer I'm looking for is: 1 COL A COL B COL C 2006 JAN 3 2006 FEB 4 2005 FEB 5 2005 FEB 5 2005 JAN 5 Can one of your smart folks help me out? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count unique entries
Just use Data/Filter/adavnced filter & select Unique records only ---GJC -- gjcase ------------------------------------------------------------------------ gjcase's Profile: http://www.excelforum.com/member.php...o&userid=26061 View this thread: http://www.excelforum.com/showthread...hreadid=529579 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count unique entries
Assuming that A2:C6 contains the data, let E2 contain the year, and F2
the month, then try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... =COUNT(1/FREQUENCY(IF(A2:A6=E2,IF(B2:B6=F2,C2:C6)),IF(A2:A6 =E2,IF(B2:B6=F 2,C2:C6)))) Hope this helps! In article , Cash wrote: I am trying to count the number of unique entries in a column of data but based on data in other columns. I need this to be automated versus filtering the data and cut/pasting, etc. For example, if I want to know how many unique entries in Column C are "2005, FEB" the answer I'm looking for is: 1 COL A COL B COL C 2006 JAN 3 2006 FEB 4 2005 FEB 5 2005 FEB 5 2005 JAN 5 Can one of your smart folks help me out? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"COUNTU" function in Excel to count unique entries in a range | Excel Worksheet Functions | |||
Count unique entries | Excel Worksheet Functions | |||
Count of unique entries | Excel Discussion (Misc queries) | |||
how to count unique entries with multiple condition | Excel Worksheet Functions | |||
count duplicate (or, inversely, unique) entries, but based on a condition | Excel Worksheet Functions |