엑셀 필터 합계 오류, 아직도 SUM 쓰시나요? SUBTOTAL 함수 완벽 정복

안녕하세요! 엑셀로 데이터 정리하다가 식은땀 흘려본 적, 다들 한 번쯤 있으시죠?

특히 월말 보고서 작성할 때 이런 상황 꼭 생깁니다.
분명히 필터를 걸어서 ‘서울 지점’ 데이터만 남겼는데,
합계를 내보니 전체 지점 합계가 나와버리는 황당한 상황 말이에요.

“어? 이거 왜 숫자가 안 맞지?”

저도 신입 사원 시절에 이 실수 때문에 부장님께 불려가서 깨진 적이 있어요.
분명 화면에는 100만 원만 보이는데, 합계는 1,000만 원으로 찍혀있으니 얼마나 당황스럽던지요.

이건 여러분의 잘못이 아닙니다.
우리가 습관처럼 쓰는 SUM 함수의 특징 때문이에요.

오늘은 필터링된 데이터, 즉 ‘눈에 보이는 셀’만 정확하게 계산해 주는 엑셀의 구세주, SUBTOTAL 함수에 대해 아주 쉽고 자세하게 알려드릴게요.

이 글을 다 읽고 나면, 더 이상 계산기 두드리며 검산할 필요가 없어질 거예요!

1. 왜 SUM 함수는 필터링 된 값을 무시할까요?

우리가 가장 흔하게 쓰는 =SUM(범위) 함수는 굉장히 정직한 녀석입니다.
그냥 지정된 범위 안에 있는 숫자는 묻지도 따지지도 않고 다 더해버리죠.

문제는 우리가 ‘필터’를 걸었을 때 발생합니다.

필터 기능을 써서 특정 행을 숨겼다고 해도, 그 행이 삭제된 건 아니거든요.
SUM 함수 입장에서는 “아, 잠깐 숨은 거네? 그럼 내가 다 찾아내서 더해줘야지!” 하고 과한 친절을 베푸는 겁니다.

그래서 화면에 보이는 값은 3개뿐인데, 결과값은 숨겨진 100개 데이터의 합이 나오는 거죠.
이걸 모르고 보고서를 올렸다가는… 상상만 해도 끔찍하죠?

2. SUBTOTAL 함수, 도대체 뭔가요?

자, 이제 해결사 SUBTOTAL을 소개합니다.
이름부터 ‘부분(Sub) 합계(Total)’라는 뜻을 가지고 있죠.

이 함수는 필터링 기능과 찰떡궁합입니다.
“필터에 의해 숨겨진 행은 계산에서 제외한다”는 강력한 규칙을 가지고 있거든요.

사용법은 생각보다 아주 간단해요.

여기서 ‘기능번호’라는 게 등장하는데요.
이것만 알면 엑셀 고수 소리 들을 수 있습니다.

SUBTOTAL은 합계만 구하는 게 아닙니다.
평균(AVERAGE), 개수(COUNT), 최대값(MAX) 등 다양한 계산을 할 수 있는데, 어떤 계산을 할지 숫자로 지정해 주는 것이 바로 기능번호입니다.

3. 가장 중요한 핵심! 기능번호 9와 109의 차이

SUBTOTAL 함수를 쓰려고 하면 1번부터 11번, 그리고 101번부터 111번까지 숫자가 쭈르륵 나옵니다.
여기서 많은 분들이 멘붕에 빠지죠.

“합계를 구하려면 9번을 쓰라는데, 109번은 또 뭐야?”

이거 정말 중요합니다. 별표 다섯 개 치세요! ⭐⭐⭐⭐⭐

표를 보니 감이 오시나요?

필터 기능만 쓴다면 9번이나 109번이나 결과는 똑같습니다.
하지만 우리가 엑셀 작업을 하다 보면 필터가 아니라, 보기 싫은 행을 우클릭해서 ‘숨기기’로 가려놓는 경우도 있잖아요?

이때 9번을 쓰면, 내가 억지로 숨겨놓은 행의 값까지 몰래 더해버립니다.
반면에 109번은 “눈에 안 보이면 무조건 계산 안 해!”라는 원칙을 지키죠.

