Excel SUBTOTAL 함수: 필터/숨김에도 정확한 합계·개수·평균
보고서에서 필터를 걸면 SUM이 전체를 더해 틀리기 쉽습니다. 해결책은 Excel SUBTOTAL 함수. 필터/숨김 행을 자동으로 제외하고, 합계·개수·평균·최댓값까지 한 번에 처리합니다.
빠른 해결(Quick Fix)
- 데이터 범위를 선택하고 데이터 > 필터(Alt+A+T) 를 켭니다.
- 합계를 넣을 셀에
=SUBTOTAL(109, 금액범위)입력 → 필터/수동 숨김 행을 제외한 합계. - 표(테이블)라면
=SUBTOTAL(9, Table1[Amount])처럼 구조화 참조 사용. - 개수는
=SUBTOTAL(103, 범위)(COUNTA), 평균은=SUBTOTAL(101, 범위)(AVERAGE).
SUBTOTAL 핵심 개념 한 장 요약
함수 번호표(1–11 / 101–111)
| 기능 | 포함코드 | 숨김 제외코드 |
|---|---|---|
| AVERAGE | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCT | 6 | 106 |
| STDEV.S | 7 | 107 |
| STDEV.P | 8 | 108 |
| SUM | 9 | 109 |
| VAR.S | 10 | 110 |
| VAR.P | 11 | 111 |
규칙: 필터로 숨겨진 행은 항상 제외. 수동 숨김 행은 1–11은 포함, 101–111은 제외. 열 숨김은 제외하지 않음.
중첩 방지: SUBTOTAL 범위 안의 다른 SUBTOTAL은 자동 무시(이중 집계 방지).
왜 SUM이 틀리고 SUBTOTAL이 맞는가(원리)
SUM은 표시 상태와 무관하게 전체 합산. SUBTOTAL은 가시 행만 집계(필터 제외, 101–111 사용 시 수동 숨김도 제외)하여 보고서의 진짜 수치를 보장합니다.
실무 예제 1: 판매 리포트 필터 합계(9 vs 109)
샘플 데이터
| Date | Region | Category | Qty | Price | Amount |
|---|---|---|---|---|---|
| 2025-10-01 | Seoul | A | 2 | 10000 | 20000 |
| 2025-10-02 | Busan | B | 1 | 15000 | 15000 |
| 2025-10-03 | Seoul | A | 3 | 10000 | 30000 |
| 2025-10-04 | Daegu | B | 5 | 8000 | 40000 |
| 2025-10-05 | Seoul | B | 4 | 12000 | 48000 |
필터: Region=Seoul → 가시 Amount 합계=98,000
수식
=SUBTOTAL(9, F2:F6)
=SUBTOTAL(109, F2:F6)
실무 예제 2: 표(테이블)+구조화 참조
- Ctrl+T로 표 생성(이름 Sales).
- 합계:
=SUBTOTAL(9, Sales[Amount]) - 수동 숨김도 제외:
=SUBTOTAL(109, Sales[Amount])
실무 예제 3: 가시 항목 개수·평균·극값
- 숫자 개수:
=SUBTOTAL(102, Sales[Qty]) - 비어있지 않은 셀 개수:
=SUBTOTAL(103, Sales[Product]) - 평균:
=SUBTOTAL(101, Sales[Amount]) - 최댓값/최솟값:
=SUBTOTAL(104, Sales[Amount]),=SUBTOTAL(105, Sales[Amount])
대체 방법 & 주의사항
- AGGREGATE:
=AGGREGATE(9,7, Sales[Amount])등 옵션으로 숨김/오류 무시 가능. - 부분합 명령: 데이터 > 부분합으로 그룹별 소계 자동 삽입.
- 피벗테이블: 다차원 집계·부분합/총합 자동.
- 열 숨김 제외 불가: SUBTOTAL은 행 기준. 열 숨김 제외가 필요하면 설계를 세로 합산 중심으로 변경.
Troubleshooting
| 증상 | 원인 | 해결법 |
|---|---|---|
| 필터해도 값이 동일 | SUM 사용 | SUBTOTAL(9/109)로 교체 |
| 수동 숨김이 섞임 | 1–11 코드 사용 | 101–111 코드 사용 |
| 열 숨김 제외 안 됨 | 행 기준 함수 | 세로 집계 또는 AGGREGATE |
| 이중 집계 | SUM으로 상·하위 합계 병용 | 상위도 SUBTOTAL 사용(중첩 자동 무시) |
| #DIV/0! | 분모 0 | IF로 예외 처리 |
마무리 & 관련 글
SUBTOTAL로 “보이는 값만” 계산하면 보고서 신뢰도가 올라갑니다. 아래 글로 이어서 완성도를 높이세요.
- 엑셀 테이블(구조화 참조) 완벽 가이드
- 고급 필터 & 자동필터 실무 패턴
- SUMIFS와 보고서 자동화
- AGGREGATE 함수: 숨김/오류 무시 옵션
- 피벗테이블 부분합/총합 제대로 쓰기
더 읽기(공식 문서): SUBTOTAL 함수, AGGREGATE 함수, 부분합 명령