Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel: Counting values between two numbers 1

Status
Not open for further replies.

Gooter

Technical User
Joined
Apr 5, 2002
Messages
20
Location
US
I have a cell range with numerical values. If I want to count how many numbers fall between a range, how would I do that? I'm trying to use the COUNTIF function and I cannot get it to work when I use more than one variable.

Example:

This works if I only want to count the values greater than 50

=COUNTIF(A6:A100,">50")

but I don't know how to modify the formula if I want to count how many values fall between 50 and 75.

=COUNTIF(A6:A100, &quot;>=50<=75&quot;) does not work.

Thanks in advance.
 
Hrmm, I couldn't get that to work. Probably not doing it correctly. I'd really like to use a formula for this though. Any more suggestions?
 
The subtotal function counted the number of cells which contained values in my range. I couldn't figure out how to use the filter criteria to count how many values were between two numbers, eg. 50 - 75.

Example

Cells:
A1~~51
A2~~58
A3~~40
A4~~57
A5~~50

The result of =subtotal(2,A1:A5) was 5. I then went to Data -> Filter -> Advanced Filter and couldn't figure out how to set my criteria.

 
Choose Custom in the filter and then select the appropriate operator and fill in or select the values

Greater than or equal to 5
Less than or equal to 50

:-)

Skip,
Skip@TheOfficeExperts.com
 
Correct me if I'm wrong, but the way I am reading this Gooter doesn't want to filter he just wants a count of the number of occurances between two numbers. Here is one way, not very fancy but it seems to work.

=(COUNTIF(Range,&quot;>MinValue&quot;)+COUNTIF(Range,&quot;<MaxValue&quot;))-COUNT(Range)

Let me know if this works for you.

Regards

weaselthend
 
Weaselthend,

Yep, that's what I was trying to do. Thanks! And thanks to SkipVoight as well!
 
My simplistic answer will also work...

=SUM(COUNTIF(A1:A12,&quot;<75&quot;)-(COUNTIF(A1:A12,&quot;<50&quot;)))
 
=COUNTIF(A6:A100,&quot;>=50&quot;)-COUNTIF(A6:A100,&quot;>75&quot;)

Regards
Ken...............



----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top