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 row in text data in Excel
  • How to get address of last cell in range in Excel
  • How to get last column number in range in Excel
  • How to calculate two-way lookup VLOOKUP in Excel Table
  • How to get address of first cell in range in Excel

Data Analysis Examples

  • Conditional Formatting Icon Sets Examples in Excel
  • Number and Text Filters Examples in Excel
  • Conditional Formatting Color Scales Examples in Excel
  • Conditional Formatting Data bars Examples in Excel
  • How To Insert and Customize Sparklines in Excel

Data Validation Examples

  • Excel Data validation specific characters only
  • Data validation must not exist in list
  • Excel Data validation no punctuation
  • Excel Data validation unique values only
  • Excel Data validation date in next 30 days

Check 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.

See illustration below:

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:   COUNTIF function: Description, Usage, Syntax, Examples and Explanation

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:   Count cells that end with in Excel

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:   Break ties with helper column and COUNTIF in Excel

Post navigation

Previous Post:

Check multiple cells are equal in Excel

Next Post:

To count total rows in a range in Excel

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
  • How to pad text to match equal length in Excel
  • How to count total words in a cell in Excel
  • Remove last characters from right in a cell in Excel
  • How to create email address with name and domain in Excel
  • CODE function: Description, Usage, Syntax, Examples and Explanation
  • Calculate retirement date in Excel
  • Get days, months, and years between dates in Excel
  • Convert Excel time to decimal seconds
  • How to calculate nth day of week in month in Excel
  • Get fiscal year from date in Excel
  • RECEIVED function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate annuity for interest rate in excel
  • YIELDMAT function: Description, Usage, Syntax, Examples and Explanation
  • CUMPRINC function: Description, Usage, Syntax, Examples and Explanation
  • NPV function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles