Am using Excel 2003. I am trying to count the number of zip codes in a
column. I have already copied and pasted the zip codes into a separate sheet
in my workbook. I need to know how many 12345 zips, how many 12346 zips,
etc. So I want something like this as a result:

12345 2
12346 1
12347 3
12354 22

What I don't want is something like this:

12345 2
12345 2
12346 1
12347 3
12347 3
12347 3
(this is assuming that the list on the left is my original list of zip codes)

So, I need something to display the zips in one column (not necessarily the
original data column) and then the count of each occurance of the zip code.
I am trying to make a report to use with a bulk mailing. I keep thinking
that it's a countif statement, but I'm not having any luck. When I try a
pivot table (granted, I've only done a pivot table successfully on one other
project and it was a sum, so I could easily be doing this incorrectly) I only
get a count of all the zip codes in my column.
However, if all I can get is a count like in my example (whether with a
countif or with a pivot or with a nested statement), I can work with it.

Thanks in advance for any help.

Add a header to your data and you can get a list of unique zipcodes by using
Data|Filter|Advanced filter.

Debra Dalgleish explains it he
http://www.contextures.com/xladvfilter01.html#FilterUR

Then you can use formulas like this:

=countif('sheet1'!a:a,a2)
in B2 (and drag down)
to get a count for each of those unique entries.

Another option is to use data|pivottable to create a nice summary.


cjlatta wrote:

Am using Excel 2003. I am trying to count the number of zip codes in a
column. I have already copied and pasted the zip codes into a separate sheet
in my workbook. I need to know how many 12345 zips, how many 12346 zips,
etc. So I want something like this as a result:

12345 2
12346 1
12347 3
12354 22

What I don't want is something like this:

12345 2
12345 2
12346 1
12347 3
12347 3
12347 3
(this is assuming that the list on the left is my original list of zip codes)

So, I need something to display the zips in one column (not necessarily the
original data column) and then the count of each occurance of the zip code.
I am trying to make a report to use with a bulk mailing. I keep thinking
that it's a countif statement, but I'm not having any luck. When I try a
pivot table (granted, I've only done a pivot table successfully on one other
project and it was a sum, so I could easily be doing this incorrectly) I only
get a count of all the zip codes in my column.
However, if all I can get is a count like in my example (whether with a
countif or with a pivot or with a nested statement), I can work with it.

Thanks in advance for any help.

--

Dave Peterson

That did the trick. I never knew about the advanced filter trick. It didn't
do a clean unique list (I did have a couple of duplicated numbers), but the
list was short enough that I could easily edit.
I'm still not sure how to do the pivot table option you mentioned, but I'm
happy with what I have. I wouldn't mind learning how to use the pivot table
feature for this problem.
Thanks for all!

"Dave Peterson" wrote:

Add a header to your data and you can get a list of unique zipcodes by using
Data|Filter|Advanced filter.

Debra Dalgleish explains it he
http://www.contextures.com/xladvfilter01.html#FilterUR

Then you can use formulas like this:

=countif('sheet1'!a:a,a2)
in B2 (and drag down)
to get a count for each of those unique entries.

Another option is to use data|pivottable to create a nice summary.


cjlatta wrote:

Am using Excel 2003. I am trying to count the number of zip codes in a
column. I have already copied and pasted the zip codes into a separate sheet
in my workbook. I need to know how many 12345 zips, how many 12346 zips,
etc. So I want something like this as a result:

12345 2
12346 1
12347 3
12354 22

What I don't want is something like this:

12345 2
12345 2
12346 1
12347 3
12347 3
12347 3
(this is assuming that the list on the left is my original list of zip codes)

So, I need something to display the zips in one column (not necessarily the
original data column) and then the count of each occurance of the zip code.
I am trying to make a report to use with a bulk mailing. I keep thinking
that it's a countif statement, but I'm not having any luck. When I try a
pivot table (granted, I've only done a pivot table successfully on one other
project and it was a sum, so I could easily be doing this incorrectly) I only
get a count of all the zip codes in my column.
However, if all I can get is a count like in my example (whether with a
countif or with a pivot or with a nested statement), I can work with it.

Thanks in advance for any help.

--

Dave Peterson