Count Excel Formulas

Count cells equal to a specific value in Excel

CA Anshuman Tandon

Sometimes the simplest question in a spreadsheet is: how many times does a particular value appear in a range? Maybe you want to count how many times the color “red” occurs, or how many orders came from a specific state, or how many people responded with “Yes.”

This guide shows two straightforward ways to do that in Excel — using the classic COUNTIF function and an alternative SUMPRODUCT formula based on Boolean logic. Both are useful to know, especially as your needs get more advanced.

What does “count cells equal to” mean

The idea is to count the number of cells within a certain range that exactly match a specific value. For example:

  • Count how many cells equal “red” in a list of colors.
  • Count how many cells equal the number 100 in sales data.
  • Count how many cells equal the name “Jim” in a roster.

Using COUNTIF to count equal values

COUNTIF is Excel’s go-to function for counting cells that meet a single condition, including exact matches.

The generic formula

text=COUNTIF(range, value)
  • range is the group of cells to search (like D5:D16).
  • value is the criterion you want to count, such as "red" or a reference to a cell holding that value.

Example

To count how many cells equal "red" in the range D5:D16, you write:

text=COUNTIF(D5:D16, "red")

This formula returns 4 if there are exactly four cells containing the word “red”.

Note:

  • When the criteria is text typed directly in the formula, it needs to be enclosed in double quotes.
  • If the criteria is in another cell, refer to that cell without quotes, for example: =COUNTIF(D5:D16, H1).

Using SUMPRODUCT with Boolean logic

SUMPRODUCT combined with a logical test can also count exact matches, using a different approach that is sometimes more flexible for complex cases.

The formula looks like this:

text=SUMPRODUCT(--(D5:D16 = "red"))

How this works:

  • The logical test D5:D16 = "red" returns an array of TRUE and FALSE values for each cell in the range.
  • The double negative -- converts those TRUE/FALSE results into 1s and 0s (TRUE becomes 1, FALSE becomes 0).
  • SUMPRODUCT adds up the 1s, effectively counting how many cells contain "red".

For example, the array might be:

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

Which converts to:

text{1;1;0;0;0;0;1;0;0;1;0;0}

And the sum of the array is 4.

When to use COUNTIF vs SUMPRODUCT

COUNTIF is simpler and usually the best choice when you just need to count one condition like equals “red.”

  • SUMPRODUCT is more powerful if you want to combine multiple conditions or need more flexible logical operations, like case-sensitive counts or conditional sums.

For most everyday counting of equal values, COUNTIF works perfectly and is easier to read.

Practical example: count colors in a list

You have a column D listing product colors:

textred
blue
red
green
red
yellow
blue
red

You want to count how many red products there are.

Using COUNTIF:

text=COUNTIF(D5:D12,"red")

It returns 4 since there are exactly four “red” cells.

Using SUMPRODUCT:

text=SUMPRODUCT(--(D5:D12="red"))

It produces the same result.

Tips and best practices

  • Make sure text criteria are quoted when typed directly: "red" not red.
  • Use cell references without quotes if the criteria might change, like =COUNTIF(D5:D16, H1).
  • COUNTIF is not case-sensitive, so “red” and “Red” count the same. For case-sensitive counting, SUMPRODUCT with EXACT can help.
  • COUNTIF only supports one condition. Use COUNTIFS if multiple conditions are needed.

Key takeaways and what to try next

Counting cells equal to a specific value is one of the most fundamental Excel tasks. Whether you use the elegant simplicity of COUNTIF or the flexibility of SUMPRODUCT with Boolean arrays, you’ll get fast, reliable counts.

Next time you manage lists of items, try both methods on a sample dataset. Experiment with changing the criteria from text to numbers and see how Excel handles them. Once comfortable, explore combining conditions with COUNTIFS or expanding SUMPRODUCT formulas with more complex Boolean logic.

Tags:

Related tutorials