엑셀 배경색이 같은 셀 값 합계 구하는 방법

엑셀에서 같은 조건을 가진 셀의 개수를 구하는 함수는 SUMIF가 있습니다. 하지만 SUMIF 함수에 조건을 셀 배경색으로 설정할 수는 없습니다. 

VBA를 이용하면 배경색이 같은 셀의 값의 합을 구할 수 있는 함수를 만들어 사용이 가능하게 됩니다.  VBA라고 하면 어렵게 다가올 수도 있지만 Ctrl + C 와 Ctrl + V만 할 줄알면 만들 수 있으므로 쉽게 따라 할 수 있습니다. 

배경색 같은 셀 값 합계 함수 만들기

Excel 을 열고 [Alt + F11] 키를 눌러 Visual Basic 편집기 창을 열어 삽입(I) -> 모듈(M)을 차례로 선택하여 모듈(코드) 입력창을 엽니다.

코드 입력창에 아래의 내용을 복사해서 붙여 넣기 합니다. 블로그에서 바로 복사가 안되면 아래의 TXT 파일을 다운받아 복사하면 됩니다.

Function Sumbackcolor(range_data As Range, criteria As Range) As Double
Applicaion.Volatile
    Dim datax As Range
    Dim xcolor As Double
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
    If datax.Interior.ColorIndex = xcolor Then
        Sumbackcolor = Sumbackcolor + datax
    End If
Next datax
End Function

Excel_SumBackColor_Code.txt

0.00MB

이제 Visual Basic 편집기를 닫으면 배경색이 같은 셀의 값의 합을 구하는 SumBackColor 함수를 사용할 수 있습니다. 

 

배경색 같은 셀의 값 합계 구하는 함수 사용하기

사용하는 방법은 일반적인 엑셀에서 사용하는 다른 함수와 같은데 위의 코드에서 보면 

첫줄의 Function Sumbackcolor에서 함수의 이름을 SumBackColor로 정의해 주었기 때문에 아래와 같이 =Sum 로 검색만 해도 엑셀에서 원래 존재하던 함수처럼 SumBackColor라는 함수가 보이게 됩니다.

*이 함수명을 바꾸고 싶다면 코드에서 SumBackColor 3개 모두 원하는 이름으로 바꾸어 사용할 수가 있습니다.

아래와 같이 배경색이 여러개인 셀이 있고 배경색이 같은 셀의 값의 합을 구해야 하는 작업을 해야 한다면

G3셀에 =SumBackColor(B2:D15,G2)와 같이 입력한 뒤에 G2셀에 왼쪽 표에서 원하는 배경색을 [서식 복사]를 이용하여 붙여 넣기 하면 배경색 별로 셀 값의 합을 구할 수 있습니다. 

VBA로 생성한 함수를 이 파일에서 계속 사용하고 싶다면 엑셀 파일을 저장할 때 아래와 같이 [파일 형식(T)]에서 Excel 매크로 사용 서식 파일(*.Xltm)로 바꾸어 저장하면 이 파일에서는 항상 SumBackColor 함수를 사용할 수 있게 됩니다.