Excel formula: All values in a range are at least

Generic formula

=NOT(COUNTIF(range,"<65"))

Summary

To test if all values in a range are at least a certain threshold value, you can use the COUNTIF function together with the NOT function. In the example shown, the formula in G5 is:

=NOT(COUNTIF(B5:F5,"<65"))

Explanation

At the core, this formula uses the COUNTIF function to count any cells that fall below a given value, which is hardcoded as 65 in the formula:

COUNTIF(B5:F5,"<65")

In this part of the formula, COUNTIF will return a positive number if any cell in the range is less than 65, and zero if not. In the range B5:F5, there is one score below 65 so COUNTIF will return 1.

The NOT function is used to convert the number of from COUNTIF into a TRUE or FALSE result. The trick is that NOT also "flips" the result at the same time:

If any values are less than 65, COUNTIF returns a positive number and NOT returns FALSE
f no values are less than 65, COUNTIF returns a zero and NOT returns TRUE

This is the equivalent of wrapping COUNTIF inside IF and providing a "reversed" TRUE and FALSE result like this:

=IF(COUNTIF(B5:F5,"<65"),FALSE,TRUE)

Author

Dave Bruns

Related formulas

All cells in range are blank

Working from the inside out, this formula contains an expression inside SUMPRODUCT that tests each cell in a range like so: -- ( B5:D5 <> "" ) Inside the parentheses, the result of B5:D5 "" looks like this: { TRUE , FALSE , TRUE }...

Related functions

Excel COUNTIF Function

COUNTIF is an Excel function to count cells in a range that meet a single condition. COUNTIF can be used to count cells that contain dates, numbers, and text. The criteria used in COUNTIF supports logical...

Excel NOT Function

The Excel NOT function returns the opposite of a given logical or boolean value. When given TRUE, NOT returns FALSE.When given FALSE, NOT returns TRUE. Use the NOT function to reverse a logical value.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.