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 ?
Europa MacDonaldChief slayer of dragonsAsked:
Who is Participating?
 
NBVCConnect With a Mentor 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
 
NBVCConnect With a Mentor 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

adjust ranges to suit.
0
 
Europa MacDonaldChief 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
 
andrew_manConnect With a Mentor 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.

All Courses

From novice to tech pro — start learning today.