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, G6 contains this formula:

=COUNTIF(D5:D11,"red")

Explanation

The COUNTIF function is fully automatic — it counts the number of cells in a range that match the supplied criteria.

For range, we use D5:D11, which contains colors. For criteria, we simply use "red".

COUNTIF returns the count of values in D5:D11 that are equal to "red".

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

Author

Dave Bruns

Related formulas

Count cells that do not contain

COUNTIF counts the number of cells in the range that do not contain "a" by matching the content of each cell against the pattern " *a*", which is supplied as the criteria. The "*" symbol (the asterisk) is a wildcard in Excel that means "match any...

Count cells that contain text

COUNTIF counts the number of cells that match the supplied criteria. In this case, the criteria is supplied as the wildcard character "*" which matches any number of text characters. A few notes: The logical values TRUE and FALSE are not counted as...

Cell contains specific text

The SEARCH function returns the position of the search string when found, and the #VALUE! error if not found. We use this fact to test whether the search string is found by using the ISNUMBER function to "catch" valid numeric positions. ISNUMBER...

Running count of occurrence in list

Working from the inside out, the COUNTIF function is set up to count the value "blue" that appears in column B: COUNTIF ( $B$5:B5 , "blue" ) Note the left side of the range reference is locked ($B$5) and the right side is relative (B5)...

Summary count with COUNTIF

The COUNTIF function takes two arguments: a range of cells to count, and the criteria to use for counting. In this case we want to count by color, so we have set up a small table that lists all colors in column F. This allows us to use the t he...

Count cells that contain either x or y

When you count cells with "OR logic", you need to be careful not to double count. For example, if you are counting cells that contain "abc" or "def", you can't just add together two COUNTIF functions, because you may double count cells that contain...

Related functions

Excel COUNTIF Function

COUNTIF is an Excel function to count cells in a range that meet a single condition. COUNTIF can be used to count cells that contain dates, numbers, and text. The criteria used in COUNTIF supports logical...

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.