ThisCalculator

Everyday

Count Cells Equal to This or That?

Updated · Standard formula, shown below

The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.

This calculator counts how many cells in a selected range match any of several criteria you specify — for example, counting every cell that equals “red” or “blue,” or every number that is 5, 10, or 15. You enter the range and up to four values (text or numbers), and the result is the total count of cells that match at least one of those values. The calculation uses the standard SUMPRODUCT and COUNTIF approach, so you can verify the result by hand or in your spreadsheet software.

Formula

The calculator uses the array formula:

Total count = SUMPRODUCT(COUNTIF(range, {value1, value2, value3, value4}))

Where:

  • range — the cells you want to check (e.g., A1:A20).
  • value1, value2, … — the criteria; a cell counts if it equals any of these.
  • COUNTIF(range, value) — counts cells equal to that single value.
  • SUMPRODUCT — adds the individual counts together, avoiding the need to press Ctrl+Shift+Enter in most spreadsheet apps.

If you leave a value blank, it is ignored. The formula works for text (case-insensitive in Excel and Google Sheets) and for numbers.

Worked Example

Using the default inputs: range = A1:A20, value1 = “red”, value2 = “blue”, value3 and value4 blank.

Suppose the range contains: red, blue, green, red, blue, yellow, red, blue, red, blue, green, red, blue, red, blue, red, blue, red, blue, red — that is 12 reds and 8 blues.

Step 1: Count cells equal to “red”: COUNTIF(A1:A20, “red”) = 12.

Step 2: Count cells equal to “blue”: COUNTIF(A1:A20, “blue”) = 8.

Step 3: Add them: 12 + 8 = 20.

The calculator returns 20, which matches the total number of non-empty cells in this example. If you had other values, they would be added too.

When to Use This Count

This type of count is useful when you have a list of categories and need to know how many items fall into a small set of them. Common situations include:

  • Counting how many orders were placed in a few specific regions (e.g., “North” or “South”).
  • Counting how many products have a status of “Pending” or “In Review”.
  • Counting how many test scores are exactly 90, 95, or 100.
  • Counting how many cells contain one of several error codes.

It is not the same as counting cells that contain a substring (e.g., any cell with “red” inside a longer text) — that requires wildcards or SEARCH. This calculator only matches exact full-cell equality.

How to Choose the Right Criteria

Be precise about what you want to match:

  • Exact text match — “red” will not match “red ” (with a trailing space) or “Red” if your spreadsheet is case-sensitive (most are not).
  • Numbers — 5 will match the number 5, but not the text “5”. If your range contains numbers stored as text, the count may be zero.
  • Blank cells — if you want to count blanks, use “” as a value, but note that this calculator treats a blank value as “ignore this criterion”.
  • Wildcards — this calculator does not support wildcards; it only does exact equality.

If you need to count cells that contain a word anywhere inside them, use a different formula (e.g., COUNTIF with “*red*”).

Common Mistakes and Pitfalls

Here are the most frequent errors people make when counting with multiple criteria:

  • Using OR logic incorrectly — writing COUNTIF(range, “red”) + COUNTIF(range, “blue”) is exactly what this calculator does, but many people try COUNTIF(range, {“red”,”blue”}) without SUMPRODUCT, which in older Excel versions only returns the first count.
  • Mixing text and numbers — a cell containing the number 5 and a cell containing the text “5” are different. Check your data types.
  • Leading/trailing spaces — “red ” does not equal “red”. Use TRIM or clean your data first.
  • Case sensitivity — Excel and Google Sheets COUNTIF are case-insensitive by default. If you need case-sensitive, you must use a different approach (e.g., SUMPRODUCT with EXACT).
  • Counting empty cells — if you accidentally include a blank criterion, it is ignored, so you won’t count blanks unless you explicitly use “” (which this calculator does not allow as a blank input).

Limits of the Formula

The formula works for up to four values. If you need more, you can extend the pattern by adding more COUNTIF terms. The formula is limited to exact matches; it cannot handle ranges of numbers (e.g., “between 10 and 20”) or partial text matches. For those, you would need COUNTIFS with multiple conditions or wildcards. Also, the formula counts each cell once per matching value — if a cell equals both value1 and value2 (impossible for exact equality), it would be counted twice, but that cannot happen because a cell has only one value.

Performance is fine for typical ranges (up to tens of thousands of cells). For very large datasets, consider using a pivot table or dynamic array functions like FILTER.

Frequently Asked Questions

Does this count cells that contain the value as part of a longer text?

No. This calculator only counts exact matches. For example, if you search for “red”, a cell containing “red apple” will not be counted. To count partial matches, you would need to use wildcards like “*red*” in a COUNTIF formula, but this calculator does not support that.

Is the count case-sensitive?

No, the formula uses COUNTIF, which is case-insensitive in both Excel and Google Sheets. So “Red” and “red” are treated as the same. If you need case-sensitive counting, you must use a different formula with EXACT or a helper column.

What if I want to count cells that equal one of many values (more than four)?

You can extend the formula manually by adding more COUNTIF terms. For example, to count five values, you would write: =COUNTIF(range,”a”)+COUNTIF(range,”b”)+COUNTIF(range,”c”)+COUNTIF(range,”d”)+COUNTIF(range,”e”). The calculator is limited to four for simplicity.

Can I use this to count cells that are not equal to something?

No, this calculator only counts equal-to matches. To count cells that are not equal to a value, you would use COUNTIF(range, “<>value”). That is a different operation.

Why does my result show 0 even though I can see matching cells?

Check for leading or trailing spaces in your data or criteria, or check if the cells contain numbers stored as text. Also, make sure the range is correct and does not include merged cells that might hide values.

Does this work in Google Sheets too?

Yes, the formula SUMPRODUCT(COUNTIF(…)) works in both Microsoft Excel and Google Sheets, and the calculator replicates that logic. In Excel 365, you could also use the newer FILTER function, but the result is the same.

Related

Formula used
in r | Range (e.g. A1:A20) | - | A1:A20 | 0
in v1 | Value 1 | - | red | 0
in v2 | Value 2 | - | blue | 0
in v3 | Value 3 (optional) | - | - | 0
in v4 | Value 4 (optional) | - | - | 0
out count | Matching cells | - | SUMPRODUCT(COUNTIF(r, {v1,v2,v3,v4})) | 0

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.