Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to get last column number in range in Excel
  • Two-column Lookup in Excel
  • Count rows with at least n matching values
  • Multi-criteria lookup and transpose in Excel
  • Approximate match with multiple criteria in Excel

Data Analysis Examples

  • How to create Gauge Chart in Excel
  • Conflicting Multiple Conditional Formatting Rules in Excel
  • Understanding Pivot Tables in Excel
  • Conditional Formatting Color Scales Examples in Excel
  • Excel Pie Chart

Data Validation Examples

  • How To Create Drop-down List in Excel
  • Excel Data validation must contain specific text
  • Excel Data validation require unique number
  • Excel Data validation with conditional list
  • Prevent invalid data entering in specific cells

Check if multiple cells have same value in Excel

by

To confirm that a range of cells all have the same value, you can use a formula based on the COUNTIF function.

Formula

=COUNTIF(range,"<>value")=0

Explanation

In the example shown, the formula in C9 is:

=COUNTIF(C5:C8,"<>ok")=0

Note: this formula is not case-sensitive, you can find a case-sensitive formula here.

Worked Example:   Check if multiple cells have same value with case sensitive in Excel

How this formula works

This formula relies on the standard behavior of the COUNTIF function. The range is C5:C8, the criteria is provided as not equals OK:

=COUNTIF(C5:C8,"<>ok")

The COUNTIF then returns a count of any cells that do not contain “OK” which is compared to zero. If the count is zero, the formula returns TRUE. If the count is anything but zero, the formula returns FALSE.

Worked Example:   Excel Data validation require unique number

Ignore empty cells

To ignore empty cells, you can use a more generic version of the formula:

=COUNTIF(range,value)=COUNTA(range)

This formula generates a count of all matching values, and compares that count to a count of all non-empty cells.

Worked Example:   How to create dynamic named range with INDEX in Excel

Post navigation

Previous Post:

How to count total number of cells in a rectangular range in Excel

Next Post:

Get column name from index in Excel Table

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
  • Clean and reformat telephone numbers using SUBSTITUTE function in Excel
  • Remove text by matching in a cell in Excel
  • Extract last name from full name — Manipulating NAMES in Excel
  • RIGHT, RIGHTB functions: Description, Usage, Syntax, Examples and Explanation
  • How to count total words in a range in Excel
  • Count birthdays by month in Excel
  • WORKDAY function: Description, Usage, Syntax, Examples and Explanation
  • Get days between dates in Excel
  • Get last weekday in month in Excel
  • How to calculate next scheduled event in Excel
  • How to calculate Net Present Value (NPV) in Excel
  • COUPDAYSNC function: Description, Usage, Syntax, Examples and Explanation
  • TBILLYIELD function: Description, Usage, Syntax, Examples and Explanation
  • Calculate original loan amount in Excel
  • ODDFYIELD function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles