» Counting the Number of Values Between Upper and Lower Limits
CATEGORY - Excel Formula and Function
VERSION - All Microsoft Excel Versions
Finding the number of values in List1 (Column A) that are larger than 20 and smaller than 50.
Solution:
Use the COUNTIF function as shown in the following formula:
=COUNTIF(A2:A7,"">20"")-COUNTIF(A2:A7,"">=50"")
Or use the SUMPRODUCT function as shown in the following formula:
=SUMPRODUCT((A2:A7>20)*(A2:A7<50))
List1
30
10
60
40
15
55
Result 2
Book Store:
alizok
how would this formula change if you need to compare more than two things?
Reply: alizok
Alan
Hi alizok,
[QUOTE=alizok]how would this formula change if you need to compare more than two things?[/QUOTE]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):
[url]http://www.excelforum.com/showthread.php?p=1360137&posted=1#post1360137[/url]
HTH,
Alan.
