Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting a unique list, then counting in a range
I have a bunch of blocks of 5 x 5 that look like this:
1,3,7,3,7 2,7,3,7,2 2,4,8,8,2 1,2,8,4,7 1,4,7,4,2 What I want to do is have a summary of the count of each value like the following: Item Count 1 3 2 6 3 3 4 4 7 6 8 3 I know I can do a CountIf by just creating the item list myself, but I would like the timelist to autogenerate as well. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting a unique list, then counting in a range
Hi,
I'm not sure what you mean by auto generate the time list? Countif automatically updates as does any other function if the data changes. If you don't want to update automatically, you could use a pivot table which would then update when you pressed refresh. For that though you should put your data in a more database type layout. Another alternative is to write a VBA macro. -- Thanks, Shane Devenshire "JN" wrote: I have a bunch of blocks of 5 x 5 that look like this: 1,3,7,3,7 2,7,3,7,2 2,4,8,8,2 1,2,8,4,7 1,4,7,4,2 What I want to do is have a summary of the count of each value like the following: Item Count 1 3 2 6 3 3 4 4 7 6 8 3 I know I can do a CountIf by just creating the item list myself, but I would like the timelist to autogenerate as well. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting a unique list, then counting in a range
JN wrote:
I have a bunch of blocks of 5 x 5 that look like this: 1,3,7,3,7 2,7,3,7,2 2,4,8,8,2 1,2,8,4,7 1,4,7,4,2 What I want to do is have a summary of the count of each value like the following: Item Count 1 3 2 6 3 3 4 4 7 6 8 3 I know I can do a CountIf by just creating the item list myself, but I would like the timelist to autogenerate as well. With your data in A1:E5, enter the following: F1 =SMALL($A$1:$E$5,ROW()) F2 =IF(OR(F1="",F1=MAX($A$1:$E$5)),"",SMALL(IF($A$1:$ E$5F1,$A$1:$E$5,""),1)) *** F2 is an array formula (press CTRL+SHIFT+ENTER to enter the formula) Fill F2 down to F25 G1 =IF(F1="","",COUNTIF($A$1:$E$5,F1)) Fill G1 down to G25 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting a unique list, then counting in a range
Thank you. This appears to work for the numbers. Is there a function like
SMALL that works for text values? "Glenn" wrote in message ... JN wrote: I have a bunch of blocks of 5 x 5 that look like this: 1,3,7,3,7 2,7,3,7,2 2,4,8,8,2 1,2,8,4,7 1,4,7,4,2 What I want to do is have a summary of the count of each value like the following: Item Count 1 3 2 6 3 3 4 4 7 6 8 3 I know I can do a CountIf by just creating the item list myself, but I would like the timelist to autogenerate as well. With your data in A1:E5, enter the following: F1 =SMALL($A$1:$E$5,ROW()) F2 =IF(OR(F1="",F1=MAX($A$1:$E$5)),"",SMALL(IF($A$1:$ E$5F1,$A$1:$E$5,""),1)) *** F2 is an array formula (press CTRL+SHIFT+ENTER to enter the formula) Fill F2 down to F25 G1 =IF(F1="","",COUNTIF($A$1:$E$5,F1)) Fill G1 down to G25 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Unique Values by Date Range | Excel Worksheet Functions | |||
Counting Unique Alpahnumeric's in a list | Excel Worksheet Functions | |||
Counting Unique Part Numbers In A Range | Excel Worksheet Functions | |||
counting unique entries in a list | Excel Discussion (Misc queries) | |||
Counting unique text/number cells from a range | Excel Discussion (Misc queries) |