In this tutorial I explain some methods to count the number of rows used**Excel-Formel**. Usually we can get the number of rows by just clicking on the column headers. However, Excel has several options for counting rows that contain values. For example, we can use some functions and combination of functions to count rows in Excel. So let's look at the methods.

**Contents** hide

Download the workbook

5 quick ways to count rows with a formula in Excel

1. Use the Formula with ROWS function to count rows in Excel

2. Apply Excel formula with IF and COUNT functions to count rows

3. Insert a formula using IF and COUNT BLANK functions to count the rows

4. Use Excel formula to count rows with IF and COUNTIF functions in Excel

5. Combination of MMULT, TRANSPOSE, COLUMN and SUM functions to count rows

Application

Related Articles

**Download the workbook**

You can download the workbook we used to create this article.

**Count the lines with Formula.xlsx**

**5 quick ways to count rows with a formula in Excel**

**1. Use the Formula with ROWS function to count rows in Excel**

Excel has a built-in function to count row numbers. We can get the number of rows of a range by simply applyingROW functionFormula. For example, we have a data set (**B5:D10**) with biographical data of several people. So let's find the number of rows of this dataset.

**Steps:**

- First, enter the formula below
**Cell D12**.

`=BELIEF(B5:D10)`

Here**EXCITEMENT**The function returns the number of rows in the array:**B5:D10**.

- Come on, hit it
**Input**. - As a result you get the number of rows that contain values.

**2. Apply Excel****Formel z****IF and COUNT functions Count rows**

Sometimes large datasets contain empty rows. In this case, you may need to count the rows that contain data. In these situations, we can create formulas using Excel functions. For example, we can get a formula using a combinationIFINUMBERfunctions. Let's add an additional column.**To count**' in our existing dataset to simplify the calculation.

**Steps:**

- First, we check whether the rows in our dataset contain values or not. To do this, enter the following formula
**Cell E5**.

`=IF(NUM(B5:D5)>0,1,0)`

Here**NUMBER**The function counts the number of nonblank cells in a range**B5:D5**. On the other hand, if the result**TO COUNT**The formula is greater than**0**,**IF**function returns**1**otherwise it returns**0**.

- Then press
**Input**and we get the following result for**row 5**. Then pull down**Complete the handle**(**+**) to copy the formula to the other cells.

- Then for each of the rows we get the following number.

- Now we will use
**SUM function**to get the total number of rows of data. Enter the following formula**Cell D12**.

`=SUM(E5:E10)`

- After that hit
**Input**. - Finally, here is the total number of rows.

**3. Insert a formula using IF and COUNT BLANK functions to count the rows**

Similar to**Method 2**, now we use a combination**IF**ICOUNTBLANKFunctions to get a formula to calculate the total number of rows in the data set.

**Steps:**

- First, enter the following formula
**Cell E5**.

`=IF(COUNBLANK(B5:D5)=3,0,1)`

Here**COUNTBLANK**The function counts the number of empty cells in a range**B5:D5**. In our dataset we have three columns in each row. So if all cells are empty, file**COUNTBLANK**The function is returned**3**. Later,**IF**The function is returned**0**if the line is empty, otherwise**1**.

- Then press
**Input**. As a result, we get the following result for each row.

- Similar later
**Method 2**, we sum the above result to get the total number of nonblank rows of our data set (**B5:D10**). Enter the formula below**Cell D12**and press**Input**.

`=SUM(E5:E10)`

- In summary, we found the total number of rows
**5**.

**Continue reading: Excel VBA: Count rows with specific data (8 examples)**

**Related Readings:**

- How Excel counts rows with a value (8 ways)
- Excel VBA to count rows of data (4 examples)
- How to Count Rows of Data in Excel (4 Formulas)
- Count Filtered Rows in Excel Using VBA (Step by Step Guide)

**4. Use Excel****Formula for counting rows****IF and COUNTIF functions in Excel**

Likewise in**Method 2**I**Method 3**We will use it nowCOUNTIF functionalong with**IF**Function to create a formula to find the number of nonblank rows.

**Steps:**

