엑셀 필터한 것만 합계 내고 번호 유지하기

고객 명단에 필터를 걸어 "서울"만 남겼는데, 아래 합계 칸은 여전히 전체 합계를 보여 줍니다. 순번도 1, 4, 6처럼 띄엄띄엄 남아서 몇 명인지 한눈에 들어오지 않습니다. 지역별·구분별로 명단을 나눠 보고 그 자리에서 합계를 확인해야 하는 영업 명단에서 자주 겪는 일입니다.

시험 공부를 할 때도 마찬가지입니다. 성적표를 반별로 걸러 놓고 합계를 보거나 데이터 탭의 [부분합] 기능을 연습하다 보면, 결과 칸 안에 SUBTOTAL 함수가 들어 있는 것을 보게 됩니다. 이 글은 엑셀 "목적별 함수 조합" 시리즈의 한 편으로, SUBTOTAL 하나로 필터한 것만 합계와 끊기지 않는 순번을 함께 해결합니다.

예제 표 — 지역별 고객 명단

아래처럼 A열은 순번, B열은 고객명, C열은 지역, D열은 구분, E열은 월 보험료인 표가 있다고 해 보겠습니다. 이름과 숫자는 모두 연습용으로 지어낸 것입니다. 맨 윗줄의 A~E는 열 문자, 맨 왼쪽 숫자는 행 번호입니다.

	A	B	C	D	E
1	순번	고객명	지역	구분	월 보험료
2	(수식)	김민준	서울	신규	52,000
3	(수식)	이서연	경기	갱신	38,000
4	(수식)	박준호	서울	갱신	45,000
5	(수식)	최수아	인천	신규	29,000
6	(수식)	정우진	경기	신규	61,000
7	(수식)	강하은	서울	신규	33,000
8	(수식)	윤태오	인천	갱신	47,000
9	(수식)	한예린	경기	갱신	55,000
10
11				보이는 합계	(수식)

합계 칸(E11)은 표 바로 아래가 아니라 한 줄 띄운 11행에 두었습니다. 필터 범위와 섞이지 않게 하려는 것입니다.

완성 수식 — 어디에 넣고 어디까지 채우나

수식은 두 개입니다.

A2 셀:  =SUBTOTAL(3, $B$2:B2)
E11 셀: =SUBTOTAL(9, E2:E9)

1. A2 셀을 클릭하고 =SUBTOTAL(3, $B$2:B2)를 입력한 뒤 Enter를 누릅니다.
2. A2 셀을 다시 클릭하고, 셀 오른쪽 아래의 작은 네모(채우기 핸들)를 A9까지 끌어 내립니다.
3. E11 셀에 =SUBTOTAL(9, E2:E9)를 입력하고 Enter를 누릅니다.
4. 1행 아무 칸이나 클릭한 뒤 필터를 켜고(Ctrl + Shift + L), C1의 화살표에서 "서울"만 체크합니다.

필터를 걸기 전과 건 뒤의 결과는 다음과 같습니다.

상태	보이는 행	A열 순번	E11 합계	비교: SUM(E2:E9)
필터 없음	2~9행 전체	1, 2, 3, 4, 5, 6, 7, 8	360,000	360,000
지역 = 서울	2, 4, 7행	1, 2, 3	130,000	360,000

서울 고객 세 명의 월 보험료 52,000 + 45,000 + 33,000 = 130,000만 더해졌고, 순번도 1, 2, 3으로 이어집니다. 같은 자리에 SUM을 넣으면 필터와 상관없이 360,000이 그대로 나옵니다. 필터를 해제하면 SUBTOTAL 쪽도 360,000과 1~8로 돌아옵니다.

수식 풀어 보기 — 안쪽부터 한 겹씩

1단계: SUBTOTAL의 첫 번째 숫자는 "어떤 계산을 할지"

SUBTOTAL은 SUBTOTAL(function_num, ref1, ...) 모양입니다. 첫 번째 인수 function_num은 계산 종류를 고르는 번호이고, 두 번째부터는 계산할 범위입니다. 자주 쓰는 번호만 추리면 다음과 같습니다.

계산	숨긴 행 포함	숨긴 행 제외
AVERAGE(평균)	1	101
COUNT(숫자 개수)	2	102
COUNTA(비어 있지 않은 칸 개수)	3	103
MAX(최댓값)	4	104
MIN(최솟값)	5	105
SUM(합계)	9	109

Microsoft 문서에 따르면 필터로 걸러진 행은 번호와 관계없이 항상 계산에서 빠집니다. 1~11과 101~111의 차이는 필터가 아니라 마우스 오른쪽 버튼의 [숨기기]처럼 손으로 숨긴 행을 넣느냐 빼느냐입니다.

2단계: 합계 — SUBTOTAL(9, E2:E9)

9번은 SUM입니다. 그래서 이 수식은 "E2:E9 중 지금 화면에 보이는 칸만 더하라"는 뜻이 됩니다. 서울 필터를 걸면 E2(52,000), E4(45,000), E7(33,000)만 남아 130,000이 나옵니다.

