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

Data Analysis

  • How To Create Pareto Chart in Excel
  • How to calculate correlation coefficient Correlation in Excel
  • Move chart to a separate worksheet in Excel
  • How to create Checklist in Excel
  • Understanding Pivot Tables in Excel

References

  • Last row number in range
  • Left Lookup in Excel
  • Count rows that contain specific values in Excel
  • How to use Excel TRANSPOSE Function
  • To count total rows in a range in Excel

Data Validations

  • How To Create Drop-down List in Excel
  • Excel Data validation date in next 30 days
  • Excel Data validation exists in list
  • Excel Data validation must not contain
  • Excel Data validation no punctuation

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:   Count cells that are blank in Excel

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:   Basic text sort formula 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:

44 Practical Excel IF function Examples

Next Post:

Large with criteria 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

Logical Functions

  • IFNA function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel FALSE Function
  • AND function: Description, Usage, Syntax, Examples and Explanation
  • NOT function: Description, Usage, Syntax, Examples and Explanation
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Convert decimal hours to Excel time
  • Calculate series of dates by workdays in Excel
  • Display Date is same month in Excel
  • How to calculate next scheduled event in Excel
  • How to calculate working days left in month in Excel

Grouping

  • Group times into 3 hour buckets in Excel
  • Map inputs to arbitrary values in Excel
  • Map text to numbers in Excel
  • Categorize text with keywords in Excel
  • How to randomly assign data to groups in Excel

General

  • How to choose page/paper size in Excel before Printing
  • How to test a range for numbers in Excel
  • 231 Keyboard Shortcut Keys In Excel
  • How to count total columns in range in Excel
  • How to generate random number weighted probability in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning