How to Use Excel to Count Rows with Value (8 Ways)

There are several Microsoft Excel functions to count rows with a value in Excel. In this article, we are going to know about them with examples and explanations.

1. Selecting Range of Cells to Count Rows with Value in Excel 

By selecting the range of cells, we can quickly count the rows with value. Assuming we have a dataset of Microsoft products and their year versions. We can count the rows containing product names.

STEPS:

• First, select all the rows.
• Then at the Status bar on the bottom right-hand side, an option Count is showing the number of active rows that contain values.

2. Applying Excel COUNTA Function to Count Rows with Value

Applying the COUNTA function is one of the dynamic ways to count rows with data. Here we some Microsoft products in a row. We are going to count the total number of rows at Cell C10 that contain product names.

STEPS:

• First of all, select Cell C10.
• Then type the formula:

• Now hit Enter to see the result.

Read More: How to Count Rows with Formula in Excel (5 Quick Methods)

3. Counting Rows with Numerical Value with COUNT Function in Excel 

Sometimes row contains a numeric value in Excel. COUNT function helps us to count them. Presume we have a dataset of Microsoft products with their year version. We are going to count the numerical value containing rows at Cell C10.

STEPS:

• Firstly, select Cell C10.
• Then type the formula:

• In the end, press Enter and we will see the result.

4. Applying COUNTIF Function to Count Rows with Text Value in Excel

With the help of a wild character Asterisk (*), we can apply the COUNTIF function to count rows with text values. Asterisk helps us to find out any number of characters in a row. If there is a combination of numerical & text values in a row, it also helps to consider the row as a text value. We have a dataset of Microsoft products.

STEPS:

• At first, select Cell C10.
• Now type the formula:

• Then hit Enter for the result.

5. Using Excel SUM, MMULT, TRANSPOSE & COLUMN Functions to Count Rows with Specific Value

We can apply an array formula containing SUM, MMULT, TRANSPOSE & COLUMN functions to find rows that contain a specific value. Let’s say we have a worksheet containing Microsoft products and their year version. We will find out the number of rows holding “2017” at Cell C10.

STEPS:

• Select Cell C10.
• Type the formula:

• Hit Enter to see the result.

➤➤➤ Simplification of the Formula:

• The logical criterion of the formula is:

This generates the TRUE/FALSE array result and the double negative (—) compels the values of TRUE/FALSE in 1 & 0 respectively.

• The array of 4 rows and 2 columns (4*2 array) goes to the MMULT function as Array1.
• To get the column number in an array format, we use the COLUMN function.

• To transform the column array format into a row array, we use the TRANSPOSE function.

• Lastly, the SUM function counts the rows with values.

6. Counting Rows with Multiple OR Criteria in Excel

With the help of Boolean logic and SUMPRODUCT function, we can count rows with multiple OR criteria. From the below dataset, we have to count the rows where product1 is “Word” or product2 is “Excel”.

STEPS:

• Select Cell C10.
• After that type the formula:

➤ NOTE: Here the two logical criteria are attached by the sign Plus (+) as addition is required in Boolean algebra. The first logical criteria test if the product1 is “Word” and the second criteria test if the product2 is “Excel”. We won’t use the SUMPRODUCT function only as it double counts rows with both “Word” &“Excel”. We use double negative(—) as it compels the values of TRUE/FALSE in 1 & 0 respectively with “>0”. A single array of 1s & 0s is created inside the SUMPRODUCT function.

• Then hit Enter for the result.

.

7. Using Excel SUMPRODUCT Function to Count Rows that Meet Internal Criteria 

Assuming we have a dataset of products and the sales record of Group 1 & Group 2. We use the SUMPRODUCT function for counting rows that meets internal criteria.

Criteria:

• Group 1 > Group 2
• Group 2 > Group 1

STEPS:

• Select Cell C10.
• Now for Group 1 > Group 2 criteria, type the formula:

• Hit Enter.
• Then for Group 2 > Group 1 criteria, type the formula:

• Finally, hit Enter and see the result.

8. Using VBA to Count Rows with Value in Excel

We can use VBA code to count rows with values. Here we have a dataset. We are going to count all the used rows that contain data.

STEPS:

• Go to the sheet tab and Right-click on the mouse of the current sheet.
• Select View Code.

• A VBA Module window pops up.
• Now type the following code on it.

Option Explicit
Sub RowsWithValue()
Dim i As Long
Dim q As Range
With ActiveSheet.UsedRange
'loop through each row from the used range
For Each q In .Rows
'check if the row contains a cell with a value
If Application.CountA(q) > 0 Then
'counts the number of rows non-empty Cells
i = i + 1
End If
Next
End With
MsgBox "Used rows number = " & i
End Sub

• Click on the Run option.

• At last, we can see the final counting result in a short message box.

Read More: How to Count Rows with VBA in Excel (5 Approaches)

Download Practice Workbook

Download the following workbook and exercise.

Conclusion

These are the quickest ways to count rows with a value in Excel. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.

Related Articles

• Excel VBA to Count Rows with Data (4 Examples)
• How to Count Rows with Data in Excel (4 Formulas)
• Excel Count Visible Rows (Formula and VBA Code)