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

Get FREE Advanced Excel Exercises with Solutions!

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.

Count Rows with Value by Selecting the Range of Cells

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.

Applying COUNTA Function to Count Rows with Value

STEPS:

  • First of all, select cell C10.
  • Then type the formula:
=COUNTA(B5:B8)

  • Now hit Enter to see the result.


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.

COUNT Function to Count Rows with Numerical Value

STEPS:

  • Firstly, select cell C10.
  • Then type the formula:
=COUNT(B5:C8)

  • 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.

COUNTIF Function to Count Rows with Text Value

STEPS:

  • At first, select cell C10.
  • Now type the formula:
=COUNTIF(B5:B8,"*")

  • 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” in cell C10.

SUM, MMULT, TRANSPOSE & COLUMN Functions to Count Rows with Specific Value

STEPS:

  • Select cell C10.
  • Type the formula:
=SUM(--(MMULT(--(C5:D8=2017),TRANSPOSE(COLUMN(C5:D8)))>0))

  • Hit Enter to see the result.

➤➤➤ Simplification of the Formula:

  • The logical criterion of the formula is:
=--(C5:D8=2017)

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.
=COLUMN(C5:D8)
  • To transform the column array format into a row array, we use the TRANSPOSE function.
=TRANSPOSE(COLUMN(C5:D8))
  • 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.

Excel Count Rows with Multiple OR Criteria

STEPS:

  • Select cell C10.
  • After that type the formula:
=SUMPRODUCT(--((C5:C8="Word")+(D5:D8="Excel")>0))

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

Excel Count Rows that Meets Internal Criteria with SUMPRODUCT Function

STEPS:

  • Select cell C10.
  • Now for Group 1 > Group 2 criteria, type the formula:
=SUMPRODUCT(--(C5:C8>D5:D8))

Excel Count Rows that Meets Internal Criteria with SUMPRODUCT Function

  • Hit Enter.
  • Then for Group 2 > Group 1 criteria, type the formula:
=SUMPRODUCT(--(C5:C8<D5:D8))

  • 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.

Using VBA to Count Rows with Value in Excel

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.


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.


<< Go Back to Count Rows | Formula List | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nuraida Kashmin
Nuraida Kashmin

Nuraida Kashmin, Bachelor's degree in Mechanical Engineering from Rajshahi University of Engineering & Technology. Since 2021, she wrote 45+ articles on Excel problems and reviewed over 1000 articles. Currently working as a Project Manager, she is responsible for developing and implementing content strategies, managing writers and editors, staying updated on new technology, analyzing data, and tracking content performance indicators. Her interests include Project Management, Creative Writing, Digital Marketing, Reporting, Monitoring & Documentation, and Online Advocacy for SAP &... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo