Table of Contents
In Excel, there are three ways to count the number of rows with a specific value. These include using the COUNTIF formula, filtering the data and then counting the results, and using the SUMPRODUCT formula. Each of these methods allow you to quickly and easily identify the number of rows with a specific value in a spreadsheet.
You can use the following methods to count rows with a particular value in Excel:
Method 1: Count Rows with Any Value
=COUNTIF(B2:B11, "<>")
Method 2: Count Rows with No Value
=COUNTBLANK(B2:B11)
Method 3: Count Rows with Specific Value
=COUNTIF(B2:B11, "50")
The following examples show how to use each method with the following dataset in Excel:
Example 1: Count Rows with Any Value
We can use the following formula to count the number of rows with any value in column B:
=COUNTIF(B2:B11, "<>")
The following screenshot shows how to use this formula in practice:
We can see that there are 7 rows with any value in the Points column.
Example 2: Count Rows with No Value
We can use the following formula to count the number of rows with no value in column B:
=COUNTBLANK(B2:B11)
The following screenshot shows how to use this formula in practice:
We can see that there are 3 rows with no value in the Points column.
Example 3: Count Rows with Specific Value
We can use the following formula to count the number of rows with a value of “8” in column B:
=COUNTIF(B2:B11, "8")
The following screenshot shows how to use this formula in practice:
We can see that there are 3 rows with a value of “8” in the Points column.