Exceljet

Quick, clean, and to the point

Count cells equal to

Excel formula: Count cells equal to
Generic formula 
=COUNTIF(range,value)
Summary 

To count the number of cells equal to a specific value, you can use the COUNTIF function. In the example shown, H6 contains this formula:

=COUNTIF(D5:D16,"red")

The result is 4, since there are four cells in the range D5:D16 that contain "red".

Explanation 

In this example, the goal is to count cells equal to a specific value. In this case, we want to count cells that contain "red" in the range D5:D16. This problem can be solved with the COUNTIF function and the SUMPRODUCT function, as explained below.

COUNTIF function

One way to solve this problem is with the COUNTIF function, which is designed to count cells in a range that meet one specific condition. COUNTIF takes two arguments: range and criteria:

=COUNTIF(range,criteria)

For this problem, range is D5:D16 and criteria is "red". We place double quotes ("red") because "red" is a text value. The formula in cell H6 is:

=COUNTIF(D5:D16,"red") // returns 4

COUNTIF returns the count of values in D5:D16 that are equal to "red", which is 4.

Note: when text values are supplied directly as criteria in COUNTIF, they need to be enclosed double quotes (""). If you have a criteria in another cell, supply the cell address as criteria without quotes, as seen in this example.

With SUMPRODUCT

Another way to solve this problem is with the SUMPRODUCT function and Boolean logic. To count cells in the range D5:D16 that are equal to "red", you can use a formula like this:

=SUMPRODUCT(--(D5:D16="red")) // returns 4

Because the range D5:D16 contains 12 values, the logical expression D5:D16="red" returns an array with 12 TRUE/FALSE results like this:

{TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE}

Each TRUE corresponds to a cell in D5:D16 that contains "red". The double negative (--) is used to convert the TRUE and FALSE values to 1s and 0s:

=SUMPRODUCT({1;1;0;0;0;0;1;0;0;1;0;0})

SUMPRODUCT then sums the items in the array and returns 4 as a final result. Although in this example the COUNTIF function is an excellent solution, SUMPRODUCT provides a lot more flexibility in more complex problems.

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.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.