Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Function- Between two values (Excel 2000)

    I have a spreadsheet with salaries ranging from 0 through 50,000. What I need is a count of how many salaries are between 10000 through 30000, or between 0 to 10000. Anyone know how I can do this? I didn't find a "Between" function so I think I may need to do a nested if function. But, I need some guidance.

    Thanks a bunch! <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excel Function- Between two values (Excel 2000)

    =COUNTIF(range,">10000")-COUNTIF(range,">30000")

    etc.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Function- Between two values (Excel 2000)

    Agggh, John beat me by 2 seconds! Anyway, =COUNTIF(A:A,"<10000") counts the number of entries in column A that are less than $10,000. You can combine conditions if you use an array formula, but it is easier to just do some math as John did. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Function- Between two values (Excel 2000)

    This is my particular sledhehammer to crack your particular nut...

    =COUNT(A1:A10)-(COUNTIF(A1:A10,">"&C3)+COUNTIF(A1:A10,"<"&C2))

    Where:

    A1:A10 is your list of salaries
    C3 is your maximum value
    C2 is your minimum value

    Sheet attached. Hope it helps.

    Regards
    Peter
    Attached Files Attached Files

  5. #5
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Function- Between two values (Excel 2000)

    The function doesn't seem to give me the correct answer. According to your spreadsheet, if you are counting between 45,000 and 100,000, then the answer should be 5 and not 2.
    =COUNT(A1:A10)-(COUNTIF(A1:A10,">"&C3)+COUNTIF(A1:A10,"<"&C2))

    Can't seem to make it work, any more help would be appreciated.

  6. #6
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Function- Between two values (Excel 2000)

    Hello John,

    This doesn't seem to give me what I am looking for. What I want is a count of how many salaries are between 10,000 through 30,000. And when I changed the operators like this: =COUNTIF(A:A,">10000")-COUNTIF(A:A,"<30000"), I get a negative 2 as the answer, instead of 5.

  7. #7
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Function- Between two values (Excel 2000)

    How do I ombine conditions. I can easily count how many less than 10,000 but how about how many between 10,000 and 30,000

    Thanks in advance.

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Function- Between two values (Excel 2000)

    Count how many there are and subtract off the ones you don't want:
    <pre>=COUNT(A:A)-COUNTIF(A:A,">30000")-COUNTIF(A:A,"<10000")</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excel Function- Between two values (Excel 2000)

    [Edited, boy did I mess up]

    I messed up the operators, they should have been:

    =COUNTIF(A:A,"<30000.01")-COUNTIF(A:A,"<10000")

    or

    =COUNTIF(A:A,">9999.99")-COUNTIF(A:A,">30000")
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Function- Between two values (Excel 2000)

    Thank you for your help. I got it to work with your formula.

    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

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