SOLVED

Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1469206%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1469206%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F701397%22%20target%3D%22_blank%22%3E%40carolyncarolynrodgers%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%20there%20is%20a%20method.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'll%20let%20you%20know%20the%20formula.%20In%20the%20meantime%2C%20I've%20notified%20the%20moderator%20of%20these%20boards%20to%20remove%20the%20file%20you%20linked%20to%20your%20original%20posting.%20You%20should%20not%20post%20files%20that%20contain%20personal%20address%20(or%20other%20kinds%20of)%20private%20information.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhenever%20you%20post%20files%20here%2C%20they're%20basically%20accessible%20to%20the%20world%3B%20I'm%20sure%20you%20would%20not%20want%20your%20own%20contact%20info%20broadcast%20widely.%20Post%20a%20sample%20without%20any%20real%20people's%20names%20or%20addresses.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1469226%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1469226%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F701397%22%20target%3D%22_blank%22%3E%40carolyncarolynrodgers%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBy%20far%20the%20easiest%20method%20does%20depend%20on%20your%20having%20the%20most%20recent%20version%20of%20Excel.%20These%20newest%20versions%20support%20a%20function%20called%20UNIQUE.%20You%20can%20use%20that%20to%20write%20a%20formula%20along%20these%20lines%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DUNIQUE(M2%3AM401)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Ein%20which%20M2%3AM401%20refers%20to%20the%20entire%20column%20under%20the%20heading%20of%20Zip%20Code%3C%2FP%3E%3CP%3Ethen%20in%20the%20cell%20next%20to%20the%20resulting%20list%20you%20enter%20the%20formula%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DCOUNTIF(%24M%242%3A%24M%24401%2CP2)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Ein%20which%20the%20results%20of%20the%20UNIQUE%20formula%2C%20which%20begins%20in%20cell%20P2%2C%20are%20compared%20with%20the%20values%20in%20that%20original%20column%20of%20all%20the%20zip%20codes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESince%20I'd%20already%20looked%20at%20your%20file%2C%20I%20used%20those%20formulas%20to%20get%20the%20following%20results%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1592344756897.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F199124iC9D31BA29A0CACE0%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22mathetes_0-1592344756897.png%22%20alt%3D%22mathetes_0-1592344756897.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20then%20destroyed%20my%20copy%20of%20your%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1469319%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1469319%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much%20for%20your%20help.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1469378%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1469378%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethis%20was%20working%20beautifully.%20%26nbsp%3BI%20dowloaded%20a%20google%20sheet%20to%20excel%20and%20now%20this%20does%20not%20work.%20HELP%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1469550%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1469550%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F701397%22%20target%3D%22_blank%22%3E%40carolyncarolynrodgers%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWithout%20posting%20private%20information%20this%20time%2C%20can%20you%20post%20a%20copy%20of%20the%20spreadsheet%20with%20a%20few%20dummy%20names.%20Without%20that%2C%20there's%20no%20way%20I%20or%20any%20else%20can%20tell%20you%20what's%20gone%20wrong.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1469907%22%20slang%3D%22zh-CN%22%3ERE%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1469907%22%20slang%3D%22zh-CN%22%3EIF%20HK'S%20ZIP%20IS%20%22852%22%20AND%20COLUMN%20T%20IS%20ZIP%20DATA%2C%20THEN%20SSUMPRODUCT%20(T%3AT%22852%22)%20X%201)%20%22%20CAN%20FIND%20OUT%20HOW%20HOW%20%22852%22%20IN%20COLUMN%20T%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1470804%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1470804%22%20slang%3D%22en-US%22%3EI%20figured%20out%20what%20I%20was%20doing%20wrong.%20I%20was%20not%20putting%20in%20the%20correct%20column%20letter.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1469124%22%20slang%3D%22en-US%22%3EExcel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1469124%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%20I%20have%20a%2042%20mailing%20lists%20that%20each%20contain%20400%20addresses%20and%20several%20zip%20codes.%26nbsp%3B%20I%20need%20to%20know%20how%20many%20records%20are%20in%20each%20zip.%26nbsp%3B%20Is%20there%20a%20formula%20I%20can%20plug%20in%20to%20get%20this%20number.%26nbsp%3B%20Now%20I%20am%20sorting%20the%20zip%20code%20column%20and%20scrolling%20through%20each%20zip%20and%20getting%20the%20count%20that%20way.%26nbsp%3B%20I%20am%20so%20hoping%20there%20is%20a%20quicker%20way.%26nbsp%3B%20Thank%20you%20for%20all%20your%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1469124%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
New Contributor

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

7 Replies
Highlighted

@carolyncarolynrodgers 

 

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.

Highlighted
Best Response confirmed by carolyncarolynrodgers (New Contributor)
Solution

@carolyncarolynrodgers 

 

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:

mathetes_0-1592344756897.png

I then destroyed my copy of your file.

Highlighted
Thank you so much for your help.
Highlighted

@mathetes 

this was working beautifully.  I dowloaded a google sheet to excel and now this does not work. HELP

 

 

Highlighted

@carolyncarolynrodgers 

 

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.

Highlighted
IF HK'S ZIP IS "852" AND column T IS ZIP DATA, THEN [=SUMPRODUCT((T:T="852")*1)] CAN FIND OUT HOW MANY "852" IN column T
Highlighted
I figured out what I was doing wrong. I was not putting in the correct column letter.