## What Is Count Rows In Excel?

The different methods to count rows in Excel enable one to determine the total number of rows in a given dataset. Users can count Excel rows containing data, numeric values, texts, rows in a range and blank rows while analyzing financial and statistical data.

For example, the first table lists grocery items and their quantities.

And if we must count the total rows of grocery items and quantity values and display the output in cells F2:F3. Then, we can apply the **Excel COUNTA function** as the **formula to count rows in Excel** cells F2:F3.

The **COUNTA()** as the **formula to count rows in Excel** cells F2 and F3 calculates the total number of rows containing data in the specified cell range. And it returns the output as a number.

##### Table of contents

###### Key Takeaways

- The count rows in Excel techniques evaluate the total number of rows in the specified cell range.
- Users can count Excel rows based on data, specific data formats, blanks, and in a range while working with sales, marketing, and financial datasets.
- We can check the
**Count**field in the**Status Bar**to determine the rows count in a chosen cell range. And we can use the Excel inbuilt functions**COUNTA**,**COUNT**,**COUNTBLANK**,**COUNTIF**, and**ROWS**to count rows with data, numbers, blanks, texts, and total rows, respectively.

### How To Count The Number Of Rows In Excel?

Let us see how to **automatically count rows in Excel **and use different inbuilt functions to obtain the Excel row counts based on specific requirements.

#### #1 – Excel Count Rows Which Has Only The Data

We can consider the following method as the **shortcut to count rows in Excel** that contain only data:

- Ensure the dataset contains accurate and valid data.
- Select the cell range in the column where we must count the total rows containing only data.
- Check the
**Excel Status Bar**for the**Count**value, showing the number of non-empty rows in the chosen column range.

##### Basic Example

The image below shows an employee’s details in column C.

And the requirement is to count the total number of non-empty rows in the cell range C2:C8.

Then, here is how to refer to the **Status Bar** as the **shortcut to count rows in Excel** containing data.

**Select the cell range C2:C8.**

Once we choose the required cell range, Excel will show the count of non-empty rows in the chosen range in the**Status Bar**at the bottom-right corner of the worksheet.

In the above example, the chosen cell range contains seven cells, and five appear non-empty. However, the**Count**value in the**Status Bar**is**6**.

The reason is that cell C4 is indeed empty. But cell C5 contains two space characters, making it a non-empty cell.

And, since six cells out of the seven cells in the chosen range are non-empty, Excel shows the required number of total rows containing data as**6**in the**Status Bar**.

Thus, we can use the above method to**automatically count rows in Excel**that are non-empty.

#### #2 – Count All The Rows That Have The Data

The steps to use the **COUNTA()** to count all the rows that have the data are as follows:

