Exceljet

Quick, clean, and to the point

Count cells equal to either x or y

Excel formula: Count cells equal to either x or y
Generic formula 
=COUNTIF(rng,value1) + COUNTIF(rng,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.

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(rng,0) + COUNTIF(rng,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.

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
Author 
Dave Bruns

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.