VBA SUMIF와 SUMIFS 함수

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

VBA에는 SUMIF 함수나 SUMIFS 함수에 해당하는 함수가 없으므로 사용자는 WorksheetFunction 객체를 사용하여 내장된 Excel 함수를 사용해야 합니다.

SUMIF WorksheetFunction

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

Sub TestSumIf()
Range("D10") = Application.WorksheetFunction.SumIf(Range("C2:C9"), 150, Range("D2:D9"))
End Sub

위의 프로시저는 C 열의 셀 값이 150인 행들에 대해서만  D2:D9 범위의 셀을 합산합니다.

변수에 SUMIF 결과 할당하기

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

Sub AssignSumIfVariable()
   Dim result as Double
'변수에 결과값을 할당합니다
   result = WorksheetFunction.SumIf(Range("C2:C9"), 150, Range("D2:D9"))
'결과값을 표시합니다
  MsgBox "판매코드가 150과 일치하는 결과의 합계는 " &  result & " 입니다."
End Sub

SUMIFS 사용하기

SUMIFS 함수는 SUMIF 워크시트 함수와 유사하지만 둘 이상의 기준을 설정할 수 있다는 점이 다릅니다. 아래 예제에서는 판매 코드가 150이고 매입가격이  2보다 큰 경우 판매 가격을 합산합니다. 이 수식에서는 합산할 셀 범위가 기준 앞에 있는 반면, SUMIF 함수에서는 셀 범위가 뒤에 있다는 것을 알 수 있습니다.

Sub MultipleSumIfs()
   Range("D10") = WorksheetFunction.SumIfs(Range("D2:D9"), Range("C2:C9"), 150, Range("E2:E9"), ">2")
End Sub

 

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

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

Sub TestSumIFRange()
   Dim rngCriteria As Range
   Dim rngSum as Range
'셀 범위를 변수에 할당합니다
   Set rngCriteria = Range("C2:C9")
   Set rngSum = Range("D2:D9")
'Range 객체를 사용하여 함수를 선언합니다
   Range("D10") = WorksheetFunction.SumIf(rngCriteria, 150, rngSum)
'Range 객체들을 초기화합니다
  Set rngCriteria = Nothing
  Set rngSum = Nothing
End Sub

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

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

Sub TestSumMultipleRanges() 
   Dim rngCriteria1 As Range 
   Dim rngCriteria2 as Range
   Dim rngSum as Range
'셀 범위를 변수에 할당합니다
   Set rngCriteria1= Range("C2:C9")
   Set rngCriteria2 = Range("E2:E9")   
   Set rngSum = Range("D2:D9")
'Range 객체를 사용하여 함수를 선언합니다 
Range("D10") = WorksheetFunction.SumIfs(rngSum, rngCriteria1, 150, rngCriteria2, ">2")
 'Range 객체들을 초기화합니다
  Set rngCriteria1 = Nothing 
  Set rngCriteria2 = Nothing
  Set rngSum = Nothing
End Sub

~보다 큰(연산자 >)를 사용하기 때문에 해당 기준은 쌍따움표 처리해야합니다.

SUMIF 수식

WorksheetFunction.SUMIF를 사용하여 워크시트의 범위에 합계를 추가하면 수식이 아닌 상수값이 반환됩니다. 즉, Excel의 수치가 변경되어도 WorksheetFunction에서 반환된 값은 변경되지 않습니다.

위의 예에서 프로시저는 C 열에서 판매코드가 150에 해당되는 D2:D9 범위의 값들을 더하고 그 결과를 D10에 넣었습니다. 수식 입력줄에서 볼 수 있듯이 이 결과는 수식이 아니라 상수입니다.

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

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

Formula 메서드

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

Sub TestSumIf()
  Range("D10").Formula = "=SUMIF(C2:C9,150,D2:D9)"
End Sub

 

FormulaR1C1 메서드

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

Sub TestSumIf()
   Range("D10").FormulaR1C1 = "=SUMIF(R[-8]C[-1]:R[-1]C[-1],150,R[-8]C:R[-1]C)"
End Sub

수식을 더 유연하게 만들고 싶다면 코드를 다음과 같이 수정할 수 있습니다:

Sub TestSumIf() 
   ActiveCell.FormulaR1C1 = "=SUMIF(R[-8]C[-1]:R[-1]C[-1],150,R[-8]C:R[-1]C)"
End Sub

위와 같이 코드를 사용하면 수식의 위치에 따라 참조하는 범위의 값도 변합니다. SUMIFS 함수 내부의 범위는 행(R)과 열(C) 구문을 사용하여 참조해야 합니다.

이 두 가지 방법을 사용하면 VBA를 사용하여 셀이나 범위에 Excel 수식을 사용할 수 있습니다.

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