To apply COUNTIFS function in VBA Excel.
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.
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.
Post you may like