이번 포스팅에서는 엑셀(Excel) 필터 기능의 진정한 파트너, 화면에 보이는 데이터만 정확하게 계산해주는 엑셀 SUBTOTAL 함수 사용법과 SUM 함수와의 결정적인 차이점에 대해 이해할 수 있도록 자세히 정리해보았습니다.
참고로 아래 글은 아래 테스트 날짜와 기기, 앱 버전에서 작동 여부를 직접 확인 완료 하였습니다.
테스트 날짜 : 25년 10월 15일
윈도우 버전 : Windows 11 Home / 24H2
사용기기 : Microsoft Surface Pro 11th Edition
프로그램명/버전 : Microsoft 365 / Excel / 버전 2508
엑셀로 방대한 양의 데이터를 다룰 때, ‘필터’ 기능은 원하는 정보만 걸러서 보는 데 필수적인 도구입니다. 하지만 많은 실무자들이 필터를 적용한 후 큰 혼란에 빠지는 경험을 합니다. 예를 들어, 전체 판매 데이터에서 ‘사과’ 품목만 필터링하여 화면에 보이게 한 후, 판매량 합계를 구하기 위해 SUM 함수를 사용했더니, 필터와 상관없이 여전히 전체 품목의 합계가 표시되는 상황입니다.
이것이 바로 SUM 함수의 명확한 한계입니다. SUM 함수는 숨겨지거나 필터링된 행을 구분하지 않고, 지정된 범위 안의 모든 숫자를 무조건 더합니다. 바로 이 문제를 완벽하게 해결하기 위해 탄생한 함수가 바로 엑셀 SUBTOTAL 함수입니다.
엑셀 SUBTOTAL 함수는 ‘부분합(Subtotal)’이라는 이름 그대로, 전체 데이터 중 특정 ‘부분’에 대한 계산을 수행하는 데 특화된 강력한 함수입니다. 이 함수의 가장 큰 특징은 화면에 보이는, 즉 필터링된 데이터만을 대상으로 계산할 수 있다는 점입니다. 또한, 단순 합계(SUM)뿐만 아니라 평균(AVERAGE), 개수(COUNT), 최대값(MAX) 등 11가지의 다양한 통계 기능을 하나의 함수로 모두 처리할 수 있는 ‘만능 함수’이기도 합니다.
(개인 노하우) 실무에서 필터 기능이 적용된 표를 다룬다면, 합계는 무조건 SUM 대신 엑셀 SUBTOTAL 함수를 사용하는 습관을 들이는 것이 좋습니다. 이는 데이터 분석의 정확성을 보장하고, 조건이 바뀔 때마다 수식을 수정할 필요 없이 동적으로 결과값이 업데이트되는 최고의 효율성을 제공하기 때문입니다.
이번 포스팅에서는 이러한 엑셀 SUBTOTAL(부분합) 함수에 대해 구체적으로 알아보고, SUM 함수와 어떤 점에서 다른지, 그리고 실제로 어떻게 사용하면 되는지 예시와 함께 정리해보겠습니다.
앞선 포스팅에서 정리한 엑셀 관련 글은 아래와 같습니다.
Excel 엑셀 랜덤 함수 rand, randbetween 사용법 3분 정리
엑셀 함수 안됨 계산 인식 오류 10초 해결 꿀팁
자동 만년 Excel 엑셀 달력 만들기 1분 해결 방법
Excel 엑셀 줄바꿈 함수 2개 사용법
Excel 엑셀 날짜 주차 변환하는 10초 해결 방법
엑셀 SUBTOTAL 함수란? SUM과의 차이점
엑셀 SUBTOTAL 함수는 엑셀에서 부분합을 구할 때 사용하는 함수로, 표나 데이터 범위에서 그룹별 합계를 빠르게 계산하거나, 숨겨진 행을 계산에서 제외하고 싶을 때 유용하게 사용할 수 있습니다.
또한, 단순 합계뿐만 아니라 평균, 개수, 최대값, 최소값 등 여러 통계 함수의 기능을 통합적으로 제공하는 것이 특징입니다.
엑셀에서 SUBTOTAL 함수는 다음과 같이 표기합니다.
SUBTOTAL(function_num, ref1, [ref2], …)
- function_num: 어떤 통계 계산을 할 것인지 지정하는 숫자(1부터 11, 101부터 111까지)가 들어갑니다. 예를 들어 1은 평균(AVERAGE), 2는 개수(COUNT), 9는 합계(SUM)에 해당합니다.
- ref1, ref2, …: 부분합을 적용할 범위를 지정합니다.
엑셀 SUBTOTAL 함수 언제 사용할까요?
- 필터 적용 후 보이는 값만 계산하고 싶을 때
필터를 걸어서 특정 조건에 해당하는 행만 남겨두었는데, 일반적인 SUM 함수로 합계를 구하면 숨겨진 행까지 모두 포함되어 계산될 때가 있습니다. 이럴 때 엑셀 SUBTOTAL 함수를 사용하면 필터링된 데이터에 대해서만 합계, 평균 등을 낼 수 있습니다. - 숨겨진 행(행 높이를 수동으로 숨김) 등을 제외하고 계산하고 싶을 때
데이터를 정리하다 보면, 불필요한 행을 숨기고 싶은 경우가 있습니다. 이 상태에서 합계를 구했을 때 숨긴 행의 데이터가 합산에 포함되지 않도록 하고 싶다면 엑셀 SUBTOTAL 함수를 이용합니다. - 다양한 통계 기능을 단일 함수로 간편하게 적용하고 싶을 때
엑셀 SUBTOTAL 함수는 합계(SUM)뿐만 아니라 평균(AVERAGE), 개수(COUNT), 최대(MAX), 최소(MIN), 표준편차(STDEV) 등 다양한 기능을 지원합니다. 하나의 함수로 여러 가지 통계 작업을 손쉽게 구현할 수 있습니다.
SUM과 무엇이 다를까요?
- 숨겨진 행 처리 여부
SUM 함수는 기본적으로 범위에 포함된 모든 셀을 합산합니다. 만약 일부 데이터를 숨기거나 필터링해도 그 값들이 완전히 제거되는 것이 아니기 때문에 계산에서 제외되지 않습니다.
반면에 SUBTOTAL 함수는 설정에 따라 숨겨진 행이나 필터링된 데이터를 제외하고 계산할 수 있습니다. - 다른 통계 연산 가능
SUM 함수는 합계만을 구하지만, SUBTOTAL 함수는 지정 값(function_num)에 따라 합계 외에도 평균, 개수 등 여러 결과를 얻을 수 있습니다.
엑셀 SUBTOTAL 사용 방법
이제 실제 예시를 통해 엑셀 SUBTOTAL 함수를 어떻게 사용하는지 구체적으로 살펴보겠습니다. 가령 아래와 같이 가상의 판매 데이터 테이블을 하나 만들어보겠습니다. 시트의 A열부터 C열까지 아래와 같은 데이터가 있다고 가정합니다.
| A열(상품명) | B열(판매량) | C열(판매단가) |
|---|---|---|
| 사과 | 10 | 1,000 |
| 바나나 | 20 | 2,000 |
| 바나나 | 5 | 1,800 |
| 배 | 10 | 3,000 |
| 사과 | 8 | 1,200 |
| 사과 | 12 | 1,100 |
| 배 | 3 | 2,800 |
| 바나나 | 15 | 2,500 |
(1) 총 판매량 합계 – 일반 SUM
먼저 C열(판매단가)은 무시하고, B열(판매량)에 대해서 일반적인 합계를 구한다고 해봅시다. 가장 간단한 방식은 예를 들어 B9 셀에 다음과 같이 입력하는 것입니다.
=SUM(B2:B9)이렇게 하면 판매량의 총합(10+20+5+10+8+12+3+15=83)이 계산됩니다. 그러나 이 합계는 숨기거나 필터링을 했을 때도 변동 없이 83을 계속 보여줄 것입니다.
(2) SUBTOTAL 함수로 합계 구하기
동일한 위치(B9 셀)에 엑셀 SUBTOTAL 함수를 활용하여 합계를 구해보겠습니다. 합계를 구할 때의 function_num값은 9이므로, 다음과 같이 입력합니다.
=SUBTOTAL(9, B2:B9)결과는 마찬가지로 83이 됩니다. 하지만 이 함수는 몇 가지 이점이 있습니다. 예를 들어 특정 상품만 필터링했을 때, 그 상품들의 판매량만 자동으로 계산해주거나, 숨겨진 행을 자동으로 제외하여 계산할 수 있다는 것입니다.
(3) 필터 적용 후 엑셀 SUBTOTAL 함수 확인하기
실제 엑셀 SUBTOTAL 함수의 활용도를 확인하기 위해 데이터를 필터링해보겠습니다.
- 먼저 A1:C1 영역을 선택한 뒤 상단 메뉴에서 데이터 → 필터를 적용합니다.
- A열(상품명) 셀에서 드롭다운 화살표를 클릭하여 “사과”만 체크합니다. (또는 아래 이미지와 같이 사과만 나타나도록 필터 설정을 합니다.)
그러면 테이블에서 사과 행(A열이 ‘사과’)만 화면에 나타납니다. 이 상태에서 B11 셀의 SUBTOTAL 함수 결과를 보면, 자동으로 사과의 판매량만 합산된 결과가 표시됩니다. 예시 데이터를 기준으로 하면 사과는 (10 + 8 + 12) = 30이므로, 엑셀 SUBTOTAL 함수는 숨겨진 행(바나나, 배)을 제외한 30을 표시하게 됩니다.
만약 SUM 함수를 사용했다면 아무리 화면에서 사과만 남도록 필터링했어도 여전히 83이 표시될 것입니다. 이것이 SUM과 SUBTOTAL의 가장 큰 차이점 중 하나입니다.
(4) 숨기기(행 높이 숨김) 후 SUBTOTAL 함수 확인하기
필터가 아닌 다른 방식으로 행을 숨겨보겠습니다. 예를 들어 B4 셀에 해당하는 배(판매량 10) 행 전체를 선택하고 우클릭 → 숨기기를 해봅니다. 필터가 아닌 일반 숨기기 작업을 한 것입니다. 마찬가지로 B11 셀의 SUBTOTAL 함수 결과가 어떻게 바뀌는지 확인해봅니다.
- 숨기기 전 SUBTOTAL = 83
- B4 행을 숨긴 후 결과를 확인하면, 숨겨진 행을 제외한 나머지 판매량만 더해 표시됩니다.
단, SUBTOTAL 함수의 function_num 선택에 따라 숨긴 행 포함 여부가 달라질 수 있습니다. 예를 들어 9 대신 109를 사용하면 숨겨진 행을 제외하고 계산할 수 있고, 9를 사용하면 수동으로 숨긴 행도 계산에 포함될 수 있습니다.
아래는 주로 사용되는 SUBTOTAL의 function_num 예시입니다.
| function_num | 통계 함수 | 숨김 처리 방식 |
|---|---|---|
| 1 또는 101 | AVERAGE | 평균 |
| 2 또는 102 | COUNT | 개수(숫자) |
| 3 또는 103 | COUNTA | 개수(숫자+문자) |
| 4 또는 104 | MAX | 최대값 |
| 5 또는 105 | MIN | 최소값 |
| 9 또는 109 | SUM | 합계 |
- 1~11: 필터로 숨겨진 행은 제외하지만, 수동으로 숨긴 행(행 높이를 0으로 숨김 등)은 포함하여 계산
- 101~111: 필터로 숨겨진 행 + 수동으로 숨긴 행 모두 제외하여 계산
이 부분은 본인이 원하는 목적에 맞춰서 수치를 선택하면 되겠습니다. 만약 “필터로 숨긴 행 + 수동 숨긴 행 전부 계산에서 빼겠다”고 하면 109, “필터로 숨긴 행만 빼고 수동 숨긴 행은 포함하겠다”고 하면 9를 사용하면 됩니다.
SUBTOTAL 함수를 이용한 빠른 통계
SUBTOTAL 함수를 합계로만 사용할 수도 있지만, 평균이나 개수 등을 구할 때도 동일한 형태로 사용할 수 있습니다. 예를 들어 “이 테이블에서 필터를 적용했을 때의 평균 판매량만 알고 싶다”라고 한다면:
=SUBTOTAL(1, B2:B8)이처럼 function_num 자리에 1을 넣으면 AVERAGE(평균) 함수가 적용되어, 필터로 숨겨진 행을 제외한 나머지 행들의 평균이 계산됩니다.
참고로 마이크로소프트 지원 페이지에서도 엑셀 SUBTOTAL 함수 사용법에 대해 가볍게 안내하고 있습니다.
이번 글에서는 엑셀에서 자주 쓰이지만 은근히 헷갈릴 수 있는 엑셀 SUBTOTAL 함수에 대해 살펴보았습니다. 간단히 정리하자면 다음과 같습니다.
- SUBTOTAL 함수는 합계뿐만 아니라 평균, 개수, 최대/최소 등 여러 통계 함수를 통합적으로 제공하는 함수입니다.
- 숨겨진 행이나 필터링된 결과를 계산에서 배제하거나 포함할 수 있어, 대량의 데이터에서 필요한 부분만 계산할 때 매우 유용합니다.
- SUM 함수와 달리, 필터 또는 수동 숨김 행을 무시하여 실제 화면에 보이는(또는 조건에 맞는) 데이터만 계산할 수 있습니다.
- function_num의 범위를 잘 이해하면, 필터로 숨긴 행만 빼거나 수동 숨김 행까지 모두 빼는 등 원하는 방식으로 부분합을 구할 수 있습니다.
엑셀 작업에서 ‘합계’라는 기능은 가장 기초적이지만 동시에 가장 자주 사용하는 기능입니다. 필요에 따라 SUBTOTAL을 함께 활용하면 필터링된 데이터나 숨겨진 행을 제외하여 정확한 통계를 손쉽게 낼 수 있습니다.
앞선 포스팅에서 정리한 엑셀 관련 글은 아래와 같습니다.
[Excel 꿀팁] 엑셀 pdf 저장 변환 3가지 방법 3초 해결 (페이지 잘림 해결)
Excel 엑셀 틀 고정 인쇄 방법 1분 해결 (행열 틀고정 반복 프린트 설정법)
작성자 : Sean Woo
[전공] HCI(인간-컴퓨터 상호작용)·UX(사용자 경험 연구)
[업무] 신기술 기반 사업 전략·기술 기획·디지털 트랜스포메이션 업무
ITmanual.net에서는 실생활, 업무를 진행하면서 느꼈던,
스마트폰(아이폰·갤럭시)·운영체제·각종 앱/프로그램·IT기기 활용 노하우를 실생활 중심 IT 매뉴얼로 정리합니다.