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.

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.

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

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