Getting a unique list, then counting in a range

  • Thread starter Thread starter JN
  • Start date Start date
J

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.
 
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.
 
JN said:
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.
Click to expand...

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
 
Thank you. This appears to work for the numbers. Is there a function like
SMALL that works for text values?
 
Back
Top