In this tutorial I explain some methods to count the number of rows usedExcel-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 belowCell D12.
=BELIEF(B5:D10)
HereEXCITEMENTThe function returns the number of rows in the array:B5:D10.
- Come on, hit itInput.
- As a result you get the number of rows that contain values.
2. Apply ExcelFormel zIF 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 formulaCell E5.
=IF(NUM(B5:D5)>0,1,0)
HereNUMBERThe function counts the number of nonblank cells in a rangeB5:D5. On the other hand, if the resultTO COUNTThe formula is greater than0,IFfunction returns1otherwise it returns0.
- Then pressInputand we get the following result forrow 5. Then pull downComplete the handle(+) to copy the formula to the other cells.
- Then for each of the rows we get the following number.
- Now we will useSUM functionto get the total number of rows of data. Enter the following formulaCell D12.
=SUM(E5:E10)
- After that hitInput.
- Finally, here is the total number of rows.
3. Insert a formula using IF and COUNT BLANK functions to count the rows
Similar toMethod 2, now we use a combinationIFICOUNTBLANKFunctions to get a formula to calculate the total number of rows in the data set.
Steps:
- First, enter the following formulaCell E5.
=IF(COUNBLANK(B5:D5)=3,0,1)
HereCOUNTBLANKThe function counts the number of empty cells in a rangeB5:D5. In our dataset we have three columns in each row. So if all cells are empty, fileCOUNTBLANKThe function is returned3. Later,IFThe function is returned0if the line is empty, otherwise1.
- Then pressInput. As a result, we get the following result for each row.
- Similar laterMethod 2, we sum the above result to get the total number of nonblank rows of our data set (B5:D10). Enter the formula belowCell D12and pressInput.
=SUM(E5:E10)
- In summary, we found the total number of rows5.
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 ExcelFormula for counting rowsIF and COUNTIF functions in Excel
Likewise inMethod 2IMethod 3We will use it nowCOUNTIF functionalong withIFFunction to create a formula to find the number of nonblank rows.
Steps:
- First, enter the formula belowCell E5.
=IF(COUNTIF(B5:D5,"")=3,0,1)
HereCOUNT IFThe function counts the number of cells based on criteria. Since we need to find an empty cell in each row, fileCOUNT IFFunction counts spaces ("") in a rangeB5:D5. If all three cells in a row are empty, the fileIF functionphrases0, Otherwise1.
- As a result when pressedInputkey we get the following result.
- Then, as before, we add up these individual numbersE5:E10to get the total number of rows. So enter the formulaCell D12.
=SUM(E5:E10)
- Then strikeInput.
- In the end we get the following number of rows4.
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,IADDITIVEfunctions.
Steps:
- First, enter the formula belowCell D12.
=SUM(--(MMULT(--(B5:D10=80),TRANSPOSE(COLUMN(B5:D10)))>0))
- Then strikeInput.
- Finally we found it5The rows contain a value80.
🔎How does the formula work?
We are looking for the result80’ 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 criterioncreateTRUE/LIEResults for each value in the range. Then double the negative force in each caseTRUE/LIEvalues up to1I0and 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 functionused to generate a numeric array of the appropriate size. TheSPLITThe function returns a 3-column array {2,3,4}, ITRANSPOSE Functionchanges this array to a 3-row array {2;3;4}.
- –(MMULT(–(B5:D10=80),TRANSPOSE(COLUMN(B5:D10)))>0):MMULT FunctionThen 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 entries0and strength againTRUE/LIEAgain1I0with a double minus to get the final array in itADDITIVEFunction.
Finally, in the last array,1represents the row where the logical test was returnedTRUE. Summary returned byADDITIVEThe function is the number of rows (5) that contain a value80.
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? ›- 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")
- 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)
- 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.
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? ›- 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.
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? ›- 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.
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 |
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? ›- 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.