I am 4 days into Excel, with a problem: I have 880,000 856,000 836,000 as numbers ranging between. _ Author Debra Dalgleish Posted on JSeptemCategories Excel Formulas Using the Excel operators, you want quantities that are >=5 AND=5 and the second to test for quantity =” & E4,$B$2:$B$10,”=” & E4,$B$2:$B$10,”=” & E4,$B$2:$B$10,”link to the page In this example you’re counting the orders with a quantity between 5 and 10. The result will be a count of the orders that meet all the criteria. Instead of using two COUNTIF functions, and subtracting one result from the other, you’ll list all the ranges and criteria within one COUNTIFS formula.

In Excel 2007, and later versions, you can use the COUNTIFS function, and the formula is a bit simpler. The combined formulas show a result of 6 - the number of orders with a quantity between 5 and 10. Those shouldn’t be included in the final count, because they’re over the maximum. The first formula shows that there are 9 orders with a quantity of 5 or higher.įrom that amount, you’ll subtract the number of orders with a quantity greater than 10 (3 orders). Then, you can combine those two COUNTIF formulas, to find the answer. With two separate COUNTIF formulas, you can calculate the number of orders where the quantity is 5 or greater, and the number of orders where the quantity is greater than 10. Unfortunately, Excel doesn’t have a Between function, so you’ll have to find another solution. The challenge is to count the orders where the quantity is between 5 and 10. In the worksheet shown below, there’s a list of items ordered, and the quantity of each item. In earlier versions, COUNTIFS is not available, and you can use COUNTIF. How many students got a grade between 50 and 70? How many golfers scored between 70 and 80? How many orders were for a quantity between 5 and 10? In Excel 2007 and higher, use the COUNTIF and COUNTIFS functions to calculate the answers to those questions.

How to find a range in excel 2010