Getting a unique list, then counting in a range

[JN]

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.

 

[ShaneDevenshire]

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.

 

[Glenn]

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$5>F1,$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