If you want to count cells in a range that are greater than a given value then you can do that easily with help of Excel built-in Excel COUNTIF function. Understanding how to use the COUNTIF function to count cells greater than a given value is essential. Reading this post will take you through a step-by-step procedure on how to easily do this.
Figure 1: Count Cells greater than a certain value
The formula for counting the number of cells that are greater than a given value is as below:
- rng denotes the range of value where you want to count cells.
- X shows the threshold, i.e. the value that everything else should be greater than.
There are times when we need to count the number of values that are greater than a given specified value. This can easily be done in Excel using the “count cells greater than” procedure. This procedure largely utilizes the COUNTIF Excel function in its operation.
The COUNTIF function in this procedure will be used to count the cells whose values are greater than the set threshold. Any cell whose value is not above the threshold will be ignored, and won’t be counted.
Using the data in figure 1 above, we can set the threshold as 50, and thus have our formula modified as below:
The figure below shows how our example will look like:
Figure 2: Count cells greater than 50
The same procedure is used to count cells that are equal to or greater than the specified threshold.
The syntax, in this case, will be:
This will count all the cells that have value 50 and above. A quick run of the formula will reveal that we have 6 cells that contain the value 50 and above.
Figure 3. Result
When do we use the “Count cells greater than procedure?”
While one might know how to use this procedure, knowing when it is required is also essential. There are numerous instances when one can use the COUNTIF function when counting cells whose values are greater than a given threshold. Top among them are student marks in a classroom, amount in invoices for business people and so forth.
Benefits of “Count cells greater than” procedure
As it might be obvious now, the COUNTIF Excel function plays a vital role in helping one count cells greater than a set value. And there are many benefits of this function as seen below:
Easy to use
Perhaps this is the easiest procedure that anyone can ever carry out without any struggle. For as long as you have a simple knowledge of the COUNTIF function, you will easily count the number of cells whose values are greater than the set threshold.
Think of a situation where there are hundreds of invoices and one would want to know the number that is above the “Break-even” point for a business. Counting each one of them might be hard, tedious and time-consuming. But with the use of the “Count Cells greater than” procedure, one can easily know those invoices that are above the break-even point. This will help in decision-making by the executive.