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 here:
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 here:
> 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
>