Introduction

In this article, we will be discussing Excel VBA count if

Prerequisites

A computer or a laptop containing Microsoft Excel.

The VBA Count If

Count If function in excel is one of the most important functions in Visual Basics for Application. The functions which are criteria based, they are also known as the rulers in Excel for calculations. And, for us to know well the different calculating functions in excel, we should know first count if function.

Parameters in Count if

• Arg1 - this is a required parameter and is used for a range of data types. This is used to the cell range that we wished to count.
• Arg2 - this is a required parameter and is used for a variant data type. In this type, the criteria are usually in a numerical form, an expression, a cell reference, or even a text which defines the cells that will be counting on. It can be used as 45, "45", ">45", or "rose".

Using Count If Function

Code:

Option Explicit

Sub Example1()

    Range("G4").Value = WorksheetFunction.CountIf(Range("E1:E10"), "Lily")


End Sub

Code explanation:

• Range("G4").Value - this will instruct VBA to give us the return on the cell location G4.
• WorksheetFunction - by adding the Worksheetfunction, this will let VBA that we are accessing the function on the worksheet we are working on.
• CountIf - this is the syntax of the function that we are about to use.
• (Range("E1: E10") - this refers to the range of cells we wished to work on.
• Lily - this refers to the cell value that we are counting on the given cell range.

And the result after running the code:

And we have a return of 2 in cell G4 because in the cell range of E1: E10 we have 2 cells containing the word Lily.

Another example:

Code:

Option Explicit

Sub Countif_Example2()

  Dim VR As Range
  Dim RC As Range
  Dim CV As String

  Set VR = Range("E1:E10")
  Set RC = Range("G4")

  CV = "Lily"

  RC = WorksheetFunction.CountIf(VR, CV)

End Sub

Code explanation:

• Dim VR As Range - this refers to the values listed.
• Dim RC As Range - this is referring to the cell result
• Dim CV As String - this refers to the value of the criteria.

-Set VR = Range("E1: E10") - this refers to the value of range variable which is the cells from E1 to E10.

• Set RC = Range("G4") - this refers to the cell location where we wanted the result to be found.
• CV = "Lily" - this is the criteria that we are looking at the cell range.
• RC = WorksheetFunction.CountIf(VR, CV) - this will instruct VBA the method we are about to use, which is the count if.

And the result after running the code:

Conclusion

This concludes the article about VBA Count If.