Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i count the number of unique values in a given column?
I have a huge data extract that i'm trying to sort through. The first thing
I want to do is count the number of unique entries in a given column. For example, in my extract a "name" comes up many times - i want to count how many "unique" names I have on my data sheet, in a specific column. I'm pretty sure this is easily done with pivot tables - but can't figure it out myself. (the pivot table keeps summing stuff up - it's not "counting"). |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i count the number of unique values in a given column?
Try this kind of formula:
=SUMPRODUCT((A1:A1000<"")/COUNTIF(A1:A1000,A1:A1000&"")) Adjust range references to suit your situation. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Bobby" wrote: I have a huge data extract that i'm trying to sort through. The first thing I want to do is count the number of unique entries in a given column. For example, in my extract a "name" comes up many times - i want to count how many "unique" names I have on my data sheet, in a specific column. I'm pretty sure this is easily done with pivot tables - but can't figure it out myself. (the pivot table keeps summing stuff up - it's not "counting"). |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i count the number of unique values in a given column?
If you download and install the free add-in Morefunc.xll, you can use
COUNTDIFF... http://xcell05.free.fr/english/morefunc/countdiff.htm Otherwise, try... =SUMPRODUCT((Range<"")/COUNTIF(Range,Range&"")) Hope this helps! In article , Bobby wrote: I have a huge data extract that i'm trying to sort through. The first thing I want to do is count the number of unique entries in a given column. For example, in my extract a "name" comes up many times - i want to count how many "unique" names I have on my data sheet, in a specific column. I'm pretty sure this is easily done with pivot tables - but can't figure it out myself. (the pivot table keeps summing stuff up - it's not "counting"). |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i count the number of unique values in a given column?
First, thanks so much for your quick reply!
I created a VERY simple spreadshett to test this out. I have 2 columns: A is "names" and B is "numbers". Under A i have "bobby" listed 3 times and "susy" listed 1 time. Under B i have 1, 2, 3, 4. I wasn't sure if the forumla you provided was to be used through pivot table, so what i did was simply insert it in cell A6 (just below the last name in column A). I edited the formula to read: =SUMPRODUCT((A2:A5<"")/COUNTIF(A2:A5,A2:A5&"")) - it worked PERFECT!!! Thank you so much! I wanted to ask you - is the way i have done it what you intended - or were you thinking of using this formula in a pivot table? somehow? "Ron Coderre" wrote: Try this kind of formula: =SUMPRODUCT((A1:A1000<"")/COUNTIF(A1:A1000,A1:A1000&"")) Adjust range references to suit your situation. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Bobby" wrote: I have a huge data extract that i'm trying to sort through. The first thing I want to do is count the number of unique entries in a given column. For example, in my extract a "name" comes up many times - i want to count how many "unique" names I have on my data sheet, in a specific column. I'm pretty sure this is easily done with pivot tables - but can't figure it out myself. (the pivot table keeps summing stuff up - it's not "counting"). |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i count the number of unique values in a given column?
You got it right.....That formula works fine without a pivot table.
(Thanks for letting me know it worked for you) *********** Regards, Ron XL2002, WinXP "Bobby" wrote: First, thanks so much for your quick reply! I created a VERY simple spreadshett to test this out. I have 2 columns: A is "names" and B is "numbers". Under A i have "bobby" listed 3 times and "susy" listed 1 time. Under B i have 1, 2, 3, 4. I wasn't sure if the forumla you provided was to be used through pivot table, so what i did was simply insert it in cell A6 (just below the last name in column A). I edited the formula to read: =SUMPRODUCT((A2:A5<"")/COUNTIF(A2:A5,A2:A5&"")) - it worked PERFECT!!! Thank you so much! I wanted to ask you - is the way i have done it what you intended - or were you thinking of using this formula in a pivot table? somehow? "Ron Coderre" wrote: Try this kind of formula: =SUMPRODUCT((A1:A1000<"")/COUNTIF(A1:A1000,A1:A1000&"")) Adjust range references to suit your situation. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Bobby" wrote: I have a huge data extract that i'm trying to sort through. The first thing I want to do is count the number of unique entries in a given column. For example, in my extract a "name" comes up many times - i want to count how many "unique" names I have on my data sheet, in a specific column. I'm pretty sure this is easily done with pivot tables - but can't figure it out myself. (the pivot table keeps summing stuff up - it's not "counting"). |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i count the number of unique values in a given column?
Thanks again. If i could ask you one more question - what is it in the
formula that determines not to count a "blank" cell? This is exactly what I want it to do - just curious what part of the formula makes that determination. "Ron Coderre" wrote: You got it right.....That formula works fine without a pivot table. (Thanks for letting me know it worked for you) *********** Regards, Ron XL2002, WinXP "Bobby" wrote: First, thanks so much for your quick reply! I created a VERY simple spreadshett to test this out. I have 2 columns: A is "names" and B is "numbers". Under A i have "bobby" listed 3 times and "susy" listed 1 time. Under B i have 1, 2, 3, 4. I wasn't sure if the forumla you provided was to be used through pivot table, so what i did was simply insert it in cell A6 (just below the last name in column A). I edited the formula to read: =SUMPRODUCT((A2:A5<"")/COUNTIF(A2:A5,A2:A5&"")) - it worked PERFECT!!! Thank you so much! I wanted to ask you - is the way i have done it what you intended - or were you thinking of using this formula in a pivot table? somehow? "Ron Coderre" wrote: Try this kind of formula: =SUMPRODUCT((A1:A1000<"")/COUNTIF(A1:A1000,A1:A1000&"")) Adjust range references to suit your situation. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Bobby" wrote: I have a huge data extract that i'm trying to sort through. The first thing I want to do is count the number of unique entries in a given column. For example, in my extract a "name" comes up many times - i want to count how many "unique" names I have on my data sheet, in a specific column. I'm pretty sure this is easily done with pivot tables - but can't figure it out myself. (the pivot table keeps summing stuff up - it's not "counting"). |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i count the number of unique values in a given column?
Regarding:
=SUMPRODUCT((A1:A1000<"")/COUNTIF(A1:A1000,A1:A1000&"")) To give credit where credit is due: That formula was developed by Aladin Akyurek and Harlan Grove. Within that formula's context (A1:A1000<"") checks if a cell value does not equal an empty string. It returns a 1 for non-blanks and a 0 for blanks. In the COUNTIF(A1:A1000,A2:A10&"") section, this part: A1:A1000&"" ensures that the COUNTIF function will always return at least 1 and never 0 (which would cause the formula to error out when the division is performed.) Consequently, the numerator for blank cells is 0, so they are not counted. The numerator for all non-blanks is 1. The denominator for all non-blanks is their count. If a value appears 3 times, three of the numerators will be 1 and their respective denominators will be 3. The SUMPRODUCT function will add those three fractions: (1/3)+(1/3)+(1/3)=1 That's how the three occurrences only count as a single instance of a unique value. Does that help? *********** Regards, Ron XL2002, WinXP "Bobby" wrote: Thanks again. If i could ask you one more question - what is it in the formula that determines not to count a "blank" cell? This is exactly what I want it to do - just curious what part of the formula makes that determination. "Ron Coderre" wrote: You got it right.....That formula works fine without a pivot table. (Thanks for letting me know it worked for you) *********** Regards, Ron XL2002, WinXP "Bobby" wrote: First, thanks so much for your quick reply! I created a VERY simple spreadshett to test this out. I have 2 columns: A is "names" and B is "numbers". Under A i have "bobby" listed 3 times and "susy" listed 1 time. Under B i have 1, 2, 3, 4. I wasn't sure if the forumla you provided was to be used through pivot table, so what i did was simply insert it in cell A6 (just below the last name in column A). I edited the formula to read: =SUMPRODUCT((A2:A5<"")/COUNTIF(A2:A5,A2:A5&"")) - it worked PERFECT!!! Thank you so much! I wanted to ask you - is the way i have done it what you intended - or were you thinking of using this formula in a pivot table? somehow? "Ron Coderre" wrote: Try this kind of formula: =SUMPRODUCT((A1:A1000<"")/COUNTIF(A1:A1000,A1:A1000&"")) Adjust range references to suit your situation. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Bobby" wrote: I have a huge data extract that i'm trying to sort through. The first thing I want to do is count the number of unique entries in a given column. For example, in my extract a "name" comes up many times - i want to count how many "unique" names I have on my data sheet, in a specific column. I'm pretty sure this is easily done with pivot tables - but can't figure it out myself. (the pivot table keeps summing stuff up - it's not "counting"). |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i count the number of unique values in a given column?
Thanks Ron!
"Ron Coderre" wrote: Regarding: =SUMPRODUCT((A1:A1000<"")/COUNTIF(A1:A1000,A1:A1000&"")) To give credit where credit is due: That formula was developed by Aladin Akyurek and Harlan Grove. Within that formula's context (A1:A1000<"") checks if a cell value does not equal an empty string. It returns a 1 for non-blanks and a 0 for blanks. In the COUNTIF(A1:A1000,A2:A10&"") section, this part: A1:A1000&"" ensures that the COUNTIF function will always return at least 1 and never 0 (which would cause the formula to error out when the division is performed.) Consequently, the numerator for blank cells is 0, so they are not counted. The numerator for all non-blanks is 1. The denominator for all non-blanks is their count. If a value appears 3 times, three of the numerators will be 1 and their respective denominators will be 3. The SUMPRODUCT function will add those three fractions: (1/3)+(1/3)+(1/3)=1 That's how the three occurrences only count as a single instance of a unique value. Does that help? *********** Regards, Ron XL2002, WinXP "Bobby" wrote: Thanks again. If i could ask you one more question - what is it in the formula that determines not to count a "blank" cell? This is exactly what I want it to do - just curious what part of the formula makes that determination. "Ron Coderre" wrote: You got it right.....That formula works fine without a pivot table. (Thanks for letting me know it worked for you) *********** Regards, Ron XL2002, WinXP "Bobby" wrote: First, thanks so much for your quick reply! I created a VERY simple spreadshett to test this out. I have 2 columns: A is "names" and B is "numbers". Under A i have "bobby" listed 3 times and "susy" listed 1 time. Under B i have 1, 2, 3, 4. I wasn't sure if the forumla you provided was to be used through pivot table, so what i did was simply insert it in cell A6 (just below the last name in column A). I edited the formula to read: =SUMPRODUCT((A2:A5<"")/COUNTIF(A2:A5,A2:A5&"")) - it worked PERFECT!!! Thank you so much! I wanted to ask you - is the way i have done it what you intended - or were you thinking of using this formula in a pivot table? somehow? "Ron Coderre" wrote: Try this kind of formula: =SUMPRODUCT((A1:A1000<"")/COUNTIF(A1:A1000,A1:A1000&"")) Adjust range references to suit your situation. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Bobby" wrote: I have a huge data extract that i'm trying to sort through. The first thing I want to do is count the number of unique entries in a given column. For example, in my extract a "name" comes up many times - i want to count how many "unique" names I have on my data sheet, in a specific column. I'm pretty sure this is easily done with pivot tables - but can't figure it out myself. (the pivot table keeps summing stuff up - it's not "counting"). |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i count the number of unique values in a given column?
Thanks Domenic. This is the same suggestion i received from Ron - and it
works pefectly! "Domenic" wrote: If you download and install the free add-in Morefunc.xll, you can use COUNTDIFF... http://xcell05.free.fr/english/morefunc/countdiff.htm Otherwise, try... =SUMPRODUCT((Range<"")/COUNTIF(Range,Range&"")) Hope this helps! In article , Bobby wrote: I have a huge data extract that i'm trying to sort through. The first thing I want to do is count the number of unique entries in a given column. For example, in my extract a "name" comes up many times - i want to count how many "unique" names I have on my data sheet, in a specific column. I'm pretty sure this is easily done with pivot tables - but can't figure it out myself. (the pivot table keeps summing stuff up - it's not "counting"). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count unique values among duplicates in a subtotal range | Excel Discussion (Misc queries) | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Match formula that pulls unique values from another column? | Excel Discussion (Misc queries) | |||
Count unique values and create list based on these values | Excel Worksheet Functions | |||
How do I sort a column a unique number? | Excel Worksheet Functions |