엑셀에서 글꼴 색상별로 합계 구하기 (효과적인 2가지 방법)

이 글에서는 엑셀에서 글꼴 색상별로 셀 합계를 구하는 방법을 소개합니다. 하나의 열에 다양한 글꼴 색상이 적용된 여러 셀이 있고, 색상별로 합계를 내야 하는 경우에 유용하게 활용할 수 있습니다. 아래 이미지는 이 글의 목표를 보여주는 예시입니다.

아래 다운로드 버튼을 통해 연습용 워크북을 받아 직접 따라 해볼 수 있습니다.

엑셀에서 글꼴 색상별로 합계 구하는 2가지 방법

1. SUBTOTAL 함수와 필터를 활용한 방법

하나의 열에 서로 다른 색상의 글꼴이 섞여 있는 데이터가 있다면, 이 방법이 가장 간단하고 효과적입니다. 아래 단계를 따라 해 보세요.

📌 진행 단계

• 먼저 C13 셀에 아래 수식을 입력합니다. 수식의 9는 SUM 함수를 의미하며, SUBTOTAL 함수가 지정된 범위의 합계를 반환하도록 해줍니다.

• 이제 데이터 범위 안의 아무 곳이나 클릭한 후 CTRL+SHIFT+L을 눌러 필터를 적용합니다. 그러면 아래 그림처럼 헤더 셀 우측 하단에 필터 버튼이 나타납니다.

• 필터 버튼을 클릭하고 색상 기준 필터를 선택해 원하는 색상으로 데이터를 걸러냅니다.

• 필터가 적용되면 아래와 같이 해당 색상 셀들의 합계만 표시됩니다.

함께 읽으면 좋은 글: 엑셀에서 필터링된 셀만 합산하는 방법 (5가지 방법)

관련 추천 글

• 엑셀에서 선택한 셀만 합산하는 방법 (4가지 쉬운 방법)
• 엑셀에서 열 끝까지 합계 구하기 (8가지 유용한 방법)
• 엑셀에서 열 합계 구하는 방법 (7가지 방법)
• [해결!] 엑셀 SUM 수식이 작동하지 않고 0만 반환될 때 (3가지 해결책)
• 엑셀에서 여러 행 합계 구하는 방법 (4가지 빠른 방법)

2. VBA 사용자 정의 함수(UDF)를 활용한 방법

데이터가 여러 행과 열에 걸쳐 분포해 있는 경우에는 앞서 소개한 필터 방법으로는 한계가 있습니다. 이럴 때는 사용자 정의 함수(User Defined Function, UDF)를 만들어 활용하는 것이 좋습니다. 아래 단계를 따라 해 보세요.

📌 진행 단계

• 먼저 통합 문서를 매크로 사용 통합 문서(.xlsm) 형식으로 저장합니다. 그다음 ALT+F11을 눌러 VBA 편집기를 열고, 메뉴에서 삽입 >> 모듈을 선택하여 새 빈 모듈을 생성합니다.

• 아래 코드를 복사합니다.

Function SUMBYFONTCOLOR(ref_color As Range, sum_range As Range)
Dim cell_color, sum_color As Long
Dim Cell As Range
Application.Volatile
sum_color = 0
cell_color = ref_color.Font.ColorIndex
For Each Cell In sum_range
If cell_color = Cell.Font.ColorIndex Then
sum_cell = WorksheetFunction.Sum(Cell, sum_cell)
End If
Next Cell
SUMBYFONTCOLOR = sum_cell
End Function

• 복사한 코드를 모듈 창에 붙여넣습니다.

• 워크시트로 돌아와서 I5:I9 셀에 원하는 색상 이름을 입력한 뒤, 각 셀의 글꼴 색상을 해당 색상으로 변경합니다.
• 그다음 J5 셀에 아래 수식을 입력하고, 채우기 핸들을 드래그하여 아래 셀에도 수식을 적용합니다.

완료되면 아래와 같이 글꼴 색상별 합계가 계산됩니다.👇

참고로, 범위 내 셀의 글꼴 색상을 변경해도 결과가 자동으로 갱신되지 않습니다. 이 경우 셀을 더블클릭한 후 다른 곳을 클릭하거나, CTRL+ALT+F9를 눌러 강제로 재계산해야 합니다.

