엑셀로 방대한 데이터를 관리하다 보면 '필터(Filter)' 기능을 자주 사용하게 됩니다. 예를 들어 수천 건의 판매 내역 중에서 '서울' 지역만 보고 싶거나, '삼성전자' 관련 항목만 추려내고 싶을 때 말이죠.
그런데 여기서 초보자분들이 자주 겪는 치명적인 실수가 있습니다. 필터를 걸어서 데이터를 추려낸 뒤, 맨 아래에 합계를 확인해 보면 숫자가 전혀 바뀌지 않고 전체 합계가 그대로 떠 있는 현상입니다.
"어? 나는 분명히 서울 지역만 필터링했는데, 왜 전국 매출 합계가 나오지?"
이것은 여러분이 SUM 함수를 사용했기 때문입니다. SUM 함수는 눈에 보이지 않는(숨겨진) 데이터까지 모조리 더해버리는 성질이 있거든요. 오늘은 필터링된 화면에 보이는 데이터만 똑똑하게 계산해 주는 SUBTOTAL 함수에 대해 알아보고, SUM 함수와 무엇이 다른지 완벽하게 비교해 드리겠습니다.
1. 왜 SUM 함수는 필터를 무시할까?
먼저 문제의 원인부터 짚고 넘어가겠습니다. 우리가 흔히 쓰는 =SUM(범위) 함수는 엑셀에게 이렇게 명령하는 것과 같습니다.
"야, 엑셀아! 내 눈에 보이든 말든 상관하지 말고, 지정한 범위 안에 있는 숫자는 무조건 다 더해!"
즉, 필터를 걸어서 100개 행 중 90개를 숨기고 10개만 화면에 남겨도, SUM 함수는 뒤에 숨어있는 90개의 값까지 모두 합산합니다. 그래서 우리가 원하는 '보이는 데이터의 합계'를 구할 수 없는 것이죠.
2. SUBTOTAL 함수란? (정의와 특징)
SUBTOTAL은 말 그대로 '부분(Sub) 합계(Total)'를 구하는 함수입니다. 하지만 단순히 합계만 구하는 것이 아니라, 사용자가 지정하는 옵션에 따라 평균, 개수, 최댓값 등 다양한 계산을 수행할 수 있는 '만능 함수'입니다.
이 함수의 가장 강력한 특징은 바로 '보이는 셀만 계산한다'는 점입니다. 필터링을 통해 행이 숨겨지면, SUBTOTAL 함수는 그 행을 계산에서 제외합니다. 마치 사용자의 마음을 읽는 것처럼 말이죠.
3. SUBTOTAL 함수 사용법 (문법과 번호)
SUBTOTAL 함수는 사용하는 방법이 조금 독특합니다. 단순히 범위를 잡는 게 아니라, "어떤 계산을 할 것인가?"를 숫자로 먼저 지정해줘야 합니다.
=SUBTOTAL(함수 번호, 계산할 범위)
핵심: 함수 번호 (Function_num) 기억하기
첫 번째 인수에 어떤 숫자를 넣느냐에 따라 기능이 바뀝니다. 다 외울 필요는 없지만, 실무에서 가장 많이 쓰는 3가지 번호는 꼭 기억하세요.
| 번호 | 기능 (원래 함수) | 사용 예시 |
|---|---|---|
| 9 | 합계 (SUM) | 필터링된 매출액의 총합 |
| 1 | 평균 (AVERAGE) | 보이는 항목들의 평균 단가 |
| 3 | 개수 (COUNTA) | 필터링된 인원수 세기 |
즉, 합계를 구하고 싶다면 9번을 쓰면 됩니다.=SUBTOTAL(9, C2:C100)이라고 입력하면, C2에서 C100 사이의 값 중 '현재 화면에 보이는 값'만 더하게 됩니다.
4. 실전 예제: 매출 현황표에서 지역별 합계 구하기
실제 상황을 가정해 보겠습니다. 아래와 같은 매출 데이터가 있습니다.
- A열: 날짜
- B열: 지역 (서울, 부산, 대구...)
- C열: 매출액
우리의 목표는 '지역 필터'를 바꿀 때마다 자동으로 매출액 합계가 변하도록 만드는 것입니다.
Step 1: 데이터 맨 위에 합계 칸 만들기
보통 합계는 표의 맨 아래에 만들지만, 필터를 걸면 합계 행도 같이 숨겨질 수 있습니다. 그래서 SUBTOTAL을 쓸 때는 표의 상단(제목 바로 위나 아래)에 결과 칸을 만드는 것이 고수들의 팁입니다.
Step 2: 수식 입력하기
매출액 합계를 표시할 셀에 다음과 같이 입력합니다.
(여기서 9는 SUM 기능을 뜻하고, C5:C1000은 매출액 데이터 범위입니다.)
Step 3: 필터 적용 및 결과 확인
이제 데이터 헤더를 선택하고 단축키 Ctrl + Shift + L을 눌러 필터를 적용합니다. 그리고 '지역'에서 '서울'만 선택해 보세요.
놀랍게도 전체 합계였던 숫자가 순식간에 '서울 지역만의 합계'로 바뀝니다. 다시 '부산'을 추가 선택하면 두 지역의 합계로 실시간 업데이트됩니다.
5. 심화: 번호 9번과 109번의 차이 (숨기기 vs 필터)
함수 마법사를 켜보면 합계 기능이 9번(SUM)도 있고 109번(SUM)도 있어서 헷갈리실 겁니다. 이 둘의 차이는 "사용자가 수동으로 행을 숨겼을 때(우클릭-숨기기)" 어떻게 반응하느냐입니다.
| 구분 | 필터로 숨겨진 행 | 수동으로 숨긴 행 (우클릭-숨기기) |
|---|---|---|
| 번호 9 | 계산 제외 (O) | 포함해서 계산 (X) |
| 번호 109 | 계산 제외 (O) | 계산 제외 (O) |
결론만 말씀드리면:
일반적인 '필터' 작업만 한다면 9번을 써도 충분합니다. 하지만 행을 수동으로 숨기는 작업까지 반영해서 정말 눈에 보이는 것만 더하고 싶다면 109번을 쓰는 것이 안전합니다.
6. 보너스 꿀팁: 필터 걸어도 깨지지 않는 '순번' 만들기
데이터 왼쪽에 1, 2, 3, 4... 순번(No)을 매겨두었는데, 필터를 걸면 순번이 1, 5, 8, 12...처럼 뒤죽박죽이 되어 보기 싫은 경우가 있습니다.
이때 SUBTOTAL 함수를 응용하면 필터를 걸어도 항상 1, 2, 3, 4로 예쁘게 다시 정렬되는 '동적 순번'을 만들 수 있습니다.
(여기서 3번은 COUNTA(개수) 기능이며, 범위의 앞부분만 절대참조($)로 고정하는 것이 핵심입니다.)
이 수식을 아래로 쭉 드래그해 놓으면, 필터링 결과에 따라 항상 위에서부터 1, 2, 3... 번호가 새로 매겨집니다. 보고서를 상사에게 제출할 때 "오, 엑셀 좀 하는데?"라는 칭찬을 들을 수 있는 킬러 기능입니다.
7. 마무리하며
오늘은 SUM 함수의 한계를 뛰어넘어, 필터링된 데이터만 똑똑하게 계산해 주는 SUBTOTAL 함수에 대해 알아보았습니다.
핵심 요약:
- SUM은 숨겨진 데이터도 다 더하지만, SUBTOTAL은 보이는 것만 더한다.
- 합계를 구할 때 함수 번호는 9번(또는 109번)을 쓴다.
- 수식은 =SUBTOTAL(9, 범위) 형태이다.
이제 더 이상 필터를 걸고 나서 계산기를 두드리거나, 데이터를 다른 시트에 복사해서 합계를 내는 번거로운 작업을 하지 마세요. SUBTOTAL 함수 하나면 칼퇴근 시간이 30분은 빨라질 것입니다.
다음 포스팅에서는 오류 값(#N/A)을 무시하고 합계를 구해주는 AGGREGATE 함수에 대해 다뤄보겠습니다. 도움이 되셨다면 공감과 댓글 부탁드립니다!