엑셀 SUBTOTAL 함수 완전 정복 – 숨겨진 행을 무시하고 정확한 합계 구하기

Table of Contents

엑셀 SUBTOTAL 함수 완전 정복 – 숨겨진 행을 무시하고 정확한 합계 구하기

엑셀에서 데이터를 정리하다 보면 SUM 함수로 계산한 값이 정확하지 않게 보이는 경험, 한 번쯤 해보셨을 겁니다. 특히 필터로 데이터를 일부 숨기거나 행을 직접 숨겼을 때 이런 문제가 자주 발생하죠.

이럴 때 필요한 것이 바로 SUBTOTAL 함수입니다.
이 글에서는 SUBTOTAL 함수의 기본 개념, 함수 번호의 의미, 실무 활용 예시, 그리고 SUM 함수와의 차이점까지 자세히 정리해 드리겠습니다.

SUBTOTAL 함수란?

SUBTOTAL 함수는 데이터의 합계, 평균, 개수 등 요약 작업을 할 때 사용되는 함수로,
특징은 숨겨진 행을 포함할지 여부를 선택할 수 있다는 점입니다.
또한, 필터를 적용하면 자동으로 숨겨진 값은 제외하고 계산합니다.

기본 구문

=SUBTOTAL(function_num, range1, [range2], …)

인수	설명
`function_num`	사용할 계산 방식의 번호 (합계, 평균, 개수 등)
`range1`	계산할 범위
`[range2]`	(선택) 추가 범위 지정 가능

function_num 번호표

번호	숨김 행 포함	숨김 행 무시	기능 이름	설명
1	✅	❌	AVERAGE	평균 구하기
2	✅	❌	COUNT	숫자 개수 세기
3	✅	❌	COUNTA	데이터 개수 세기
4	✅	❌	MAX	최대값
5	✅	❌	MIN	최소값
6	✅	❌	PRODUCT	곱 구하기
7	✅	❌	STDEV	표본 표준편차
8	✅	❌	STDEVP	전체 표준편차
9	✅	❌	SUM	합계 구하기
10	✅	❌	VAR	표본 분산
11	✅	❌	VARP	전체 분산
101~111	❌	✅	(동일 기능)	숨겨진 행 무시하고 계산

예시 :

=SUBTOTAL(9, A2:A100) → 합계(숨겨진 행 포함)
=SUBTOTAL(109, A2:A100) → 합계(숨겨진 행 무시)

이 표는 엑셀 SUBTOTAL 함수에서 사용되는 function_num 번호의 의미를 정리한 것입니다.
function_num은 어떤 계산을 할지 선택하는 번호이며, 숨겨진 행을 포함할지 여부도 번호에 따라 다르게 동작합니다.

1~11번은 숨겨진 행을 포함하여 계산합니다.
예: 필터로 숨긴 행도 계산에 포함됩니다.
101~111번은 숨겨진 행을 무시하고 계산합니다.
예: 필터나 직접 숨긴 행은 제외하고 계산됩니다.

번호 범위	특징
1~11	SUM처럼 모든 데이터 포함
101~111	필터 등으로 숨긴 행 제외

예를 들어,

은 SUM 기능을 수행하지만 숨겨진 행도 포함한 합계를 계산합니다.

반면,

은 SUM 기능을 수행하면서도 숨겨진 행을 무시하고 계산합니다.

💡 Tip:
엑셀에서 자동 필터를 자주 사용하는 경우, 100 이상 번호를 사용하면 시각적으로 보이는 데이터만 합산되므로 훨씬 유용합니다.

SUM 함수 vs SUBTOTAL 함수

구분	SUM 함수	SUBTOTAL 함수
숨겨진 행	포함	선택적 (번호로 제어)
필터 처리	무시	자동 반영 (숨긴 행 제외)
계산 방식	합계만 가능	평균, 개수, 최대값 등 11가지 요약 가능
소계 기능	없음	중간 소계로 활용 가능

