Excel formula: Count cells equal to

Generic formula

=COUNTIF(range,value)

Explanation

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")

How this formula works

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

To count the number of cells that do not contain certain text, you can use the COUNTIF function. In the generic form of the formula (above), rng is a range of cells, txt represents the text that cells should not contain, and "*" is a wildcard...

Count cells that contain text

To count the number of cells that contain text (i.e. not numbers, not errors, not blank), use the COUNTIF function and a wildcard . In the generic form of the formula (above), rng is a range of cells, and "*" is a wildcard matching any number of...

Cell contains specific text

To check if a cell contains specific text, you can use the SEARCH function together with the ISNUMBER function. In the generic version, substring is the specific text you are looking for, and text represents text in the cell you are testing. In the...

Running count of occurrence in list

To create a running count of certain values that appear in range of cells, you can use the COUNTIF with a " mixed reference " to create a running total. In the example shown, the formula in C5 is: = IF ( B5 = "blue" , COUNTIF ( $B$5:B5 ,...

Summary count with COUNTIF

When working with data, a common need is to perform summary calculations that show total counts in different ways. For example, total counts by category, color, size, status, etc. The COUNTIF function is a good way to generate these kinds of totals...

Count cells that contain either x or y

To count cells that contain either one value or another, you an either use a helper column then tally up the count, or a more complex single cell formula. Background When you count cells with "OR" criteria, you need to be careful not to double count...

Related functions

Excel COUNTIF Function

COUNTIF is a function to count cells that meet a single criterion. COUNTIF can be used to count cells with dates, numbers, and text that meet specific criteria. The COUNTIF function supports logical operators (>,...

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.