VBA COUNTIF 및 COUNTIFS 함수

이 튜토리얼에서는 VBA에서 Excel COUNTIF 및 COUNTIFS 함수를 사용하는 방법을 보여줍니다.

VBA에는 Excel의 COUNTIF 함수나 COUNTIFS 함수에 해당하는 함수가 없습니다. 기능을 사용하려면 WorkSheetFunction 객체를 사용하여 Excel 함수를 호출해야 합니다.

COUNTIF WorksheetFunction

WorksheetFunction 객체를 사용하여 Excel의 함수 삽입 대화 상자에서 사용할 수 있는 대부분의 Excel 함수를 호출할 수 있습니다. COUNTIF 함수도 호출할 수 있습니다.

Sub TestCountIf()
   Range("D10") = Application.WorksheetFunction.CountIf(Range("D2:D9"), ">5")
End Sub

위의 프로시저는 D2:D9 범위에서 셀의 값이 5 이상인 개수를 셉니다. 구문에서 >5는 괄호 안에 있어야 합니다.

변수에 COUNTIF 함수의 결과값 할당하기

수식의 결과를 Excel 범위로 직접 다시 작성하는 대신 코드의 다른 곳에서 사용하고 싶을 수 있습니다. 이 경우 결과를 변수에 할당하여 나중에 코드에서 사용할 수 있습니다.

Sub AssignSumIfVariable()
   Dim result as Double
'변수에 함수의 결과값을 할당합니다
   result = Application.WorksheetFunction.CountIf(Range("D2:D9"), ">5")
'변수를 사용하여 결과를 표시합니다
  MsgBox "5보다 큰 셀의 개수는 " &  result & "개 입니다."
End Sub

COUNTIFS 함수 사용하기

COUNTIFS 함수는 COUNTIF WorksheetFunction과 유사하지만 둘 이상의 기준을 확인할 수 있습니다. 아래 예제에서 이 수식은 판매 가격이 6보다 크고 구입 가격이 5보다 큰 D2~D9의 셀 개수를 카운트합니다.

Sub UsingCountIfs()
   Range("D10") = WorksheetFunction.CountIfs(Range("C2:C9"), ">6", Range("E2:E9"), ">5")
End Sub

범위 객체로 COUNTIF 함수 사용하기

Range 객체에 셀 그룹을 할당하고 해당 Range 객체를 WorksheetFunction 객체와 함께 사용할 수 있습니다.

Sub TestCountIFRange()
   Dim rngCount as Range
'셀 범위를 변수에 할당합니다
   Set rngCount = Range("D2:D9")
'Range 객체를 함수에 사용합니다
   Range("D10") = WorksheetFunction.SUMIF(rngCount, ">5")
'Range 객체를 초기화합니다
  Set rngCount = Nothing
End Sub

여러 범위 객체로 COUNTIFS 함수 사용하기

마찬가지로 여러 범위 객체를 COUNTIFS에서 사용할 수 있습니다.

Sub TestCountMultipleRanges() 
   Dim rngCriteria1 As Range 
   Dim rngCriteria2 as Range

'셀의 범위를 변수에 할당합니다
   Set rngCriteria1= Range("D2:D9")
   Set rngCriteria2 = Range("E2:E10")
   
'Range 객체들을 함수에 사용합니다
  Range("D10") = WorksheetFunction.CountIfs(rngCriteria1, ">6", rngCriteria2, ">5")

'Range 객체를 초기화합니다
  Set rngCriteria1 = Nothing 
  Set rngCriteria2 = Nothing
End Sub

COUNTIF 함수

워크시트의 범위에 WorksheetFunction.COUNTIF를 사용하는 경우 Excel 수식이 아닌 정적인 값이 반환됩니다. 즉, Excel의 수치가 변경되어도 WorksheetFunction에서 반환된 값은 변경되지 않습니다.

위 예제에서는 프로시저가 D2:D9 범위에서 판매 가격이 6보다 큰 값을 가진 셀의 수를 계산하고 그 결과를 D10에 넣었습니다. 수식 입력줄에서 볼 수 있듯이 이 결과는 수식이 아니라 숫자입니다.

D2:D9 범위의 값이 변경되더라도 D10의 결과는 변경되지 않습니다.

VBA에서 WorksheetFunction.CountIf를 사용하는 대신 Formula 또는 FormulaR1C1 메서드를 사용하여 셀에 COUNTIF 수식을 적용할 수 있습니다.

Formula 메서드

Formula 메서드를 사용하면 아래와 같이 특정 셀 범위(예: D2:D9)를 지정할 수 있습니다.

Sub TestCountIf()
  Range("D10").FormulaR1C1 ="=COUNTIF(D2:D9, "">5"")"
End Sub

 

FormulaR1C1 메서드

FormulaR1C1 메서드는 정해진 셀 범위로 제한하지 않는다는 점에서 더 유용합니다. 아래 예제는 위와 동일한 답을 제공합니다.

Sub TestCountIf()
   Range("D10").FormulaR1C1 = "=COUNTIF(R[-8]C:R[-1]C,"">5"")"
End Sub

수식을 더욱 유연하게 만들기 위해 코드를 다음과 같이 수정할 수 있습니다:

Sub TestCountIf() 
   ActiveCell.FormulaR1C1 = "=COUNTIF(R[-8]C:R[-1]C,"">5"")"
End Sub

셀의 위치에 기반하여 함수에 사용되는 범위가 변합니다. 활성 셀을 기준으로 범위가 만들어지고 기준을 충족하는 셀을 계산하여 활성 셀(ActiveCell)에 수식이 입력됩니다. COUNTIF 함수 내부의 범위는 행(R) 및 열(C) 구문을 사용하여 참조해야 합니다.

이 두 가지 방법을 사용하면 VBA 내에서 셀에  Excel 수식을 사용할 수 있습니다.

이제 D10에 숫자 값 대신 수식이 표시됩니다.