Jun 16 2020 01:48 PM - last edited on Jun 17 2020 07:39 AM by Christine Karambelas
Jun 16 2020 01:48 PM - last edited on Jun 17 2020 07:39 AM by Christine Karambelas
Hello, I have a 42 mailing lists that each contain 400 addresses and several zip codes. I need to know how many records are in each zip. Is there a formula I can plug in to get this number. Now I am sorting the zip code column and scrolling through each zip and getting the count that way. I am so hoping there is a quicker way. Thank you for all your help
Jun 16 2020 02:52 PM
Yes there is a method.
I'll let you know the formula. In the meantime, I've notified the moderator of these boards to remove the file you linked to your original posting. You should not post files that contain personal address (or other kinds of) private information.
Whenever you post files here, they're basically accessible to the world; I'm sure you would not want your own contact info broadcast widely. Post a sample without any real people's names or addresses.
Jun 16 2020 03:00 PM - edited Jun 16 2020 03:12 PM
Solution
By far the easiest method does depend on your having the most recent version of Excel. These newest versions support a function called UNIQUE. You can use that to write a formula along these lines:
=UNIQUE(M2:M401)
in which M2:M401 refers to the entire column under the heading of Zip Code
then in the cell next to the resulting list you enter the formula
=COUNTIF($M$2:$M$401,P2)
in which the results of the UNIQUE formula, which begins in cell P2, are compared with the values in that original column of all the zip codes.
Since I'd already looked at your file, I used those formulas to get the following results:
I then destroyed my copy of your file.
Jun 16 2020 07:46 PM
Without posting private information this time, can you post a copy of the spreadsheet with a few dummy names. Without that, there's no way I or any else can tell you what's gone wrong.
Jun 17 2020 12:55 AM
Jun 17 2020 07:25 AM
Jun 16 2020 03:00 PM - edited Jun 16 2020 03:12 PM
Solution
By far the easiest method does depend on your having the most recent version of Excel. These newest versions support a function called UNIQUE. You can use that to write a formula along these lines:
=UNIQUE(M2:M401)
in which M2:M401 refers to the entire column under the heading of Zip Code
then in the cell next to the resulting list you enter the formula
=COUNTIF($M$2:$M$401,P2)
in which the results of the UNIQUE formula, which begins in cell P2, are compared with the values in that original column of all the zip codes.
Since I'd already looked at your file, I used those formulas to get the following results:
I then destroyed my copy of your file.