+ Reply to Thread
Results 1 to 12 of 12

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

  1. 09-13-2012, 07:45 PM #1
    Security
    Security is offline
    Registered User
    Join Date
    09-13-2012
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    7

    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?

    I appreciate your help!
    Last edited by Security; 09-14-2012 at 10:36 AM.
    Reply With QuoteRegister To Reply
  2. 09-13-2012, 07:59 PM #2
    BobBing
    BobBing is offline
    Registered User
    Join Date
    12-28-2011
    Location
    Here
    MS-Off Ver
    Excel 2007
    Posts
    43

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

    Try: =Countif(A:A,"essex")
    Reply With QuoteRegister To Reply
  3. 09-13-2012, 08:19 PM #3
    Security
    Security is offline
    Registered User
    Join Date
    09-13-2012
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    7

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

    Quote Originally Posted by BobBing View Post
    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?
    Reply With QuoteRegister To Reply
  4. 09-13-2012, 08:35 PM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    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?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  5. 09-13-2012, 09:25 PM #5
    Security
    Security is offline
    Registered User
    Join Date
    09-13-2012
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    7

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

    ignore this post
    Reply With QuoteRegister To Reply
  6. 09-13-2012, 09:27 PM #6
    Security
    Security is offline
    Registered User
    Join Date
    09-13-2012
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    7

    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
    Reply With QuoteRegister To Reply
  7. 09-13-2012, 10:12 PM #7
    icestationzbra
    icestationzbra is offline
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    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.
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -
    Reply With QuoteRegister To Reply
  8. 09-13-2012, 10:14 PM #8
    Security
    Security is offline
    Registered User
    Join Date
    09-13-2012
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    7

    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.
    Reply With QuoteRegister To Reply
  9. 09-13-2012, 10:34 PM #9
    icestationzbra
    icestationzbra is offline
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    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.

    if you get stuck, holler back and we can help you.
    Reply With QuoteRegister To Reply
  10. 09-13-2012, 10:50 PM #10
    Security
    Security is offline
    Registered User
    Join Date
    09-13-2012
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    7

    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!
    Reply With QuoteRegister To Reply
  11. 09-13-2012, 10:59 PM #11
    icestationzbra
    icestationzbra is offline
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    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
    Last edited by icestationzbra; 09-13-2012 at 11:16 PM.
    Reply With QuoteRegister To Reply
  12. 09-14-2012, 10:37 AM #12
    Security
    Security is offline
    Registered User
    Join Date
    09-13-2012
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    7

    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!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1