Counting numbers that are in between two values

[ExcelTip]

Problem:

Finding the number of values in List1 (Column A) that are larger than 20 and smaller than 50.

Solution:

Using the following COUNTIF formula:
=COUNTIF(A2:A7,\"\">20\"\")-COUNTIF(A2:A7,\"\">=50\"\")
Or this SUMPRODUCT formula:
=SUMPRODUCT((A2:A7>20)*(A2:A7

List1
30
10
60
40
15
55

Result 2

[alizok]

how would this formula change if you need to compare more than two things?

[Alan]

Hi alizok,

how would this formula change if you need to compare more than two things?

Personally, I wouldn't use that approach.

I would suggest using a sum array formula to make it easier to understand and make it more scaleable across multiple criteria.

See this post for an example (2 criteria but obvious how to extend):

http://www.excelforum.com/showthread...=1#post1360137

HTH,

Alan.