# counting values down a column

I would like to count down a column, and count the number of different values

what is the best way to do this ?
###### Who is Participating?

Commented:
You can create a quick Pivot Table (i.e Insert|Pivot Table), make sure the column has a header first.  Then drag the header title to Row area, then drag it in the Summation area, then click on that and select from Value Field Setting: Count.

Alternatively select the column go to Data, Advanced from Sort & Filter section, then check Unique Values only.  Select Copy to Another location and select that location in the Copy to field.  Click Ok.  A unique listing should show up.  Then you can use COUNTIF to count

=Countif(A:A,H1)

WHERE column A contains the original list, and the unique list starts at H1.

Copy formula down.

Alternatively, manually list all the unique values, say starting in H1, and use the COUNTIF formula above....
0

Commented:
Are you trying to count unique values in a range?

eg

=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&""))

should count unique values in range A2:A100

0

Chief slayer of dragonsAuthor Commented:
I have a list. (for simplicity I have a simple test list where I know what the result shoud roughly be)

1
1
1
2
2
2
2
3
3
3

Id like to end up with a result where all the instances of each value are counted so
1 - 3
2 - 4
3 - 3
0

Commented:
USE #1 FOR THE KEY

=IFERROR(INDEX(\$A\$1:\$A20,MATCH(0,INDEX(COUNTIF(\$C\$1:C1,\$A\$1:\$A\$20),0,0),0)),"")

USE #2 FOR THE VALUE

=IF(C2<>"",COUNTIF(\$A\$1:\$A\$20,C2),IF(C1<>"",SUM(OFFSET(D2,-ROW()+1,0,ROW()-1)),""))
Book1.xlsx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.