그래서 저는 헷갈리지 않게 무조건 109번을 사용하는 것을 추천합니다.
화면에 보이는 것만 합계 내는 것이 우리의 목적이니까요.

4. 따라 해보기: 10초 만에 필터 합계 구하기

백문이 불여일견! 실제로 어떻게 쓰는지 단계별로 보여드릴게요.
지금 엑셀 창 띄우고 한번 따라 해보세요.

상황: 거래처별 판매 리스트가 있습니다.
저는 여기서 ‘A거래처’의 판매 금액 합계만 보고 싶어요.

Step 1. 데이터 확인 및 필터 적용

데이터의 제목 줄(헤더)을 클릭하고 단축키 Ctrl + Shift + L을 눌러 필터를 적용합니다.
그리고 원하는 거래처만 선택해서 필터링하세요.

Step 2. 합계 셀에 함수 입력

합계를 표시할 셀에 다음과 같이 입력합니다.

=SUBTOTAL(109,

괄호를 열면 엑셀이 친절하게 기능 목록을 보여줍니다.
거기서 109를 더블클릭하거나 직접 입력하세요.

Step 3. 범위 지정하기

이제 계산하고 싶은 금액 열 전체를 드래그합니다.
=SUBTOTAL(109, C2:C100) 처럼요.

Step 4. 엔터 치고 확인하기

엔터를 치면 짜잔!
전체 합계가 아니라, 현재 필터링 된 항목들의 합계만 딱 나옵니다.
이제 필터 조건을 바꿔보세요. ‘B거래처’, ‘C거래처’로 바꿀 때마다 합계 숫자가 실시간으로 스르륵 바뀌는 마법을 볼 수 있습니다.

5. 자주 묻는 질문 (FAQ) & 주의사항

여기까지 읽으셨다면 이미 상위 10% 엑셀 사용자입니다.
하지만 실무에서는 변수가 항상 존재하죠. 자주 묻는 질문들을 정리해 봤습니다.

Q1. SUBTOTAL 함수 안에 또 SUBTOTAL이 있으면 어떻게 되나요?

이거 정말 똑똑한 질문입니다.
예를 들어, 중간중간 소계를 내고 맨 아래에 총계를 낼 때가 있죠?

SUBTOTAL 함수는 참조 범위 안에 있는 다른 SUBTOTAL 함수의 결과값은 무시합니다.
즉, 중간 소계가 있어도 총계 낼 때 중복으로 더해지지 않아요.
(단, SUM 함수로 소계를 냈다면 중복 계산되니 주의하세요!)

Q2. 필터링 된 데이터에 순번(No.)을 매기고 싶어요.

필터를 걸면 순번이 1, 5, 8… 이렇게 뒤죽박죽되잖아요?
이때도 SUBTOTAL을 응용할 수 있습니다.

=SUBTOTAL(103, $B$2:B2)

이런 식으로 범위를 고정해서 수식을 짜면, 필터링 된 상태에서도 1, 2, 3, 4… 순서대로 번호가 매겨집니다.
(여기서 103은 COUNTA 기능, 즉 비어있지 않은 셀의 개수를 세는 기능입니다.)

마치며: 이제 칼퇴근만 남았습니다

지금까지 엑셀 필터 합계의 정석, SUBTOTAL 함수에 대해 알아봤습니다.

처음에는 함수 번호(9, 109)가 조금 헷갈릴 수 있지만,
딱 한 번만 제대로 써보면 그 편리함에 반해서 다시는 SUM 함수로 돌아가지 못하실 거예요.

특히 “화면에 보이는 대로 계산하고 싶으면 무조건 109번!”
이것만 기억하셔도 오늘 얻어가는 건 충분합니다.

매번 계산기로 검산하느라 야근하지 마시고,
스마트한 함수 하나로 업무 시간 단축하고 칼퇴근하세요!

[이미지 마지막: 직장인이 엑셀 업무를 빠르게 마치고 여유롭게 커피를 마시는 모습]