ThisCalculator

Everyday

Count Rows with Specific Values in Excel

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 article explains how to count rows in Excel that contain specific values, covering the COUNTIF, COUNTIFS, and SUMPRODUCT functions. You will learn the formulas, their differences, and practical examples for single and multiple conditions.

Formula

The basic formula to count cells that equal a specific value is =COUNTIF(range, criteria). For multiple conditions, use =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...). The SUMPRODUCT function can also count rows based on multiple conditions across columns. Each function returns the number of cells that meet the given condition(s).

Worked Example

Suppose you have a column of 100 numbers and you want to count how many are greater than 0. Using the default values from the calculator: value = 10, range rows = 100. The formula in the calculator is r * (v > 0), which simplifies to 100 * 1 = 100. So, the result is 100 rows. This matches what you would get with =COUNTIF(A1:A100, ">0") if all 100 cells were greater than 0.

Understanding COUNTIF and COUNTIFS

COUNTIF counts cells that meet a single condition. The syntax is =COUNTIF(range, criteria). The criteria can be a number, text, expression, or cell reference. For example, =COUNTIF(A1:A10, 5) counts cells equal to 5. To count cells greater than 5, use =COUNTIF(A1:A10, ">5"). Text criteria should be in quotes, like =COUNTIF(B1:B10, "Pending").

COUNTIFS extends this to multiple conditions, all of which must be true for a cell to be counted. For instance, =COUNTIFS(A1:A10, "Apple", B1:B10, "Sold") counts rows where column A is “Apple” and column B is “Sold”. Each condition is applied to the corresponding range, and all ranges must be the same size.

Using SUMPRODUCT for Complex Counts

SUMPRODUCT can count rows based on conditions across multiple columns without needing array formulas. For example, to count rows where column A is “X” and column B is “Y”, use =SUMPRODUCT((A1:A10="X")*(B1:B10="Y")). Each condition returns an array of TRUE/FALSE, which multiplies to 1/0, and SUMPRODUCT sums them. This is useful when you need to count based on calculated criteria or when using OR logic.

Counting with OR Logic

To count rows that contain any of several values, use multiple COUNTIF functions added together. For example, =COUNTIF(A1:A10, "Red") + COUNTIF(A1:A10, "Blue") counts cells that are either Red or Blue. With SUMPRODUCT, you can write =SUMPRODUCT((A1:A10="Red")+(A1:A10="Blue")). Remember that adding TRUE values works because TRUE equals 1 in arithmetic.

Common Mistakes and How to Avoid Them

  • Mismatched range sizes: In COUNTIFS, all ranges must be the same size, or you’ll get a #VALUE! error.
  • Using quotes incorrectly: Numeric criteria without operators don’t need quotes, but text and operators do. For example, =COUNTIF(A1:A10, ">5") is correct, not =COUNTIF(A1:A10, >5).
  • Counting blank cells: To count blanks, use =COUNTIF(range, "") or =COUNTBLANK(range). To count non-blanks, use =COUNTIF(range, "<>").
  • Wildcard characters: Use * for partial matches and ? for single characters. For example, =COUNTIF(A1:A10, "A*") counts cells starting with “A”.

When to Use Each Function

Use COUNTIF for a simple single-condition count. Use COUNTIFS when you have multiple conditions that must all be true. Use SUMPRODUCT when you need more flexibility, such as counting with OR logic or based on calculated values. For example, to count rows where the date is in 2023, you could use =SUMPRODUCT((YEAR(A1:A10)=2023)*1).

Frequently Asked Questions

How do I count rows that contain a specific text in multiple columns?

Use COUNTIFS with each column as a separate range. For example, =COUNTIFS(A1:A10, "Yes", B1:B10, "Yes") counts rows where both columns have “Yes”. If you want to count rows where any column has “Yes”, use SUMPRODUCT with OR logic.

Can I count rows based on a cell value in another sheet?

Yes, reference the other sheet in the range, like =COUNTIF(Sheet2!A1:A10, B1). The formula works across sheets as long as the range is valid.

How do I count rows with unique values?

To count unique values in a range, use =SUMPRODUCT(1/COUNTIF(range, range)). This works for numeric and text values, but be careful with blank cells as they can cause errors.

What is the difference between COUNTIF and COUNT?

COUNT counts only numeric cells, while COUNTIF counts cells that meet a specified condition, which can include text. For example, COUNT(A1:A10) counts numbers only, while COUNTIF(A1:A10, “Yes”) counts cells with the text “Yes”.

How do I count rows that meet multiple conditions in different columns?

Use COUNTIFS. For instance, =COUNTIFS(A1:A10, "Product A", B1:B10, ">100") counts rows where column A is “Product A” and column B is greater than 100.

Why does my COUNTIF return 0 when I know there are matches?

Check for leading/trailing spaces, inconsistent data types (text vs. number), or incorrect criteria syntax. Use TRIM to remove spaces and ensure the criteria matches the data format.

Related

Formula used
in v | Value to count | - | 10 | 0
in r | Range rows | rows | 100 | 1
out c | Rows with value | count | r * (v > 0) | 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.