• 매번 이런 작업을 하기 번거롭다면, 아래 코드를 추가로 복사하세요.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A:Z")) Is Nothing Then
ActiveSheet.Calculate
End If
End Sub

• VBA 편집기 창으로 돌아간 후, 왼쪽 프로젝트 탐색기에서 UDF 시트를 더블클릭합니다. 빈 코드 창이 열리면 복사한 코드를 붙여넣습니다.

이제 글꼴 색상을 변경한 뒤 다른 셀을 클릭하기만 하면 결과가 자동으로 갱신됩니다.

VBA 코드 상세 설명

Function SUMBYFONTCOLOR(ref_color As Range, sum_range As Range)
두 개의 인수를 받는 공개(Public) 함수입니다. ref_color 인수는 참조 셀의 글꼴 색상을 가져오며, sum_range 인수가 가리키는 범위에서 해당 색상과 일치하는 셀 값들을 합산합니다.

Dim cell_color, sum_color As Long / Dim Cell As Range
필요한 변수들을 선언하는 부분입니다.

Application.Volatile
사용자가 어떤 변경을 하든 엑셀이 재계산하도록 강제하는 코드입니다.

sum_color = 0
변수의 초기값을 설정합니다.

cell_color = ref_color.Font.ColorIndex
참조 셀(ref_color)의 글꼴 색상 인덱스를 cell_color 변수에 저장합니다.

For Each Cell In sum_range
sum_range 범위 내의 모든 셀을 순회하는 For 반복문입니다.

If cell_color = Cell.Font.ColorIndex Then
범위 내 셀의 글꼴 색상이 참조 셀의 글꼴 색상과 일치하는 경우에만 다음 코드가 실행됩니다.

sum_cell = WorksheetFunction.Sum(Cell, sum_cell)
SUM 함수를 호출하여 글꼴 색상이 일치하는 셀 값을 누적으로 더합니다.

SUMBYFONTCOLOR = sum_cell
계산된 합계를 저장하여 함수가 이 값을 반환하도록 합니다.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
특정 워크시트에서 선택 변경이 발생했을 때만 작동하는 비공개(Private) 프로시저입니다.

If Not Intersect(Target, Range("A:Z")) Is Nothing Then
사용자가 정의된 범위(A:Z) 내에서 변경을 했을 때만 다음 코드가 실행되도록 보장합니다.

ActiveSheet.Calculate
활성 워크시트의 모든 수식을 강제로 재계산하여 변경 사항이 결과에 반영되도록 합니다.

함께 읽으면 좋은 글: Excel VBA로 행 범위의 셀 합계 구하기 (6가지 쉬운 방법)

알아두면 좋은 점

• 글꼴 색상을 변경한 후 결과가 갱신되지 않으면, 셀을 더블클릭한 뒤 다른 곳을 클릭하거나 CTRL+ALT+F9를 눌러 재계산하세요.
• 선택 변경 이벤트 코드를 사용하는 경우, 색상 변경 후 반드시 정의된 범위(A:Z) 내부의 셀을 클릭해야 결과가 갱신됩니다.

마무리

지금까지 엑셀에서 글꼴 색상별로 셀 합계를 구하는 가장 효과적인 2가지 방법을 살펴보았습니다. SUBTOTAL 함수와 필터를 활용하는 간단한 방법과, VBA 사용자 정의 함수를 활용하는 고급 방법 중 상황에 맞는 방법을 선택해 활용해 보세요. 이 글이 도움이 되었다면 댓글로 의견이나 추가 질문을 남겨 주세요. 더 많은 엑셀 활용 팁은 ExcelDemy 블로그에서 확인하실 수 있습니다.

관련 글

• 엑셀에서 색상이 적용된 셀 합계 구하기 (4가지 방법)
• 엑셀 셀 합계: 연속, 임의, 조건별 방법 총정리
• VBA 없이 엑셀에서 색상별 셀 합계 구하기 (7가지 방법)
• 엑셀에서 숫자 더하기 (2가지 쉬운 방법)
• 엑셀에서 여러 행과 열 합계 구하기