Want to learn how to check if values in a range are above a certain threshold? This post will give you an overview of how to do so using the COUNTIF function in Excel.
How to Check If All Values in a Range Are at Least a Certain Criteria in Excel
Formula Using COUNTIF
=COUNTIF(Where do you want to look?, What do you want to look for?)
To check if values in a range are above a certain threshold using the COUNTIF function, follow the procedure below:
- Click on Formulas on the menu bar in Excel.
- Next, click on More Functions and point the cursor on Statistical.
- Scroll down the list that displays and click on COUNTIF.
Example of formula
The dialogue box will display as shown below.
Figure 1: How to access the COUNTIF formula in Excel
- The range is the span of cells to which you want to apply the formula. In the example below, it is A4 to E4. Type A4:E4.
- The criteria are the value you want to use to determine the entries to be counted. In the example below, the criteria are 50. Type “>50”
- Click on OK.
It will show you the result displayed in the screenshot below:
Figure 2: Result displayed by COUNTIF
Interpretation of the Result
What is displayed above shows that in the row extending from A4 to E4 in the spreadsheet above, using the COUNTIF function to count values that are at least 50, that is, =COUNTIF(A4E4,“>50”), only 3 entries are at least 50. It is easy to look at the table and verify by physically counting that the formula is correct. The only values in that row that are 50 and above are 57, 53 and 60.
Alternatively, simply type the formula above into the cell on the right of the row you are interested in. Hit the Enter key when you finish typing, and the result will show.
NOT can be used in conjunction with the COUNTIF in order to assess whether all the entries in a spreadsheet have at least the chosen minimum value. Consider the example displayed on the screenshot below in which the formula in F4 is: =NOT(COUNTIF(A4:E4, “>50”))
Figure 3: All the entries in the range have at least the value of 50.
In this case, the formula for F4 indicates that all the entries in the range have at least the value of 50 and above. Since this is not true for the row in question, COUNTIF gives false.
This is the way this formula does it:
What COUNTIF formula does essentially is to count all the cells that have values which are at least up to a certain value, which in the formula above, is fixed as 50. COUNTIF(A4:E4,">50")
Here, COUNTIF will give the number of cells that contain an entry that is at least 50. If there is no such cell, it will give zero.
The NOT command is used for displaying the COUNTIF results in True or False format.
- Where any of the entries fall above 50, COUNTIF will state how many there are, while NOT will give False if some of the entries that fall below 50.
- In the same manner, where there are no values that fall above 50, COUNTIF will give zero, while NOT will give True, meaning that all the values are at least 50.
Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.
Leave a Comment