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

excel range formula
czioutas picture czioutas · May 2, 2013 · Viewed 116.1k times · Source

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?

Answer

barry houdini picture barry houdini · May 2, 2013

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 <=

Related questions

How to use a named column in Excel formulas

I know how to make a named range in Excel. I have a spreadsheet, with various columns going across as parameters, and then finally a formula in the last cell. This is repeated many times in each row, with each …

Read More
Creating an Array from a Range in VBA

I'm having a seemingly basic problem but can't find any resources addressing it. Simply put, I just want to load the contents of a Range of cells (all one column) into an Array. I am able to accomplish this by …

Read More
Detect if range is empty

I want to check if a range in Excel is empty. How do I write in VBA code: If Range("A38":"P38") is empty

Read More