- Select the cell range in the required column, where we must count all the non-empty rows.
- Select a target cell to display the output.
- Type
**=COUNTA(**in the cell. [Alternatively, type**=C**or**=CO**and double-click the**COUNTA**function from the Excel suggestions.] - Enter the required cell range as the function argument and close the bracket.
- Press
**Enter**to view the**COUNTA()**output.

##### Basic Example

The table below lists students’ names.** **

Here is how to count all the rows with the data in the cell range A2:A16 using **COUNTA()**. Assume the target cell is B18.

**Step 1:**Choose cell B18 and enter the**COUNTA()**.

*=COUNTA(A2:A16)*

**Step 2:**Press**Enter**to view the**COUNTA()**output in the target cell.

The **COUNTA()** returns the total count of non-empty cells in the specified range.

In the above example, the total number of rows in the chosen range is 15. And the total number of non-empty cells in the chosen range is **10**, which the **COUNTA()** returns.

#### #3 – Count The Rows That Only Have The Numbers

The steps to use the **Excel COUNT function** to count the rows that only contain numbers are as follows:

- Select the cell range in the required column, where we must count all the rows containing numbers.
- Select a target cell to display the output.
- Type
**=COUNT(**in the cell. [Alternatively, type**=C**or**=CO**and double-click the**COUNT**function from the Excel suggestions.] - Enter the required cell range as the function argument and close the bracket.
- Press
**Enter**to view the**COUNT()**output.

##### Basic Example

The following table lists numeric values.

Here is how to count all the rows containing numbers in the cell range A2:A12 using **COUNT()**. Assume the target cell is B14.

**Step 1:**Choose cell B14 and enter the**COUNT()**.

*=COUNT(A2:A12)*

**Step 2:**Press**Enter**to view the**COUNT()**output in the target cell.

The **COUNT()** returns the total count of cells containing numbers in the specified range.

In the above example, the total number of rows in the chosen range is 11. And the values in the non-empty cells in the chosen range are numbers, though in different valid number formats. We can confirm the same by clicking each cell and checking the value in the Formula Bar.

And hence, the **COUNTA()** returns the total number of cells containing numbers in the chosen range, **9**.

#### #4 – Count Rows, Which Only Has The Blanks

The steps to use the **Excel COUNTBLANK** function to count the rows that only have the blanks are as follows:

- Select the cell range in the required column, where we must count all the rows containing blanks.
- Select a target cell to display the output.
- Type
**=COUNTBLANK(**in the cell. [Alternatively, type**=C**or**=CO**and double-click the**COUNTBLANK**function from the Excel suggestions.] - Enter the required cell range as the function argument and close the bracket.
- Press
**Enter**to view the**COUNTBLANK()**output.

##### Basic Example

The following table lists the top US tech companies.

Here is how to count all the blank rows in the cell range A2:A11 using the **COUNTBLANK()**. Assume the target cell is B13.

**Step 1:**Choose cell B13 and enter the**COUNTBLANK()**.

*=COUNTBLANK(A2:A11)*

**Step 2:**Press**Enter**to view the**COUNTBLANK()**output in the target cell.

The **COUNTBLANK()** returns the total count of blank cells in the specified range.

In the above example, the total number of rows in the chosen range is 10. However, the rows 4 and 7 to 9 are blank.

And hence, the **COUNTBLANK()** returns the total number of blank rows in the chosen range as **4**.

#### #5 – Count Rows That Only Have Text Values

Excel does not offer an inbuilt function to count the rows containing only text values directly. However, we can use the **Excel COUNTIF function** with the Wildcard character, ‘*****’, as the criteria to achieve the required outcome.

And the steps are as follows:

- Select the cell range in the required column, where we must count all the rows containing only text values.
- Select a target cell to show the output.
- Type
**=COUNTIF(**in the cell. [ Alternatively, type**=C**or**=CO**and double-click the**COUNTIF**function from the Excel suggestions.] - Enter the required cell range and the Wildcard character, ‘
*****’, as the two function arguments separated by a comma, and close the bracket. - Press
**Enter**to view the**COUNTIF()**output.

##### Basic Example

The following table lists a set of item codes.

The item codes, with the phrase “**TTL_**” suffixing the numeric values, are valid text values.

So, here is how to count all the rows containing valid item codes in the cell range A2:A15, using the **COUNTIF() **and the Excel Wildcard character, ‘*****’. Assume the target cell is B17.

**Step 1:**Choose cell B17 and enter the**COUNTIF()**.

*=COUNTIF(A2:A15,”*”)*

**Step 2:**Press**Enter**to view the**COUNTIF()**output in the target cell.

The **COUNTIF()** returns the total number of cells in the given range that satisfy the specified criteria.

In the above example, the total number of rows in the chosen range is 14.

However, as the Wildcard character, ‘*****’, is the **criteria** argument in the **COUNTIF()**, the function checks for cells containing text values of any length.

And hence, as the criterion holds in cells 2 to 6, 10, 11, 13, and 15, the **COUNTIF()** returns the total number of rows containing text values in the chosen range as **9**.

#### #6 – Count All Of The Rows In The Range

The steps to use the **Excel ROWS function** to count all the rows in a given range are as follows:

- Select the cell range in the required column, where we must count all the rows.
- Select a target cell to display the output.
- Type
**=ROWS(**in the cell. [Alternatively, type**=R**or**=RO**and double-click the**ROWS**function from the Excel suggestions.] - Enter the required cell or array range as the function argument and close the bracket.
- Press
**Enter**to view the**ROWS()**output.

##### Basic Example

The image below shows column A containing the list of the top 15 US states by population.

The requirement is to convert the above dataset into an Excel Table. And then, count the total rows in the given list, including both blank and non-empty ones. Assume the target cell is D1.

Then, here is how to **count rows in Excel Table**.

**Step 1:**Choose the cell range A1:A16 and select**Insert**→**Table**.

The **Create Table** window will open, with the fields updated as shown below.

Click **OK** to obtain the required Excel Table.

**Step 2:**Click on a cell in the Excel Table to enable the**Design**tab in the ribbon.

And check the **Total Row** check box to view the total number of rows containing values in cell A17.

**Step 3:**Cell A17 shows the**Excel SUBTOTAL function**output, indicating the total number of non-empty rows in the Excel Table. However, the total rows count must also include the empty rows.

So, click the cell A17 drop-down button and choose **More Functions**.

The **Insert Function **window opens, where we must select the** ROWS** function.

Clicking **OK** in the **Insert Function **window will open the **Function Arguments** window to insert the **ROWS()** in cell A17.

**Step 4:**Update the**Array**field in the**Function Arguments**window as the cell range A2:A16 in the Excel Table.

Click **OK** in the **Function Arguments** window to view the total number of rows in the Excel Table in the target cell A17, **15**.

[ Alternatively, we can select cell D1 and enter the **ROWS()** to **count rows in Excel Table** created previously.

Next, enter the cell range A2:A16, where we must count the total rows. And then, close the bracket.

Finally, press **Enter**.

Thus, we will obtain the total count of all rows in the given Excel Table in the target cell D1, **15**.]

### Important Things To Note

- When we count rows in Excel, even a single space character in a cell gets counted as a value.
- Please avoid using the
**COUNT**functions for counting rows when cells are merged, as they might return incorrect values. - The
**Total Row**option in the**Design**tab does not include the specific column’s header in the Excel Table when displaying the total rows count.

### Frequently Asked Questions (FAQs)

**1. How to count unique rows in Excel?**

We can count unique rows in Excel using the **COUNTIF()** within the **SUMPRODUCT()**.

For example, the following table lists values in various valid data formats.

And the requirement is to count the unique rows in the given dataset. Assume the target cell is B10.

Then, here is how to use the **COUNTIF()** and **SUMPRODUCT()** in the target cell to achieve the required output.**• Step 1: **Choose cell B10, enter the following formula, and press **Enter**.*=SUMPRODUCT(((A2:A8<>””)/COUNTIF(A2:A8, A2:A8&””)))*

The above formula ignores blank spaces.

First, the numerator is an array of seven **TRUEs** since the specified range contains no blank cells.

Next, the **criteria** in the **COUNTIF()** is the array **{“50″;”50″;”45097″;”Samsung Galaxy Z Flip 4″;”45097″;”Samsung Galaxy Z Flip 4″;”0.5”}**.

And then, the **COUNTIF()** counts the appearances of each array value in the specified cell range and returns the array **{2,2,2,2,2,2,1}**. The values in the array indicate that the first six values appear twice, and the last value appears once in the given dataset.

So, the **SUMPRODUCT() **input becomes:**{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}/{2,2,2,2,2,2,1}**.

And the division results in the array **{0.5;0.5;0.5;0.5;0.5;0.5;1}**, since the **TRUE** value equals **1**.

Finally, the **SUMPRODUCT()** adds all the values in the array to return the number of unique rows as **4**.

Furthermore, cells A2 and A3 contain the same value but in the **Currency **and **Accounting **data formats. Next, cells A4 and A6 contain the same date value but in different date formats. On the other hand, cells A5 and A7 contain the same text and cell A8 contain a percentage value.

So, the total number of unique values in the given dataset is 4, corroborating the obtained count of unique rows.

**2. How to count filtered rows in Excel?**

We can count filtered rows in Excel using the following steps:**1)** Choose the required column containing the source cell range and select **Data** → **Filter** to apply the filter to the column.**2)** Select a target cell to display the output.**3)** Enter the following **SUBTOTAL()**.**=SUBTOTAL(3,Cell_Range)**

Or**= SUBTOTAL(103,Cell_Range)**

The first argument, **3** and **103**, indicates **COUNT **as the chosen function to apply to the cell range in the required column, specified as the second argument.

And, while the first formula counts with hidden rows, the second counts without hidden rows.**4) **Press **Enter** to execute the **SUBTOTAL()** in the target cell.**5) **Click the given column’s filter button and select the values to filter for display. And once we click **OK**, the filtered values show in the sheet, with the **SUBTOTAL()** in the target cell displaying the count of filtered rows in the chosen cell range.

**3. How to count rows in Excel with multiple criteria?**

We can count rows in Excel with multiple criteria using the following inbuilt functions:**• COUNTIF() with multiple text values as criteria.****• COUNTIF() with multiple date values as criteria.****• COUNTIFS() based on multiple conditions.****• SUMPRODUCT() with AND criteria.****• SUMPRODUCT() with OR criteria.**

### Download Template

This article must be helpful to understand the **Count Rows In Excel**, with its formula and examples. You can download the template here to use it instantly.

### Recommended Articles

This has been a guide to What Is Count Rows In Excel. We learn doing it using status bar, COUNTA, COUNT, COUNTBLANK, COUNTIF & ROWS functions, with examples. You can learn more from the following articles –

## Leave a Reply