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
how would this formula change if you need to compare more than two things?
Hi alizok,
Personally, I wouldn't use that approach.Originally Posted by alizok
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.
Last edited by Alan; 10-11-2005 at 06:43 AM.
To help us help you, try to do the following:
1) Be precise about what you want to do, and provide a sample of your data / inputs - exactly as they are.
2) State the formula(e) / code that you have tried. People are happy to help , but if you haven't even given it a go, you are less likely to get help, or the help you get will be very basic.
3) State the results you are getting from your formula(e) / code already.
4) State the outputs that you *want* to be getting.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks