The result is 4, since there are four cells in the range D5:D16 that contain “red”.
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: 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 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: Because the range D5:D16 contains 12 values, the logical expression D5:D16=“red” returns an array with 12 TRUE/FALSE results like this: 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 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
