count occurrences of values in a column

[englishrob]

What would be the best way to count the number of occurrences of each value in a column?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I tried to create a pivot table, but could only get it to work if I placed a 1 in the adjacent cell next to each value, i.e. another column. I can figure it out using procedural style code in VBA (test for change of value in cell below) but guess there must be a better way using ranges. I’m ok with VBA but not done much on Pivot Tables.<o></o>
<o></o>
Sample data:<o></o>
<o></o>
Sun<o></o>
Sun<o></o>
Sun<o></o>
Sun<o></o>
<o></o>
To show 4 next to the last Sun<o></o>
<o></o>
Mon<o></o>
Mon<o></o>
<o></o>
To show 2 next to the last Mon<o></o>
<o></o>
Tue<o></o>
Tue<o></o>
Tue<o></o>
Tue<o></o>
Tue<o></o>
Tue<o></o>
Tue<o></o>
<o></o>
To show 7 next to the last Tue<o></o>
<o></o>
Thanks<o></o>
<o></o>

 

[DonkeyOte]

You can use a Pivot to give a Day with Count by setting the Day field as both Row & Data Field (the latter should default to Count by default).

 

[dave3009]

With a pivot table you'd just drag the day column into both row labels and value fields.

Excel Workbook
	A
1	Day
2	Sun
3	Sun
4	Sun
5	Sun
6	Mon
7	Mon
8	Tue
9	Tue
10	Tue
11	Tue
12	Tue
13	Tue
14	Tue
Sheet4

Excel 2007

Excel Workbook
	A	B
3	Row Labels	Count of Day
4	Mon	2
5	Tue	7
6	Sun	4
7	Grand Total	13
Sheet5

Excel 2007

 

[VoG]

Try this - formula in B1 copied down

Excel Workbook
	A	B
1	Sun
2	Sun
3	Sun
4	Sun	4
5	Mon
6	Mon	2
7	Tue
8	Tue
9	Tue
10	Tue
11	Tue
12	Tue
13	Tue	7
Sheet2

 

[Rick Rothstein]

Assuming we are talking about Column A, then this formula seems to work...

=IF(COUNTIF(A$1:A1,A1)=COUNTIF(A:A,A1),COUNTIF(A:A,A1),"")

Note: This formula will work even if the data is not sorted by the contents of Column A.

 

[englishrob]

thank you all very much . simple stuff, I know, but it's been a great help.