| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Num of Unique items in col ?
I need to be able to count the number of times an item Uniquely appears in a
column. IE: Result cell NAMED: Species Contents of column: CAT CAT DOG CAT FISH FISH Species should = 3 How can I do this function in EXCEL 2003? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Num of Unique items in col ?
=SUMPRODUCT((H1:H1500<"")/COUNTIF(H1:H1500,H1:H1500&""))
for data in column H. Adjust to suit -- Gary''s Student - gsnu200849 "NaplesDave" wrote: I need to be able to count the number of times an item Uniquely appears in a column. IE: Result cell NAMED: Species Contents of column: CAT CAT DOG CAT FISH FISH Species should = 3 How can I do this function in EXCEL 2003? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Num of Unique items in col ?
"Gary''s Student" wrote: =SUMPRODUCT((H1:H1500<"")/COUNTIF(H1:H1500,H1:H1500&"")) for data in column H. Adjust to suit -- Gary''s Student - gsnu200849 "NaplesDave" wrote: I need to be able to count the number of times an item Uniquely appears in a column. IE: Result cell NAMED: Species Contents of column: CAT CAT DOG CAT FISH FISH Species should = 3 How can I do this function in EXCEL 2003? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Num of Unique items in col ?
Ok. Thanks. Now, how do I count the number of unique items by 3 other
criteria ie number of cats sold at location1 by ?salesperson by date "Gary''s Student" wrote: =SUMPRODUCT((H1:H1500<"")/COUNTIF(H1:H1500,H1:H1500&"")) for data in column H. Adjust to suit -- Gary''s Student - gsnu200849 "NaplesDave" wrote: I need to be able to count the number of times an item Uniquely appears in a column. IE: Result cell NAMED: Species Contents of column: CAT CAT DOG CAT FISH FISH Species should = 3 How can I do this function in EXCEL 2003? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Num of Unique items in col ?
Hello,
I suggest to use my UDF Pfreq: http://www.sulprobil.com/html/pfreq.html IMHO Sumproduct is highly overrated: http://www.sulprobil.com/html/sumproduct.html If you just need to count unique entries of a list see this comparison of approaches, please: http://www.sulprobil.com/html/count_unique.html [Keep in mind that the chart shows logarithmic scales, please!] The approach with Sumproduct divided by Countif is one of the worst and therefore found a place on my list of Excel Dont's: http://www.sulprobil.com/html/excel_don_ts.html Regards, Bernd |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Num of Unique items in col ?
Can you pl. explain how this works?
"Gary''s Student" wrote: =SUMPRODUCT((H1:H1500<"")/COUNTIF(H1:H1500,H1:H1500&"")) for data in column H. Adjust to suit -- Gary''s Student - gsnu200849 "NaplesDave" wrote: I need to be able to count the number of times an item Uniquely appears in a column. IE: Result cell NAMED: Species Contents of column: CAT CAT DOG CAT FISH FISH Species should = 3 How can I do this function in EXCEL 2003? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Num of Unique items in col ?
Can you pl. explain how this works?
=SUMPRODUCT((H1:H1500<"")/COUNTIF(H1:H1500,H1:H1500&"")) Try this... A1 = A A2 = B A3 = B A4 = C A5 = D =SUMPRODUCT((A1:A5<"")/COUNTIF(A1:A5,A1:A5&"")) Result is 4 Break it down into individual calculations. Enter this formula in C1 and copy down to C5: =A1<"" Enter this formula in D1 and copy down to D5: =COUNTIF(A$1:A$5,A1&"") Enter this formula in E1 and copy down to E5: =C1/D1 Enter this formula in F1: =SUM(E1:E5) That all makes sense, doesn't it? OK, delete the entry in A4. Now the result is 3 and it still makes sense. Ok, change the formula in D1 to: =COUNTIF(A$1:A$5,A1) Copy down to D5 and see what happens to those summary formulas. Concatenating the empty text string ("") prevents the #DIV/0! error when there are empty cells. If there were no empty cells then you could use: =SUMPRODUCT(1/COUNTIF(A1:A5,A1:A5)) =SUMPRODUCT((A1:A5<"")/COUNTIF(A1:A5,A1:A5&"")) Seems to have evolved as the "standard". expbiff101 -- Biff Microsoft Excel MVP "Sheeloo" wrote in message ... Can you pl. explain how this works? "Gary''s Student" wrote: =SUMPRODUCT((H1:H1500<"")/COUNTIF(H1:H1500,H1:H1500&"")) for data in column H. Adjust to suit -- Gary''s Student - gsnu200849 "NaplesDave" wrote: I need to be able to count the number of times an item Uniquely appears in a column. IE: Result cell NAMED: Species Contents of column: CAT CAT DOG CAT FISH FISH Species should = 3 How can I do this function in EXCEL 2003? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Num of Unique items in col ?
Thanks Biff (thanks to Jacob too),
I get it now. Beautiful... You are essentially dividing the count of each different number from 1 so that you get the unique count when you sum them up... I had tried to understand the same way but my mistake was that I entered 5 different characters down to row 30 .... obscuring the meaning. Thanks for the wonderful way you explained it. "T. Valko" wrote: Can you pl. explain how this works? =SUMPRODUCT((H1:H1500<"")/COUNTIF(H1:H1500,H1:H1500&"")) Try this... A1 = A A2 = B A3 = B A4 = C A5 = D =SUMPRODUCT((A1:A5<"")/COUNTIF(A1:A5,A1:A5&"")) Result is 4 Break it down into individual calculations. Enter this formula in C1 and copy down to C5: =A1<"" Enter this formula in D1 and copy down to D5: =COUNTIF(A$1:A$5,A1&"") Enter this formula in E1 and copy down to E5: =C1/D1 Enter this formula in F1: =SUM(E1:E5) That all makes sense, doesn't it? OK, delete the entry in A4. Now the result is 3 and it still makes sense. Ok, change the formula in D1 to: =COUNTIF(A$1:A$5,A1) Copy down to D5 and see what happens to those summary formulas. Concatenating the empty text string ("") prevents the #DIV/0! error when there are empty cells. If there were no empty cells then you could use: =SUMPRODUCT(1/COUNTIF(A1:A5,A1:A5)) =SUMPRODUCT((A1:A5<"")/COUNTIF(A1:A5,A1:A5&"")) Seems to have evolved as the "standard". expbiff101 -- Biff Microsoft Excel MVP "Sheeloo" wrote in message ... Can you pl. explain how this works? "Gary''s Student" wrote: =SUMPRODUCT((H1:H1500<"")/COUNTIF(H1:H1500,H1:H1500&"")) for data in column H. Adjust to suit -- Gary''s Student - gsnu200849 "NaplesDave" wrote: I need to be able to count the number of times an item Uniquely appears in a column. IE: Result cell NAMED: Species Contents of column: CAT CAT DOG CAT FISH FISH Species should = 3 How can I do this function in EXCEL 2003? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Num of Unique items in col ?
I love explaining things! I think I'd make a good teacher.
-- Biff Microsoft Excel MVP "Sheeloo" wrote in message ... Thanks Biff (thanks to Jacob too), I get it now. Beautiful... You are essentially dividing the count of each different number from 1 so that you get the unique count when you sum them up... I had tried to understand the same way but my mistake was that I entered 5 different characters down to row 30 .... obscuring the meaning. Thanks for the wonderful way you explained it. "T. Valko" wrote: Can you pl. explain how this works? =SUMPRODUCT((H1:H1500<"")/COUNTIF(H1:H1500,H1:H1500&"")) Try this... A1 = A A2 = B A3 = B A4 = C A5 = D =SUMPRODUCT((A1:A5<"")/COUNTIF(A1:A5,A1:A5&"")) Result is 4 Break it down into individual calculations. Enter this formula in C1 and copy down to C5: =A1<"" Enter this formula in D1 and copy down to D5: =COUNTIF(A$1:A$5,A1&"") Enter this formula in E1 and copy down to E5: =C1/D1 Enter this formula in F1: =SUM(E1:E5) That all makes sense, doesn't it? OK, delete the entry in A4. Now the result is 3 and it still makes sense. Ok, change the formula in D1 to: =COUNTIF(A$1:A$5,A1) Copy down to D5 and see what happens to those summary formulas. Concatenating the empty text string ("") prevents the #DIV/0! error when there are empty cells. If there were no empty cells then you could use: =SUMPRODUCT(1/COUNTIF(A1:A5,A1:A5)) =SUMPRODUCT((A1:A5<"")/COUNTIF(A1:A5,A1:A5&"")) Seems to have evolved as the "standard". expbiff101 -- Biff Microsoft Excel MVP "Sheeloo" wrote in message ... Can you pl. explain how this works? "Gary''s Student" wrote: =SUMPRODUCT((H1:H1500<"")/COUNTIF(H1:H1500,H1:H1500&"")) for data in column H. Adjust to suit -- Gary''s Student - gsnu200849 "NaplesDave" wrote: I need to be able to count the number of times an item Uniquely appears in a column. IE: Result cell NAMED: Species Contents of column: CAT CAT DOG CAT FISH FISH Species should = 3 How can I do this function in EXCEL 2003? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Num of Unique items in col ?
Dear Dave
Assuming your data is in Col A use the below formula =SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&"")) If this post helps click Yes --------------- Jacob Skaria "NaplesDave" wrote: I need to be able to count the number of times an item Uniquely appears in a column. IE: Result cell NAMED: Species Contents of column: CAT CAT DOG CAT FISH FISH Species should = 3 How can I do this function in EXCEL 2003? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I set up a formula to count only unique items in a column? | Excel Worksheet Functions | |||
| Count Unique Items | Excel Discussion (Misc queries) | |||
| Count unique items in range | Excel Worksheet Functions | |||
| Count unique items in groups | Excel Worksheet Functions | |||
| count unique items in ever-growing list? | Excel Discussion (Misc queries) | |||
Hybrid Mode
