CODEWITHSUNDEEP
excelrangeformula
czioutas
czioutas

Reputation: 1068

How do I count cells that are between two numbers in Excel?

I need a formula to count the number of cells in a range that are between 10 and 10.000:

I have: 

=COUNTIF(B2:B292,>10 AND <10.000)

But how do I put the comparison operators in without getting a formula error?

Upvotes: 15

Views: 121433

Answers (3)

Prasenjit Nandi
Prasenjit Nandi

Reputation: 1

=COUNTIFS(H5:H21000,">=100", H5:H21000,"<999")

Upvotes: 0

user3713594
user3713594

Reputation: 11

Example-

For cells containing the values between 21-31, the formula is:

=COUNTIF(M$7:M$83,">21")-COUNTIF(M$7:M$83,">31")

Upvotes: 1

barry houdini
barry houdini

Reputation: 46361

If you have Excel 2007 or later use COUNTIFS with an "S" on the end, i.e.

=COUNTIFS(B2:B292,">10",B2:B292,"<10000")

You may need to change commas , to semi-colons ;

In earlier versions of excel use SUMPRODUCT like this

=SUMPRODUCT((B2:B292>10)*(B2:B292<10000))

Note: if you want to include exactly 10 change > to >= - similarly with 10000, change < to <=

Upvotes: 20

Related Questions