How Excel 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.


Practice Workbook

Download the following workbook and exercise.


8 Quick Ways to Count Rows with Value in Excel

1. Count Rows with Value by Selecting the Range of Cells

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

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


3. COUNT Function to Count Rows with Numerical Value

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. COUNTIF Function to Count Rows with Text Value

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

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. Excel Count Rows with Multiple OR Criteria

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. Excel Count Rows that Meets Internal Criteria with SUMPRODUCT Function

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.

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


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

Nuraida Kashmin

Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. I am a Team Leader of Excel and VBA Content Developer in ExcelDemy. Here I will also post articles related to Microsoft Excel. With a strong educational background in Mechanical Engineering through experimental learning from Rajshahi University of Engineering & Technology. Skilled in Microsoft Word, Creative Writing, Microsoft Excel, Project Management, Support to Organize Different Events, Reporting, Monitoring & Documentation, Online Advocacy and Event Management Related to SAP and Youth Leaders.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo