Count cells equal to

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.