# need formula to count number of cells that contain any of a LARGE list of zip codes

1. ## need formula to count number of cells that contain any of a LARGE list of zip codes

I have a list of random customer zip codes in column A. Because of the leading zero problem, they are formatted as text.

I have a list of all zip codes in Essex County in column B. It is also formatted as text. I named that range "essex".

I want to be able to answer the following question: "How many people in column A are from Essex County?"

I have tried =COUNTIF(A:A,essex) and I have tried =COUNTIF(A:A,B:B) Neither formula works. They both return 0, even though I have many Essex County zip codes in column A.

I know that I can use something like =COUNTIF(A:A,B1)+COUNTIF(A:A,B2)+COUNTIF(A:A,B3) etc. But that will get very long very quickly! Is there a simpler formula?

2. ## Re: need formula to count number of cells that contain any of a LARGE list of zip codes

Try: =Countif(A:A,"essex")

3. ## Re: need formula to count number of cells that contain any of a LARGE list of zip codes

Originally Posted by BobBing
Try: =Countif(A:A,"essex")
Thanks for the attempt. Unfortunately, that formula doesn't work either. It returns a 0 result. I assume it's because it's searching column A for the actual word "essex", which does not exist in that column.

Any other ideas?

4. ## Re: need formula to count number of cells that contain any of a LARGE list of zip codes

you are correct in thinking that formula is looking for the WORD essex...

any chance of a small sample file?

5. ## Re: need formula to count number of cells that contain any of a LARGE list of zip codes

ignore this post

6. ## Re: need formula to count number of cells that contain any of a LARGE list of zip codes

Sorry for the delay: here's a sample file. (The actual file has private customer info, so I just made a fake file with the same info.)zipcount.xlsx

7. ## Re: need formula to count number of cells that contain any of a LARGE list of zip codes

try:

``Please Login or Register  to view this content.``

8. ## Re: need formula to count number of cells that contain any of a LARGE list of zip codes

TANFASTIC! Thank you!

Now, would you be willing to explain to me what the SUMPRODUCT function does and why that works? I'm a gal who likes fishing better than getting served fish.

9. ## Re: need formula to count number of cells that contain any of a LARGE list of zip codes

how about i just pointed you towards the rod, the bait and then the lake, and let you catch your own fish!

enter this formula in cell A19 on the spreadsheet that you provided in post #6. once the formula spits out the result, keep the cell highlighted, then use the keyboard shortcut ALT > M > V to invoke "Evaluate Formula" wizard. this wizard steps through every single step of calculation that goes on in the gut of Excel. once the wizard pops up, click on the Evaluate button (one step at a time) to see how the result shapes up. much like a sculptor chipping away at a chunk of granite to end up with a marvelous sculpture (not that dramatic, i suppose)! i call him or her the wizard because it is he or she who taught me Excel.

10. ## Re: need formula to count number of cells that contain any of a LARGE list of zip codes

Hmm...I walked through it, but it doesn't explain to me what SUMPRODUCT does or what -- does. I don't want to make you have to type up a whole bunch of nonsense just to educate me, but I was looking for the English language version of the awesome formula you wrote. For example, the English language version of the formula =SUM(A2:A23) would be "give me the sum of all of the numbers in the range sequential cells A2 through A23 inclusive".

So =SUMPRODUCT(--(COUNTIF(essex,C2:C16)>0)) would be "give me the sum of the products of the number of times a string of text from the range named essex shows up in the range sequential cells C2 to C16 inclusive when the cells are greater than zero."

See...I'm not getting that right, am I?! Because the two "--" aren't in my English language explanation at all!

11. ## Re: need formula to count number of cells that contain any of a LARGE list of zip codes

the -- are called double unary operators or double negs, in short. they are in effect two minus signs imposed on a value one after the other. they help to convert logical and textual value to corresponding numerical value, if one exists. so, --TRUE results in 1, --FALSE in 0, --"13-SEP-2012" into its DATEVALUE etc. if a number has been formatted as text, -- would right that wrong.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

12. ## Re: need formula to count number of cells that contain any of a LARGE list of zip codes

Thank you, again. This is really great info. And I'm gonna absorb the awesome info in that link over the next few days and start using =SUMPRODUCT like a master!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1