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

Lookup and Reference Examples

  • Count rows with at least n matching values
  • How to get last row in numeric data in Excel
  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • MATCH function: Description, Usage, Syntax, Examples and Explanation
  • Approximate match with multiple criteria in Excel

Data Analysis Examples

  • How To Load Analysis ToolPak in Excel
  • How To Sort One Column or Multiple Columns in Excel
  • Conditional Formatting New Rule with Formulas in Excel
  • Subtotal function in Excel
  • Understanding Anova in Excel

Data Validation Examples

  • Excel Data validation must begin with
  • Excel Data validation date in specific year
  • Excel Data validation no punctuation
  • How To Create Drop-down List in Excel
  • Excel Data validation require unique number

Count cells equal to either x or y in Excel

by

This tutorial shows how to Count cells equal to either x or y in Excel using the example below;

Formula

=COUNTIF(range,value1) + COUNTIF(range,value2)

Explanation

To count the number of cells equal to either one value OR another, you use formula that uses the COUNTIF function twice. In the example shown, cell E5 contains this formula:

=COUNTIF(B4:B9,"apples")+COUNTIF(B4:B9,"pears")

How this formula works

COUNTIF counts the number of cells in a range that match supplied criteria. In this case, the criteria for the first COUNTIF is “apples” and the criteria for the second COUNTIF is “pears”. The first COUNTIF returns the count of cells in B4:B9 equal to “apples”, 2. The second COUNTIF returns the count of cells in B4:B9 equal to “pears”, 1. The formula simply adds both results together and returns a final result of 3.

Worked Example:   Excel Data validation must begin with
Worked Example:   Excel Data validation unique values only

COUNTIF is not not case sensitive. The following formulas will return the same result:

=COUNTIF(range,"apples")
=COUNTIF(range,"APPLES")

Text versus numbers

Text values  in COUNTIF criteria need to be enclosed in quotes (“”), but numbers do not. To count cells in a range equal to zero or 1, you can use:

=COUNTIF(range,0) + COUNTIF(range,1)

If you need to count cells that contain either X or Y (instead of equal to X or Y) see the formula example here.

Worked Example:   Example of Count with Or Criteria in Excel

Alternative syntax

You can also use an array constant to count cells equal to either x or y, which looks like this:

=SUM(COUNTIF(range,{"x","y"}))

Adapted to the example shown, you can count cells equal to “apples” or “pears” with:

=SUM(COUNTIF(B5:B10,{"apples","pears"}))

When you provide multiple values to COUNTIF like this, it will return an array containing multiple results. The SUM function is used return a final SUM:

=SUM(COUNTIF(B5:B10,{"apples","pears"}))
=SUM({2,1})
=3

Post navigation

Previous Post:

Count cells less than in Excel

Next Post:

Count cells between dates 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
  • MID, MIDB functions: Description, Usage, Syntax, Examples and Explanation
  • T function: Description, Usage, Syntax, Examples and Explanation
  • How to count specific words in a range in Excel
  • PHONETIC function: Description, Usage, Syntax, Examples and Explanation
  • How to Separate Text Strings in Excel
  • Convert Excel time to Unix time in Excel
  • Extract time from a date and time in Excel
  • Get day from date in Excel
  • Convert decimal minutes to Excel time
  • Get month name from date in Excel
  • DURATION function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate annuity for interest rate in excel
  • How to calculate compound interest in Excel
  • CUMIPMT function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate Net Present Value (NPV) in Excel
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles