How to Count Regex with COUNTIF in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

While working in Microsoft Excel sometimes we need to find specific patterns using regular expressions. But regexes are not supported in Excel. Thus you might face difficulties. Today in this article, I am sharing with you how to count regex with COUNTIF in Excel.

Count Regex with COUNTIF in Excel: Detailed Steps

In the following, I have shared detailed steps to count regex with COUNTIF in Excel.

Step 1: Preparing Dataset for Regex Match

Suppose we have a dataset of some Contact List. Now we will use the COUNTIF function with regex to match in Excel.

As you can see we have a Contact List from which we will check whether it has Phone Number or not in the table.

Sample dataset of COUNTIF Regex in Excel

Step 2: Preparing Customed Helper Function with VBA

In this part, we will use VBA code to custom helper function, the RegExMatch function. Follow the instructions below-

  • First, while the workbook is opened press ALT+F11 to open the “Microsoft Visual Basic for Applications” window.
  • Second, choose “Module” from the “Insert” option.

  • Next, inside the module place the following code and save it-
Public Function RegExMatch(input_reg_range As Range, reg_pattern As String, Optional reg_match As Boolean = True) As Variant
  Dim WX() As Variant
  Dim Input_Row, Input_Col, cnt_Rows, cnt_Cols As Long
  On Error GoTo ErrHandl
  RegExMatch = WX
  Set Wregex = CreateObject("VBScript.RegExp")
  Wregex.pattern = reg_pattern
  Wregex.Global = True
  Wregex.MultiLine = True
  If True = reg_match Then
    Wregex.ignorecase = False
    Wregex.ignorecase = True
  End If
  cnt_Rows = input_reg_range.Rows.Count
  cnt_Cols = input_reg_range.Columns.Count
  ReDim WX(1 To cnt_Rows, 1 To cnt_Cols)
  For Input_Row = 1 To cnt_Rows
    For Input_Col = 1 To cnt_Cols
      WX(Input_Row, Input_Col) = Wregex.Test(input_reg_range.Cells(Input_Row, Input_Col).Value)
  RegExMatch = WX
  Exit Function
    RegExMatch = CVErr(xlErrValue)
End Function

  • After that, we will place the following pattern inside the worksheet as regex is not supported in Excel-

Read More: How to Find RegEx Patterns in Excel

Step 3: Using Formula to Find Regex and Count

As we have established the RegExMatch function, this time we will apply the RegExMatch function to find a proper match from a string.

  • Hence, choose a cell (C7) and apply the below formula down-

Here, the RegExMatch function matches whether any part of the string matches with the given expressions.

Using Formula to Find Regex and Count to COUNTIF Regex in Excel

  • Simply, click ENTER to get the output.

  • Now, pull the “Fill Handle” down to fill the column.

Using Formula to Find Regex and Count to COUNTIF Regex in Excel

  • Thereafter, let’s count cells with phone numbers with the below formula in cell (C15)-

Here, the COUNTIF function is used to count cells with the given condition “TRUE” from the given range “C7:C13”.

  • Finally, hit ENTER key from the keyboard to get the result.
  • Here, we have successfully extracted the output with the COUNTIF function and regex in Excel.

Read More: How to Perform Pattern Matching in Excel

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


In this article, I have tried to cover almost all the methods to COUNTIF regex in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience.

Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Wasim Akram
Wasim Akram

Wasim Akram, BSc, Industrial and Production Engineering, Ahsanullah University of Science & Technology, has been working with the ExcelDemy project for 10 months. Currently working as as Excel and VBA content developer who provides authentic solutions to different Excel-related problems and writes amazing content articles regularly. He published almost 150 articles and has many more coming. He is very passionate about learning new things about Microsoft office Suite and Data analysis.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF