엑셀 SUBTOTAL 함수로 필터 기준 합계·평균·개수 완벽 가이드 (101 vs 109 차이)
보고서를 만들다 보면 필터로 보이는 행만 합계를 내고 싶은데, 일반 SUM은 숨겨진 값까지 더해서 엉뚱한 결과가 나오죠. 이 글은 엑셀 SUBTOTAL 함수로 필터 기준 합계·평균·개수까지 한 번에 해결하는 방법을 다룹니다. 마지막엔 보이는 셀만 조건부 합계까지 확장하니, 끝까지 보면 실무가 확 달라집니다.
빠른 해결(Quick Fix)
- 데이터 범위를 선택하고 데이터 > 필터를 켭니다. (단축키:
Ctrl+Shift+L) - 원하는 조건으로 필터링합니다.
- 숨김행(수동 숨김)도 제외하려면 함수 번호 101–111을 씁니다.
합계:=SUBTOTAL(109, D2:D999)
평균:=SUBTOTAL(101, D2:D999)
개수(숫자):=SUBTOTAL(102, D2:D999) - 표(테이블, Ctrl+T)로 바꾸면 구조화 참조로 더 깔끔:
=SUBTOTAL(109, [매출]) - SUBTOTAL은 필터로 숨긴 행은 자동 제외합니다. 수동 숨김까지 빼려면 101–111을 쓰세요.
SUBTOTAL 핵심 개념: ‘필터 제외’와 ‘숨김행 처리’
필터 제외: SUBTOTAL은 자동 필터로 감춰진 행은 계산에서 제외합니다.
숨김행(수동 숨김) 처리: 번호 1–11은 수동 숨김 포함, 101–111은 수동 숨김 제외.
중복 방지: SUBTOTAL은 다른 SUBTOTAL 결과는 자동으로 무시합니다.
함수 번호 표(1–11 vs 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 |
SUBTOTAL이 다른 SUBTOTAL을 무시하는 이유
보고서 섹션별로 부분합을 내고 마지막에 전체합을 낼 때, 부분합을 다시 더해버리면 이중 합계가 됩니다. SUBTOTAL은 내부적으로 다른 SUBTOTAL 결과를 제외해서 이 문제를 자동으로 회피합니다.
실무 예제: 카테고리별 필터 매출 합계·평균·개수
샘플 데이터(복사해서 바로)
| 날짜 | 카테고리 | 상품 | 수량 | 단가 | 매출 |
|---|---|---|---|---|---|
| 2025-10-01 | Outer | 점퍼A | 5 | 49000 | =D2*E2 |
| 2025-10-01 | Top | 티셔츠B | 12 | 19000 | =D3*E3 |
| 2025-10-02 | Bottom | 팬츠C | 8 | 39000 | =D4*E4 |
| 2025-10-02 | Outer | 점퍼D | 3 | 69000 | =D5*E5 |
| 2025-10-03 | Top | 티셔츠E | 7 | 22000 | =D6*E6 |
| 2025-10-03 | Outer | 점퍼F | 2 | 129000 | =D7*E7 |
자동 필터 후 카테고리=Outer만 보이게 한 뒤:
- 합계:
=SUBTOTAL(109, F2:F999) - 평균:
=SUBTOTAL(101, F2:F999) - 개수(숫자):
=SUBTOTAL(102, F2:F999)
표(테이블, Ctrl+T) + 구조화 참조
범위를 표로 변환(이름: 매출표)하면 수식이 자동 확장됩니다.
- 합계:
=SUBTOTAL(109, 매출표[매출]) - 평균:
=SUBTOTAL(101, 매출표[매출]) - 개수(숫자):
=SUBTOTAL(102, 매출표[매출])
고급: ‘보이는 셀만’ 조건부 합계
SUMPRODUCT × SUBTOTAL × OFFSET
=SUMPRODUCT(
SUBTOTAL(109, OFFSET(F2, ROW(F2:F100)-ROW(F2), 0, 1)),
--(C2:C100="Outer")
)OFFSET으로 F열을 행 단위로 쪼개 SUBTOTAL에 전달 → 보이는 행일 때만 값이 더해지고, 조건과 곱해져 교집합만 집계됩니다.
동적 배열(FILTER) 대안
=SUM(
FILTER(F2:F100, (SUBTOTAL(103, OFFSET(F2, ROW(F2:F100)-ROW(F2), 0, 1))=1) * (C2:C100="Outer"))
)대체 방법/주의사항/체크리스트
- AGGREGATE로 오류 무시/숨김행 처리 옵션 확장
- 1–11 vs 101–111 정확히 선택
- COUNTA(3/103) 텍스트 처리 주의
- 표(테이블) 구조화 참조 권장
- OFFSET 휘발성: 성능 주의, 피벗 대안 검토
- 세로 집계 권장, 가로는 피벗/함수 조합 고려
Troubleshooting
| 증상 | 원인 | 해결법 |
|---|---|---|
| 필터했는데 합계가 안 맞음 | SUM 사용 | SUBTOTAL(109)로 교체 |
| 수동 숨김 행이 포함됨 | 1–11 번호 사용 | 101–111 번호로 교체 |
| 값이 2번 더해짐 | SUBTOTAL 중첩 | 상위 합계만 남기기 |
| 가시셀+조건부 합계 불가 | SUMIFS로 시도 | SUMPRODUCT×SUBTOTAL×OFFSET 사용 |
| 속도가 느림 | OFFSET 휘발성 | 피벗/Power Pivot 고려 |
마무리 & 관련 글
이제 엑셀 SUBTOTAL 함수만 알면 필터 기준 합계·평균·개수는 완벽합니다. 다음 글로 확장해보세요:
추가 참고(공식 문서) — Microsoft: SUBTOTAL 함수, Microsoft: AGGREGATE 함수