How to Use Countif in VBA in Microsoft Excel

In this article, we will learn how to work on vba and macros in Excel. We will be learning how to use countif in VBA.

Visual Basic for Applications (VBA) is the programming language of Excel and other offices. We all know that how we can use “Countif” function in Excel and how it will work. Now in this article, we are going to learn how to use COUNTIF function in VBA.

We will do it by taking an example. Let’s say we have a list of numbers and we need to find the count of numbers which are greater than 50 as shown below.

1
In this case, we will use the COUNTIF function in VBA and using macro to view the result.
Following steps will guide you:

Click on Developer tab > Visual Basic

2

Click on Insert > Module
3

This will create a module which can run on Excel
Enter the following code in the module

Sub Countif()

Range(“B10?).Value = Application.WorksheetFunction.Countif(Range(“A2:A8?), “>” & 50)

End Sub

Explanation: This “Countif” module will counts the cells in range A2:A8 cells for the numbers which are greater than 50 and returns the total count in B10 cell

Now come to the worksheet and use the ALT + F8 shortcut key for opening Macro.

Under Macro window, select “Countif” module and click Run.

5

Alternatively, you can press F5 to run the code in VBA screen.

After executing the macro we will get the output, refer to below snapshot.

6

NOTE: In above snapshot with VBA code you will only see the result, not the formula in cell B10.

VBA in Excel helps the user to build customized options and call whenever required. In this way, we can use COUNTIF in VBA code with one criteria. Hope you got this. You can perform this function in Excel 2016 and all the other versions. Find more articles on VBA and COUNTIF here. Mention any query down in the comment box below.

Users are saying about us...

  1. Jeyner Lopez

    Hi, nice post, Using VBA how is possible scroll up a column and count the numbers after some value, let say 1, and count how many time 2 follow 1, and so on until reach the set limit in the case.

  2. I just want to thank you for sharing such amazing information with us. The images are also very helpful to understand the actual concept. As with many worksheet functions, you will have to call the Application.Worksheet.Function property in the VBE to gain access. For this case, we use the .Countif() method. Thanks for sharing.

Leave a Reply to Synkronizer Cancel reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube