There has to be someone who it comes down to – to get the clerical work out of the way, fill the administrative shoes, fluff up the white collar. But sitting on the other side of an Excel screen, there’s no need for admin when Excel can be your minion. We’d say that we are counting today but that’s far from the truth; Excel is counting for us the number of cells that carry values between two given numbers.
That promise makes us happy because no one wants to manually count or slave away on any needlessly extensive work on Excel. We’re here to make certain that your Excel life is easy and this is one life you want shortened too. We’re using the COUNTIFS, SUMPRODUCT, COUNT, and FILTER functions in our attempt to count cells whose values match a number range.
We’ll require an example to show you how easy and harmonious this can be and the example is here at the ready.
Example
See this sample extract we have of some customer orders. Our interest is in column D which enlists the number of items in each order. For now, we are curious about the number of customers who placed an order of between 5 to 10 items.
No one will stop you if you apply a filter, sort the data according to descending number of ordered items, select the cells and read the Status Bar for the count. But some Excel fairy will be crying bitter tears and she’s more than happy to do the counting for you so we say…..
Let’s get counting!
Method #1 – Using COUNTIFS Function
How about we start by telling Excel to count the cells if they fall between two certain values? That is what the COUNTIFS function will do for us. The 'S function (see it as a plural of the COUNTIF function) counts the cells that meet the specified conditions.
The two conditions for COUNTIFS will be to find the cells above the lower limit and below the upper limit. The next and final bit will be to count them. Let's apply the following formula to our case example and see how it works:
=COUNTIFS(D3:D17,"<=10",D3:D17,">=5")The two conditions we’re passing to the COUNTIFS function are separated by the second comma in the formula. Firstly, the count performed by COUNTIFS should be of cells that carry a value of less than and equal to 10 in the range D3:D17. Then the second condition is applied to the same range but to count cells that are greater than and equal to 5 in value.
This effectively places the counting criteria as greater than, equal to 5 and less than, equal to 10. The COUNTIFS function counts the cells that fulfill these conditions and returns 9.
This means that there are 9 cells in D3:D17 that have values between 5 to 10. For our case example, that means 9 out of 15 customers placed orders of between 5 to 10 items.
Method #2 – Using SUMPRODUCT Function
Part of understanding the Excel-verse means exploring options and we know another function that can count values between two numbers. This is the SUMPRODUCT function and it multiplies the values in a range with a corresponding range. Then it returns the sum of those values. Used around that concept, SUMPRODUCT makes for a great adding tool to bring together what you need from a range of cells.
Keeping the conditions the same as before, let’s feed it into this formula in a way that will be better accepted by the SUMPRODUCT function:
=SUMPRODUCT((D3:D17>=5)*(D3:D17<=10))The range given to the SUMPRODUCT function is D3:D17 and the first counting basis is >=5. The second range in the formula is also D3:D17 with the basis for counting as <=10. Both parts in the formula are entered as the same argument (as one array) and have an asterisk in between as the multiplication operator.
Row by row, when every cell in column D is checked to be between 5 to 10, TRUE or FALSE will be returned. But since multiplication requires numbers, the asterisk will force the TRUE and FALSE into 1s and 0s. Then these will be summed by SUMPRODUCT and give us the count again as 9:
That was one of the many ways to use the SUMPRODUCT function for counting cells. And you can see that we have gotten the desired result from SUMPRODUCT without any Ctrl + Shift + Enter complications of an array formula.
Method #3 – Using COUNT & FILTER Function
We’ll drag your attention back to the start of this tutorial where we were talking about no one stopping you from going the Filter route. Here’s the Excel-seasoned way to do it and it’s going to involve the FILTER function, topped off with the COUNT function – just to make sure it’s not you doing the counting.
The FILTER function filters a range and is like the function version of the Filter feature; you punch in the filtering particulars and the function will filter the data in the range accordingly. The COUNT function counts cells in a range that contains numbers. The formula ahead is an example of how these two functions can work together to count cells with values between two numbers in Excel:
=COUNT(FILTER(D3:D17,(D3:D17>=5)*(D3:D17<=10)))Beginning with the FILTER function, we’ve supplied the array to be filtered as D3:D17. The logical tests given in the second argument are added with an asterisk (that works like the AND concept) between them and they target column D for values between 5 to 10. Without the COUNT function, the FILTER function will spill its result, returning all the cells matching the given criteria:
=FILTER(D3:D17,{8;7;9;8;9;5;6;8;7})
Then the COUNT function counts how many cells contain numbers and since there are 9 cells returned by the FILTER function, we get the result as expected:
By now, you will have learned 3 easy ways of counting cells with values between two numbers with concordant results. For your task, we hope that you can take one of these methods and easily further it if need be. We’ll hurry back with more Excel suggestions that befit or excel the white collar. Are you ready?
About Mehwish Javaid