This post will guide you how to count rows that contain a specific text or number in Excel 2013/2016 or Excel office 365. How do I count the number of rows that contain a particular value while working with your current worksheet in Excel. And you can do this by a array formula based on the SUM function, the MMULT function, the TRANSPOSE function and the COLUMN function in Excel.

1. General Formula

The below general formula can help you to count rows that have specific values in Excel. Like this:

=SUM(--(MMULT(--(criteria), TRANSPOSE(COLUMN(Range)))>0))

Note: this formula is an array formula and you have to press “CTRL + SHITF +Enter” to change it from a normal formula to a array formula.

2. Count Row That Contain Specific Value Using Formula

Assuming that you have a data of list in range B2:C6. And you want to know the number of rows that have a particular value 60 in your given range of cells.  And you can refer to the above generic formula to create a new array formula. Like this:

=SUM(--(MMULT(--(B1:C6=60),TRANSPOSE(COLUMN(B1:C6)))>0))

Let’s See That How This Formula Works:

=--(B2:C6=60)

This formula is a logic criteria, and it is used to generate a TRUE and FALSE array result, and the double negative operator can be used to force the TRUE and FALSE values to 1 and 0 respectively.

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

The Column function can be used to get the column number in an array format. And the TRANSPOSE function is used to change the column array format to row array.

Finally, the SUM function will count all those rows that have your specific value in the given range of cells.

3. Count Row That Contain Specific Value Using VBA Macro

In addition to the array formula, we’ll now explore a VBA macro method to count rows containing a specific value. This method is particularly useful for those who prefer automation or need to handle more complex tasks.

To begin, open your Excel workbook and navigate to the ‘DEVELOPER‘ tab. Press ‘ALT+F11‘ or click ‘Visual Basic‘ to open the Visual Basic for Applications editor.

In the VBA editor, click ‘Insert‘ and then ‘Module‘ to create a new module where we’ll write our macro.

We’ll write a VBA macro that loops through the specified range, checks for the specific value, and counts the occurrences. Let’s enter the code into the module.

Sub CountRowsWithValue()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim targetValue As Variant
    targetValue = InputBox("Enter the value to search for:")
    Dim rowCount As Long
    rowCount = 0
    Dim rng As Range
    Dim r As Long

    ' Prompt user to select a range
    Set rng = Application.InputBox("Select the range to search within:", "Select Range", Type:=8)
    If rng Is Nothing Then
        MsgBox "No range was selected. Please run the macro again and select a range."
        Exit Sub
    End If

    ' Loop through each row in the selected range
    For r = 1 To rng.Rows.Count
        ' Check if any cell in the row contains the target value
        If Application.WorksheetFunction.CountIf(rng.Rows(r), targetValue) > 0 Then
            rowCount = rowCount + 1
        End If
    Next r

    ' Display the result
    MsgBox "The number of rows containing the value '" & targetValue & "' is: " & rowCount
End Sub

Save the code and return to your Excel worksheet. Press ‘ALT + F8‘, select ‘CountRowsWithSpecificValue‘ from the list, and click ‘Run‘ to execute the macro.

The provided VBA code allows you to input a value and select a range of cells. The macro will then count and display the number of rows containing that value.

After running the macro and selecting the range and value, a message box will display the count of rows containing the specific value within your chosen range.

4. Video: Count Row That Contain Specific Value

This Excel video tutorial will show you how to count rows with specific values in Excel. We’ll cover two methods: an array formula for manual calculation and a VBA macro for automated counting.

5. Related Functions

• Excel COLUMN function
  The Excel COLUMN function returns the first column number of the given cell reference.The syntax of the COLUMN function is as below:=COLUMN ([reference])….
• Excel SUM function
  The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
• Excel TRANSPOSE function
  Excel TRANSPOSE formula allows you to rotate (swap) values from rows to columns and vice versa in Excel.The Excel TRANSPOSE Function syntax:=TRANSLATE (range) …