- First, enter the formula below
**Cell E5**.

`=IF(COUNTIF(B5:D5,"")=3,0,1)`

Here**COUNT IF**The function counts the number of cells based on criteria. Since we need to find an empty cell in each row, file**COUNT IF**Function counts spaces ("") in a range**B5:D5**. If all three cells in a row are empty, the file**IF function**phrases**0**, Otherwise**1**.

- As a result when pressed
**Input**key we get the following result.

- Then, as before, we add up these individual numbers
**E5:E10**to get the total number of rows. So enter the formula**Cell D12**.

`=SUM(E5:E10)`

- Then strike
**Input**. - In the end we get the following number of rows
**4**.

**Continue reading: How to Count Rows Using Multiple Criteria in Excel (6 Methods)**

**5. Combination of MMULT, TRANSPOSE, COLUMN and SUM functions to count rows**

Sometimes in a large dataset we need to count the number of rows with a specific value. For example, we have a data set of student test scores. Now we will find out how many rows in our data set the result will have**„80“**. To serve our purpose we will use a combinationMANY,TRANSPOSE,**SPLIT,**I**ADDITIVE**functions.

**Steps:**

- First, enter the formula below
**Cell D12**.

`=SUM(--(MMULT(--(B5:D10=80),TRANSPOSE(COLUMN(B5:D10)))>0))`

- Then strike
**Input**. - Finally we found it
**5**The rows contain a value**80**.

**🔎****How does the formula work?**

We are looking for the result**80**’ in the record. The most difficult thing is that the result "**80**' can appear in any column of the dataset and can be in more than one column of the same row. The logical criteria used here are:

**–(B5:D10=80):**This is a logical criterioncreate**TRUE**/**LIE**Results for each value in the range. Then double the negative force in each case**TRUE**/**LIE**values up to**1**I**0**and generate the following table:

**{0,0,1;0,1,0;1,0,0;0,1,0;1,0,0;0,0,0}**

**TRANSPOSE(Column(B5:D10)):**Now,**COLUMN function**used to generate a numeric array of the appropriate size. The**SPLIT**The function returns a 3-column array {**2,3,4**}, I**TRANSPOSE Function**changes this array to a 3-row array {**2;3;4**}.**–(MMULT(–(B5:D10=80),TRANSPOSE(COLUMN(B5:D10)))>0):MMULT Function**Then it runs and returns the result of a 6 x 1 array:

**SUM(–({4;3;2;3;2;0}>0))**

Now we use > to check if there are any non-zero entries**0**and strength again**TRUE**/**LIE**Again**1**I**0**with a double minus to get the final array in it**ADDITIVE**Function.

Finally, in the last array,**1**represents the row where the logical test was returned**TRUE**. Summary returned by**ADDITIVE**The function is the number of rows (**5**) that contain a value**80**.

**Continue reading: How to Count Rows of Data in a Column Using VBA in Excel (9 Ways)**

**Application**

In the article above, I tried to cover the methods in detail. We hope these methods and explanations are enough to solve your problems. If you have any questions please let me know.

**Related Articles**

- How to Count Rows with VBA in Excel (5 Approaches)
- Count visible rows in Excel (formula and VBA code)
- How to Count Lines of Text in Excel (8 Easiest Ways)
- Count the rows in a group with a pivot table in Excel (step by step guide)
- Excel VBA: Count Rows in a Named Range (4 Methods)
- How to count rows in a selection using VBA in Excel
- Excel VBA: Count rows in a worksheet (5 examples)

## FAQs

### How do I count the number of rows in an Excel formula? ›

To count the rows containing numbers, you can apply the COUNT function to the data. In this example, we will use cell B13 to apply the formula. Select the range A1 to A12 and enter the formula **=COUNT(A1:A12)** in cell B13. Press ENTER key here, and it will return the count of rows containing only numbers.

**What is the formula to count rows with data? ›**

**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")

**How do I count rows in Excel with multiple criteria? ›**

**How to Countif Multiple Criteria?**

- Step 1: document the criteria or conditions you wish to test for.
- Step 2: type “=countifs(“ and select the range you want to test the first criteria on.
- Step 3: input the test for the criteria.
- Step 4: select the second range you want to test (it can be the same range again, or a new one)

**How do I count the number of rows in VBA? ›**

**Use VBA to Count Rows**

- First, you need to define the range for which you want to count the rows.
- After that, use a dot (.) to open the list of properties and methods.
- Next, type or select the “Rows” property.
- In the end, use the “Count” property.

**How do I count rows in Excel with filters? ›**

To count filtered rows in Excel, you can **use the SUBTOTAL function that allows you to select its count feature in the first argument and the range from which you want to count filtered rows**. After using this formula, when you apply a filter on that range and filter cells, it will only count the visible rows.

**How do I count the number of rows in Excel excluding hidden rows? ›**

Select a blank cell you will place the counting result into, and type the formula **=COUNTVISIBLE(B2:H11) (B2:H11 is the range where you will count cells ignoring hidden cells/rows/columns) into it, and press the Enter key**.

**How do I sum the number of rows based on a cell value in Excel? ›**

To total the values in a range containing several rows, simply **specify the desired range in the Sum formula**. For example: =SUM(B2:D6) - sums values in rows 2 to 6. =SUM(B2:D3, B5:D6) - sums values in rows 2, 3, 5 and 6.

**How do you count the number of rows in a condition? ›**

To get the number of rows to count that matches the condition, you should **use first df[] to filter the rows and then us the len() to count the rows after rows are filtered with the condition**. You need to select the "Courses" column in DataFrame to check if any value of the "Courses" column is equal to "Pandas" .

**How do you use the count formula? ›**

Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers. For example, you can enter the following formula to count the numbers in the range A1:A20: **=COUNT(A1:A20)**. In this example, if five of the cells in the range contain numbers, the result is 5.

**Can you use Countifs on rows? ›**

Count Cells that Match multiple criteria

In Excel 2007 and later versions, **you can use the COUNTIFS function to count rows that meet two or more criteria**.

### How do I count rows with specific text in Excel? ›

To count the cells with text in Excel, **choose a destination cell and enter the formula =COUNTIF(range,criteria)**. Here, the range denotes the array of cells within which you want the function to act. The criteria variable denotes the condition to satisfy when counting the values.

**How do I count the number of rows between two values? ›**

Select a blank cell which you want to put the counting result. **Copy and paste the formula =COUNTIFS(B2:B8,">75", B2:B8,"<90") into the Formula Bar, and then press the Enter key**. Then you can see the result of cell numbers displaying in the selected cell immediately.

**How do I count rows after filter in Excel VBA? ›**

**Steps to Count Filtered Rows in Excel with VBA**

- STEP 1: Apply Filter in the Dataset. In the first place, select any cell of your dataset. ...
- STEP 2: Launch the VBA Window to Count Filtered Rows in Excel. ...
- STEP 3: Type and Save the Excel VBA Code. ...
- STEP 4: Run the VBA Code to Count Filtered Rows in Excel.

**How to count length in VBA? ›**

**The VBA LEN function is listed under the text category of VBA functions**. When you use it in a VBA code, it returns the number of characters in a string. In simple words, you can count the number of characters of a string. It works like the LEN function in the worksheet.

**How do you count in Excel with a condition? ›**

To count numbers or dates that meet a single condition (such as equal to, greater than, less than, greater than or equal to, or less than or equal to), **use the COUNTIF function**.

**What is the Countifs function in Excel? ›**

The COUNTIFS function is a premade function in Excel, which **counts cells in a range based on one or more true or false condition**. It is typed =COUNTIFS : =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

**How do I count rows without using count function? ›**

**Count Rows of a table Without using Count() Function**

- SELECT so.[name] as.
- , CASE WHEN si. indid between 1 and 254.
- THEN si.[name] ELSE NULL END.
- AS [Index Name]
- , si. indid, rows.
- FROM sys. sysindexes si.
- INNER JOIN sysobjects so.
- ON si. id = so. id.

**How do I count rows in sheets? ›**

**Select a blank cell and type the =COUNTA function including the range of cells that you want to count**. For example, we used =COUNTA(A2:A11). Just hit enter, and the COUNTA function will automatically count the cells that are not blank.

**What is the difference between count and Counta? ›**

**The Count function counts the number of records that contain a number in a single-column table.** **The CountA function counts the number of records that aren't blank in a single-column table**. This function includes empty text ("") in the count.

**How do I count rows in a group? ›**

To count the number of rows, **use the id column which stores unique values** (in our example we use COUNT(id) ). Next, use the GROUP BY clause to group records according to columns (the GROUP BY category above). After using GROUP BY to filter records with aggregate functions like COUNT, use the HAVING clause.

### How you can sum up the rows and column number quickly in the Excel sheet? ›

If you need to sum a column or row of numbers, let Excel do the math for you. **Select a cell next to the numbers you want to sum, click AutoSum on the Home tab, press Enter**, and you're done. When you click AutoSum, Excel automatically enters a formula (that uses the SUM function) to sum the numbers.

**How to add the total amounts using a formula with cell references in Excel? ›**

You can add individual values, cell references or ranges or a mix of all three. For example: **=SUM(A2:A10)** Adds the values in cells A2:10. =SUM(A2:A10, C2:C10) Adds the values in cells A2:10, as well as cells C2:C10.

**What is the difference between Countif and Countifs? ›**

Both COUNTIF and COUNTIFS serve very similar purposes. Both are used to count the number of cells that match conditions. COUNTIF is a simpler function if you only need a single check. COUNTIFS, on the other hand, is highly useful when you need to test against multiple conditions.

**What is the formula how do you count cells with specific text? ›**

In the empty cell, type in the following: “ **=COUNTIF (range, criteria)** .” This formula counts the number of cells containing text inside your cell range. For the “range” part, type the “cell range” you want to count. Enter the first and last cells separated by a colon.

**What is the formula for Counta? ›**

The COUNTA function is an inbuild statistical excel function that counts the number of non-blank cells (not empty) in a cell range or the cell reference. For example, cells A1 and A3 contain values but, cell A2 is empty. The formula “**=COUNTA(A1,A2,A3)**” returns 2.

**How do I count the number of rows in sheets? ›**

**Select a blank cell and type the =COUNTA function including the range of cells that you want to count**. For example, we used =COUNTA(A2:A11). Just hit enter, and the COUNTA function will automatically count the cells that are not blank.

**What is the total number of rows and columns in Excel? ›**

Feature | Maximum limit |
---|---|

Total number of rows and columns on a worksheet | 1,048,576 rows by 16,384 columns |

Column width | 255 characters |

Row height | 409 points |

Page breaks | 1,026 horizontal and vertical |

**What is the total number of rows in a spreadsheet? ›**

Explanation. **ROWS counts the number of rows in any supplied range and returns a number as a result**. For example, if we provide all of column A in a range, Excel returns 1,048,576 the total number of rows in an Excel worksheet.

**What is the formula to count in sheets? ›**

The COUNT function is a premade function in Google Sheets, which counts cells with numbers. It is typed **=COUNT** and gets a list of cells: =COUNT(value1, [value2, ...]) You can select cells one by one, but also ranges, or even multiple ranges.

**What is the formula to count number of sheets in Excel? ›**

Count the number of sheets in a workbook SHEETS function

Select a blank cell, than type **=SHEETS()** into it, press Enter key to get the number of sheets.

### What is sum formula in Excel? ›

The SUM function adds values. You can add individual values, cell references or ranges or a mix of all three. For example: **=SUM(A2:A10)** Adds the values in cells A2:10. =SUM(A2:A10, C2:C10) Adds the values in cells A2:10, as well as cells C2:C10.

**How do I count cells with text in Excel? ›**

**On the Formulas tab, click More Functions, point to Statistical, and then click one of the following functions:**

- COUNTA: To count cells that are not empty.
- COUNT: To count cells that contain numbers.
- COUNTBLANK: To count cells that are blank.
- COUNTIF: To count cells that meets a specified criteria.