» 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:
Recommended Books:
- AWAKEN THE GIANT WITHIN : HOW TO TAKE IMMEDIATE CONTROL OF YOUR MENTAL, EMOTIONAL, PHYSICAL AND FINANCIAL
- Microsoft Excel Version 2002 Step by Step
- The Intelligent Investor: The Definitive Book On Value Investing, Revised Edition
- The Total Money Makeover. : A Proven Plan for Financial Fitness
- Microsoft Outlook 2002 for Dummies
- The 22 Immutable Laws of Marketing : Exposed and Explained by the World's Two
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.
