# Count cells equal to either x or y

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 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 B5:B10 equal to "apples", 2. The second COUNTIF returns the count of cells in B5:B10 equal to "pears", 1. The formula simply adds both results together and returns a final result of 3.

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

### 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:

If you need to count cells that ** contain** either X or Y (instead of

*equal*to X or Y) see the formula example here.

### Alternative syntax

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

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

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:

## Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

## Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.