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

1. 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. Re: Excel Function- Between two values (Excel 2000)

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

etc.

3. 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

4. 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

Sheet attached. Hope it helps.

Regards
Peter

5. 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. 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. 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

8. 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>

9. 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")

10. 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
•