Apply Countifs function in VBA Excel

countifs function vba excel

Objective

To apply COUNTIFS function in VBA Excel.

Approach

We use Countifs function when we have 2 or more than 2 condition, based on which we want to count the number of values.

In the case below we had 2 columns SID_1 and SID_2,we had data from row 2 to 6.So here we have 2 conditions, according to first condition how many cells had value as “10” in range A2 to A6.Then in the second condition, we wanted to find how many cells had value as “A” in range B2 to B6.

Code

Sub ApplyCountifsFunction()

Range_1 = "A2:A6"
Range_2 = "B2:B6"
Citeria_1 = Chr(34) & "10" & Chr(34)
Citeria_2 = Chr(34) & "A" & Chr(34)

Range("D2").Formula = "=countifs(" & Range_1 & "," & Citeria_1 & "," & Range_2 & "," & Citeria_2 & ")"

End Sub

 

So Countifs will check for both the conditions, it will check that which rows matched both the conditions. So, if we look at the results in the image below, we can see that there were 2 rows which had “10” in column A and string “A” in column. Hence, we can see that result shown in cell D2 is 2.

 

countifs function vba excel

 

Post you may like

Select multiple rows and columns – VBA Excel