Count cells equal to either x or y in Excel

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.

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.

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