Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Filter Data Based on Date in Excel
  • How to calculate current stock or inventory in Excel
  • How to Create Thermometer Chart in Excel
  • Chart Axes in Excel
  • How to Create Column Chart in Excel

References

  • How to use Excel MMULT Function
  • How to get address of named range in Excel
  • How to get last row in mixed data with blanks in Excel
  • Extract data with helper column in Excel
  • How to calculate two-way lookup VLOOKUP in Excel Table

Data Validations

  • Excel Data validation don’t exceed total
  • Data validation must not exist in list
  • Excel Data validation no punctuation
  • Excel Data validation with conditional list
  • Excel Data validation exists in list

How to test for all values in a range are at least in Excel

by

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.

Formula

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

Explanation

In the example shown, the formula in G5 is:

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

How this formula works

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.

Worked Example:   Count long numbers without COUNTIF in Excel

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
Worked Example:   Check multiple cells are equal in Excel

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)

Post navigation

Previous Post:

How to generate random date between two dates in Excel

Next Post:

Popularly Used Excel Functions and their examples

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • How to use Excel TRUE Function
  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • Check multiple cells are equal in Excel
  • OR function: Description, Usage, Syntax, Examples and Explanation
  • IFNA function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Display the current date in Excel
  • EOMONTH function: Description, Usage, Syntax, Examples and Explanation
  • How to show last updated date stamp in Excel
  • How to calculate quarter from date in Excel
  • DAY function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Group times into unequal buckets in Excel
  • Map text to numbers in Excel
  • Categorize text with keywords in Excel
  • Group numbers with VLOOKUP in Excel
  • Calculate conditional mode with criteria in Excel

General

  • With vs Without Array Formula in Excel
  • Basic text sort formula in Excel
  • Subtotal by color in Excel
  • Delete Blank Rows at Once in Excel
  • Count cells that contain errors in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning