+ Reply to Thread
Results 1 to 6 of 6

Creating formulas to count cells that fall into specific ranges

  1. #1
    Registered User
    Join Date
    04-17-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2003
    Posts
    43

    Unhappy Creating formulas to count cells that fall into specific ranges

    Hello,
    I am trying to create a few formulas to count the number of cells whose values fall into a specified range. For example, I have ~1000 rows of cells containing ages ranging from 1 to 85, and I want to automatically count the number of cells that fall between 19-29, 30-39 and so on and so forth. The ages are in column 10 of Table 1 in my spreadsheet. The formula that I came up with is =SUM((Table1[[#All],[Column10]]>=19)+(Table1[[#All],[Column10]]<=29), but this has not been working. For calculating the number of cells whose values are less than 18 I use the formula =COUNTIF(Table1[[#All],[Column10]], "<18"), which works perfectly, but I do not know how to apply that to a range of numbers...
    I KNOW there is a way to do this, but I can't come up with it.
    Thank you to anyone that can provide some insight!

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Creating formulas to count cells that fall into specific ranges

    Use a helpcolumn in your data sheet.

    In this helpcolumn you can use a VLookup formula to find the range the data is in.

    After that you can use a pivot table or sumproduct to count the data.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    04-17-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2003
    Posts
    43

    Re: Creating formulas to count cells that fall into specific ranges

    Thank you, but I am afraid that I do not know how to do either of those things. That would take a lot more explaining haha

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Creating formulas to count cells that fall into specific ranges

    Maybe this...

    =COUNTIFS(Table1[[#All],[Column10]],">=19",Table1[[#All],[Column10]],"<=29")
    Last edited by Tony Valko; 04-19-2013 at 02:49 PM. Reason: I don't know how to spell
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    04-17-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2003
    Posts
    43

    Re: Creating formulas to count cells that fall into specific ranges

    Thanks Tony, that works. I am slightly bummed that I did not think of that myself though!
    I appreciate it.

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Creating formulas to count cells that fall into specific ranges

    You'll think of it the next time!

    Thanks for the feedback!

+ 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