엑셀 SUBTOTAL 함수 사용법 — 필터링된 데이터만 정확히 합산하기

엑셀에서 자동 필터를 걸어 일부 행만 화면에 남기고 SUM 함수로 합계를 내면, 숨겨진 행까지 포함된 '전체 합계'가 나와 당황하는 경우가 많습니다. SUBTOTAL 함수는 이런 상황을 위해 만들어진 함수로, 필터로 숨겨진 행을 제외하고 '화면에 실제로 보이는 값'만 계산해 줍니다.

기본 구조

=SUBTOTAL(기능번호, 범위)

기능번호는 합계·평균·개수·최대값 등 어떤 계산을 할지 정하는 코드입니다. 같은 '합계' 기능이라도 두 가지 번호가 있는데, 하나는 숨긴 행도 포함하고 하나는 제외합니다.

자주 쓰는 기능번호표

• •1 또는 101 = AVERAGE (평균)
• •2 또는 102 = COUNT (숫자 개수)
• •3 또는 103 = COUNTA (비어있지 않은 셀 개수)
• •4 또는 104 = MAX (최대값)
• •5 또는 105 = MIN (최소값)
• •9 또는 109 = SUM (합계)
• •1~11번대: 수동으로 숨긴 행은 포함하고 '필터로' 숨긴 행만 제외
• •101~111번대: 수동으로 숨긴 행과 필터로 숨긴 행을 모두 제외 (실무에서는 이 범위를 주로 사용)

실무 예시 — 필터 걸어도 정확한 합계

B2:B100에 매출 데이터가 있고, 이 범위에 자동 필터를 걸어 특정 조건만 남긴 상태에서 화면에 보이는 매출만 합산하려면 다음과 같이 씁니다.

=SUBTOTAL(109, B2:B100)

필터 조건을 바꿔 화면에 보이는 행이 달라지면, 이 수식 결과도 자동으로 다시 계산되어 항상 '현재 보이는 데이터'만의 정확한 합계를 보여줍니다. 반면 =SUM(B2:B100)은 필터와 상관없이 항상 전체 행의 합계를 그대로 보여줍니다.

그룹별 소계와 전체 총계 함께 만들기

SUBTOTAL의 중요한 특징 중 하나는 'SUBTOTAL 결과를 다시 SUBTOTAL로 계산하면 이미 계산된 소계는 중복으로 더하지 않는다'는 점입니다. 이 덕분에 지역별로 소계를 SUBTOTAL로 여러 번 넣어도, 맨 아래 총계 셀에서 전체 범위를 SUBTOTAL로 한 번 더 감싸면 소계가 중복 합산되지 않습니다.

=SUBTOTAL(109, B2:B15)

데이터 탭의 '부분합' 기능을 사용하면 이 SUBTOTAL 수식이 그룹마다 자동으로 삽입되며, 개요(윤곽) 기능으로 소계·총계만 접어서 볼 수도 있어 보고서 요약에 매우 유용합니다.

필터링된 데이터의 개수 세기

필터를 걸었을 때 '현재 몇 건이 조회되었는지'를 표시하고 싶다면 COUNTA 기능번호인 103을 씁니다.

=SUBTOTAL(103, A2:A100)

이 수식을 표 위쪽에 항상 보이게 배치해 두면, 필터 조건을 바꿀 때마다 '현재 조회된 건수'가 자동으로 갱신되어 대시보드처럼 활용할 수 있습니다.

자주 나는 오류와 주의사항

• •행 자체를 삭제(우클릭 → 행 숨기기 아닌 삭제)하면 SUBTOTAL·SUM 결과가 똑같이 달라집니다. SUBTOTAL의 효과는 '숨긴 행'에서만 발휘됩니다.
• •101~111 기능번호는 필터와 '수동 숨기기' 모두 제외하지만, 1~11 기능번호는 필터로 숨긴 행만 제외하고 수동으로 숨긴 행(마우스 우클릭 숨기기)은 그대로 포함합니다. 두 번호대를 혼동하지 않도록 주의하세요.
• •SUBTOTAL은 범위 안에 다른 SUBTOTAL 수식이 있으면 그 결과를 자동으로 무시(중복 제거)합니다. 이 특성 때문에 소계·총계 구조를 만들 때는 오히려 유리하지만, 의도치 않게 특정 셀이 계산에서 빠질 수도 있으니 결과를 항상 검산하세요.
• •SUBTOTAL은 피벗테이블 내부나 3차원 참조(여러 시트 동시 참조)에서는 정상 작동하지 않을 수 있습니다.