엑셀에서 합계를 구할 때 가장 많이 쓰는 함수는 SUM입니다.
하지만 숨겨진 행을 제외하거나 필터로 걸러진 데이터만 합산하고 싶을 때는 SUM 함수만으로는 부족합니다.

이럴 때 활용할 수 있는 함수가 바로 SUBTOTAL 함수입니다.

비교 항목	SUM 함수 설명	SUBTOTAL 함수 설명
숨겨진 행 포함 여부	무조건 포함합니다. 숨긴 행도 모두 계산 대상에 포함됩니다.	function_num 값에 따라 포함 여부를 선택할 수 있습니다. (예: 9 vs 109)
필터 처리	필터로 숨긴 데이터도 계산되므로 정확한 결과를 기대하기 어렵습니다.	필터로 숨긴 데이터는 자동으로 제외되며, 실시간으로 변경 반영됩니다.
계산 기능	단순한 합계만 가능합니다.	평균, 개수, 최대값, 최소값 등 11가지 요약 계산을 지원합니다.
소계 기능	사용할 수 없습니다.	그룹별 소계를 계산할 수 있어 데이터 > 소계 기능과 함께 자주 사용됩니다.

💡 정리하면,

단순 합계 계산: SUM()
필터 반영, 소계 계산, 숨김 행 무시 등 조건부 합계: SUBTOTAL()

따라서 실무에서는 SUBTOTAL 함수가 더 유연하고 똑똑한 선택이 될 수 있습니다.
상황에 따라 적절히 선택해서 사용해 보세요!

실무 활용 예제

✔️ 필터를 적용한 합계 계산

=SUBTOTAL(109, B2:B100)

기능: 필터로 숨겨진 행은 제외하고 합계 계산

활용: 매출 데이터에서 특정 지역, 제품군만 필터링 후 합계 구할 때 유용

✔️ 소계와 전체 합계 자동 구분

제품	매출
A	100
A	200
B	300
B	400

활용: 데이터 > 소계 기능을 통해 각 그룹별 소계가 자동 계산되며, 이때 SUBTOTAL 함수가 내부적으로 사용됩니다.

✔️ 행 숨김을 무시한 합계 계산

=SUBTOTAL(9, A2:A20)      → 숨김 포함

=SUBTOTAL(109, A2:A20)    → 숨김 무시

설명: 직접 행을 숨긴 경우, 109처럼 100 이상의 번호를 사용해야 숨겨진 행을 제외한 합계를 계산할 수 있습니다.

주의할 점

SUBTOTAL 함수는 병합 셀에서는 예기치 않은 결과를 낼 수 있으니 주의하시기 바랍니다.
SUBTOTAL 함수 안에 다른 SUBTOTAL이 있는 경우는 중복 계산되지 않습니다.
SUBTOTAL은 테이블 형식에서도 자동으로 계산 행에 사용됩니다.

마무리하며 – SUM보다 똑똑한 요약 함수

엑셀에서 SUM 함수만 사용하다 보면 숨어 있는 데이터까지 계산되어 엉뚱한 결과가 나오는 경우가 있습니다.
그럴 때 SUBTOTAL 함수는 숨겨진 행을 제어하면서도 다양한 계산 기능을 제공하기 때문에 실무에서 매우 강력한 도구가 됩니다.

단순 합계뿐만 아니라, 필터와 소계를 자주 사용하는 데이터 분석 업무에서는 SUBTOTAL 함수 사용이 필수라고 할 수 있습니다.

🔗 SUBTOTAL 함수 더 알아보기

Microsoft 공식 SUBTOTAL 함수 설명 공식 가이드

엑셀 SUBTOTAL 함수 완전 정복 – 숨겨진 행을 무시하고 정확한 합계 구하기

엑셀 SUBTOTAL 함수 완전 정복 – 숨겨진 행을 무시하고 정확한 합계 구하기

SUBTOTAL 함수란?

기본 구문

function_num 번호표

SUM 함수 vs SUBTOTAL 함수