+ Reply to Thread
Results 1 to 5 of 5

Count how many times a number appears within a range of two numbers

  1. #1
    Registered User
    Join Date
    02-18-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    8

    Count how many times a number appears within a range of two numbers

    Hi,
    I want to find how many entries are from a certain state (in Australia) e.g. how many people who are in victoria, which has a postcode range between 3000-3999. I have the people's postcode within my data.
    I have looked through the forums and the solutions posted there didn't work for me!!
    I have quite a big data set, so i would prefer not to do it by hand!
    Thanks,
    Marissa

  2. #2
    Valued Forum Contributor ratcat's Avatar
    Join Date
    03-07-2008
    Location
    Rural NSW, Australia
    MS-Off Ver
    Vista 2007
    Posts
    1,111

    Re: Count how many times a number appears within a range of two numbers

    G'day and welcome to the formula

    Try this,

    Please Login or Register  to view this content.

    Remember to adjust the range to suit your data layout

    HTH
    Have I made you happy ??? If yes, please make me happy by pressing the http://www.excelforum.com/images/buttons/reputation-40b.png Add Reputation button in my post.
    Please don't forget to do the same to other contributors of this forum.

    Thanks
    I don't void confusion, I create it

  3. #3
    Registered User
    Join Date
    02-18-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Count how many times a number appears within a range of two numbers

    this one doesn't seem to work...it returns a number almost double what it's supposed to be!

  4. #4
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Count how many times a number appears within a range of two numbers

    Try this one:

    Please Login or Register  to view this content.
    adjust the range as required, and use "Ctrl-Shift-Enter" once you've copied it to the formula bar.

    HTH
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  5. #5
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474

    Re: Count how many times a number appears within a range of two numbers

    Here's another way... Assuming that B2:B100 contains the postal code, try...

    =COUNTIF(B2:B100,">=3000")-COUNTIF(B2:B100,">3999")

    Hope this helps!

    xl-central.com

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1