Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Using COUNTIF with multiple criteria

    I am needing to use COUNTIF to count ages. I have a spreadsheet that calculates ages, but now I need to count how many people are between 0-10, between 10-20, and so on and so forth. I have figured it out this far =COUNTIF(A1:a122,"<10"), but that is only for less than ten. What kind of formula do I use to figure out >10<20?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    =COUNTIF(A1:a122,"<20") -COUNTIF(A1:a122,"<10")

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I don't know what I'm doing wrong, but that isn't working. Being as there are only 122 records, I just counted them for the job I needed, but I'd still like to figure it out. I have the spreadsheet attached. That formula was giving me weird numbers. Between 10 and 20 there are actually 24 people, but that formula gives me 20.
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The formula you have is
    =COUNTIF(A1:A122,">9")-COUNTIF(A1:A122,"<20")

    is not the one I gave you and is not what you asked about. The one I gave:
    =COUNTIF(A1:A122,"<20")-COUNTIF(A1:A122,"<10")

    Gives 24...

    [The first countif counts everything <20 (including all those <10). The 2nd subtracts out the ones <10. ]

    Steve

  5. #5
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    I think first of all the bracket ranges need to changed. For example 10-20 and 20-30 would double count the 20's. 20 would be counted in 10-20 and 20 would be counted in 20-30.

    The attached uses the COUNTIFS function. That function is not available in Excel 2003.

    Look at Rows 4 and 5.

    HTH
    Attached Files Attached Files

  6. #6
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Row 6 of this version uses COUNTIF and can be used with earlier versions of Excel.
    Attached Files Attached Files
    Last edited by tfspry; 2011-04-16 at 14:06. Reason: Added an .xls file version

  7. #7
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts
    OK. Thanks for the explanation. It didn't make sense to me so I changed it around, now it all works.

Posting Permissions

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