3단계: 순번 — $B$2:B2 가 한 줄씩 늘어나는 범위

순번 수식의 핵심은 범위 $B$2:B2입니다. 시작점 $B$2는 $로 고정했고, 끝점 B2는 고정하지 않았습니다. 아래로 채우면 이렇게 바뀝니다.

셀	실제 수식	세는 범위	서울 필터 때 결과
A2	=SUBTOTAL(3, $B$2:B2)	B2 한 칸	1
A4	=SUBTOTAL(3, $B$2:B4)	B2~B4 (보이는 칸은 B2, B4)	2
A7	=SUBTOTAL(3, $B$2:B7)	B2~B7 (보이는 칸은 B2, B4, B7)	3

3번은 COUNTA, 즉 비어 있지 않은 칸을 세는 계산입니다. "첫 행부터 내 행까지, 보이는 이름이 몇 개인가"를 세니 곧 내 순번이 됩니다. 숨겨진 3·5·6행의 이름은 세지 않으므로 번호가 건너뛰지 않습니다.

4단계: 왜 ROW()-1로는 안 되나

순번을 흔히 =ROW()-1로 매깁니다. ROW는 그 칸의 행 번호를 돌려주므로 2행은 1, 4행은 3, 7행은 6이 됩니다. 행 번호는 필터와 상관없이 그대로라서, 서울 필터를 걸면 순번이 1, 3, 6으로 보입니다. 필터와 함께 쓸 순번이라면 SUBTOTAL 방식이 맞습니다.

자주 막히는 곳

• 손으로 숨긴 행까지 빼고 싶을 때 — 필터 없이 5·6행을 [숨기기] 한 경우, SUBTOTAL(9, E2:E9)는 숨긴 행을 넣어 360,000을, SUBTOTAL(109, E2:E9)는 빼고 270,000을 돌려줍니다. 순번도 3 대신 103을 쓰면 보이는 행만 1~6으로 이어집니다.
• SUBTOTAL은 세로 범위용입니다 — Microsoft 문서는 이 함수가 열(세로 범위)용으로 설계되었고 가로 범위용이 아니라고 설명합니다. 행을 걸러 내는 표(위에서 아래로 쌓인 명단)에 쓰십시오.
• SUBTOTAL끼리는 겹쳐 세지 않습니다 — 범위 안에 다른 SUBTOTAL 수식이 있으면 그 칸은 이중 계산을 막기 위해 무시됩니다. 중간 소계가 끼어 있는 표에서 전체 합계를 낼 때 편리한 성질입니다.
• #VALUE! 오류 — 여러 시트를 한 번에 묶는 3차원 참조(예: Sheet1:Sheet3!E2)를 넣으면 SUBTOTAL은 #VALUE!를 돌려줍니다.

다른 방법 — 표 기능과 [부분합] 명령

표 기능의 요약 행. 범위를 표로 바꾼 뒤(Ctrl + T) [표 디자인] 탭(버전에 따라 [테이블 도구] > [디자인])에서 [요약 행]을 체크하면, 엑셀이 맨 아래에 =SUBTOTAL(109, [열 이름]) 같은 수식을 알아서 넣어 줍니다. 수식을 직접 쓰지 않아도 필터 결과에 맞는 합계가 나옵니다. 다만 순번 수식은 여전히 직접 넣어야 합니다.

데이터 탭의 [부분합] 명령. 지역별로 소계를 한 번에 끼워 넣고 싶다면 이 명령이 SUBTOTAL 수식을 자동으로 만들어 줍니다. 먼저 묶을 열(예: 지역)로 정렬해 두어야 하고, 지우고 싶을 때는 같은 창에서 [모두 제거]를 누릅니다. Microsoft 문서에 따르면 표(Ctrl + T로 만든 표) 상태에서는 부분합 명령을 쓸 수 없으므로 일반 범위로 되돌린 뒤 사용합니다. 시험 준비 중이라면 [부분합] 명령으로 만들어진 칸을 클릭해 수식 입력줄에 SUBTOTAL이 들어 있는 것을 직접 확인해 보면 원리가 잘 잡힙니다.

방법	필터한 것만 합계	순번 유지	어울리는 경우
SUBTOTAL 수식 직접 입력	됨	됨(3 또는 103)	일반 범위, 원하는 위치에 합계
표 기능 요약 행	됨(109)	순번은 따로 입력	계속 행이 늘어나는 명단
[부분합] 명령	그룹별 소계 자동	해당 없음	정렬된 목록에 그룹별 합계 끼워 넣기

자주 묻는 질문

함께 보면 좋은 글

• 엑셀 필터 사용법 원하는 데이터만 보기
• 엑셀 절대참조 상대참조 차이와 $ 사용법
• 엑셀 표 기능 사용법 범위를 표로 바꾸기
• 엑셀 글 전체 목차

참고

• Microsoft 지원 — SUBTOTAL 함수
• Microsoft 지원 — Excel 표의 데이터 합계(요약 행)
• Microsoft 지원 — 워크시트의 데이터 목록에 부분합 삽입