엑셀 필터를 걸었는데 합계는 왜 그대로일까?

엑셀에서 필터를 걸었는데 합계가 그대로라면 합계 셀의 수식부터 본다. =SUM(D2:D7)은 그 범위의 숫자를 더하므로 화면에서 일부 행이 사라졌다고 계산 대상까지 줄어들지 않는다. 필터 결과만 더하고 직접 숨긴 행도 제외하려면 =SUBTOTAL(109,D2:D7)로 계산한다. 수동으로 숨겨 둔 거래까지 포함할 목적이라면 첫 인수를 9로 쓴다. Microsoft의 SUBTOTAL 설명

한 r/excel 이용자는 수천 행 목록을 여러 열로 필터한 뒤 금액을 드래그했지만, SUM이 그 사이의 숨은 행까지 더한다고 물었다. 같은 스레드에서는 반대로 필터를 바꿔도 이전 합계를 유지하고 싶다는 후속 질문이 나왔다. SUBTOTAL로 계산하면 영업팀을 볼 때는 영업 합계, 지원팀을 볼 때는 지원 합계가 나온다. 지원팀을 조회하면서도 영업 완료액을 계속 표시할 칸에는 SUMIFS로 부서와 상태 조건을 지정할 수 있다. 필터한 값의 합계를 묻는 원문

42만원 중 영업팀 25만원만 남기는 예제

다음은 동작을 따라가기 위한 가상 거래 목록이다. 컴퓨터용 Excel의 새 시트에 A1:D7 범위로 입력한다. 첫 줄은 번호·부서·상태·금액이고, 금액 칸에는 ‘원’이라는 글자를 붙이지 않은 숫자를 넣는다. 아래 표의 ‘시트 행’은 위치를 설명하는 표시이므로 실제 입력 열에 넣지 않는다.

시트 행	A: 번호	B: 부서	C: 상태	D: 금액(원)
2	T-01	영업	완료	120000
3	T-02	지원	완료	80000
4	T-03	영업	대기	60000
5	T-04	영업	완료	40000
6	T-05	지원	대기	90000
7	T-06	영업	완료	30000

A1:D7을 선택하고 데이터 → 필터를 켠다. 부서 머리글의 화살표에서 전체 선택을 해제한 뒤 ‘영업’만 선택한다. 지원 부서인 3행과 6행은 화면에서 빠지고 영업 거래 네 건이 남는다. 필터는 이 두 거래를 삭제하지 않는다. 머리글에서 조건을 해제하면 다시 표시된다. 범위와 표에 필터 적용하기

비교용 수식은 필터 대상 행과 겹치지 않는 F10:G12에 둔다. F10에는 ‘전체 거래’, F11에는 ‘필터 결과·수동 숨김 포함’, F12에는 ‘필터 결과·수동 숨김 제외’를 적는다. G10부터 다음 수식을 각각 입력한다. 오른쪽에 놓더라도 2~7행에 있으면 거래 행이 숨겨질 때 요약 셀도 함께 사라지므로 행 위치까지 떨어뜨린다.

=SUM(D2:D7)
=SUBTOTAL(9,D2:D7)
=SUBTOTAL(109,D2:D7)

기대값은 G10이 420000, G11과 G12가 250000이다. 영업 네 건인 120000+60000+40000+30000을 직접 더해도 250000이다. 필터 뒤에 보이는 셀을 드래그했더라도 수식 입력줄이 D2:D7이면 연속 범위를 참조한다. SUM을 계속 쓰면서 눈에 안 보이는 거래만 자동으로 제외할 수는 없다. SUM의 범위 합산 방식

전체 거래 금액도 필요한 보고서라면 G10을 없앨 이유가 없다. 예를 들어 전체 접수액과 지금 조회한 부서 금액을 나란히 보여 주면 두 수식이 서로 다른 일을 한다. 다만 두 칸을 모두 ‘합계’라고 표시하면 받는 사람이 420000과 250000 중 어느 숫자를 옮길지 다시 물어야 한다. 계산 대상을 셀 이름에 남긴다.

아직 행을 직접 숨기기 전에 상태도 ‘완료’로 걸러 보면 영업 합계는 190000이 된다. 여기서 부서만 지원으로 바꾸면 80000이다. 지원 전체인 170000이 나오지 않는 것은 상태 필터가 남아 대기 거래를 빼기 때문이다. 상태 조건을 해제하면 지원 두 건이 모두 나타난다. 다음 예제로 넘어갈 때는 상태 필터를 풀고 부서를 다시 영업으로 맞춘다.

9와 109가 달라지는 순간은 행을 직접 숨길 때다

영업 필터를 유지한 상태에서 시트 왼쪽의 5행 번호를 선택하고 마우스 오른쪽 버튼의 숨기기를 실행한다. 40000원 거래가 화면에서 사라진다. 이때 G11의 기대값은 그대로 250000이고 G12는 210000이다. 첫 인수 9는 이 수동 숨김을 계산에서 빼지 않지만 109는 뺀다.

예제 상태	SUM	SUBTOTAL 9	SUBTOTAL 109
처음, 여섯 거래 모두 표시	420000	420000	420000
부서가 영업인 네 거래만 필터	420000	250000	250000
영업 필터 상태에서 5행도 직접 숨김	420000	250000	210000

따라서 109가 언제나 더 정확한 합계인 것은 아니다. 오래된 거래를 화면 정리용으로 접어 두었어도 부서 실적에는 넣어야 한다면, 표시된 줄만 더한 210000은 목적과 맞지 않는다. 반대로 회의 화면에 남긴 세 건을 합하려면 250000이 맞지 않는다. 같은 원본에서 달라진 것은 거래액이 아니라 포함할 행이다.

특히 ‘취소 건은 빼자’ 같은 업무 규칙을 수동 숨김만으로 관리하면 다음 사람이 행을 다시 표시했을 때 금액이 바뀐다. 취소·완료처럼 다시 적용해야 할 조건은 상태 열에 적고 그 값으로 필터하거나 조건식을 쓰는 편이 추적하기 쉽다. 숨김 자체에는 ‘화면 정리’와 ‘집계 제외’라는 사유가 구별돼 저장되지 않기 때문이다.

109는 가로 방향으로 숨긴 열을 빼는 기능도 아니다. 월별 금액을 B2:G2에 늘어놓고 C열을 숨기는 상황에는 같은 논리를 적용할 수 없다. 이 가이드는 한 행이 한 거래인 세로 목록을 대상으로 한다. 원래 예제로 돌아갈 때는 필터 조건과 직접 숨긴 행을 모두 풀어 여섯 거래가 보이는지 확인한 뒤 다음 단계를 진행한다.

거래가 늘어나는 목록은 표의 요약 행으로 계산한다

예제 범위 D2:D7은 일곱 번째 행까지만 계산한다. 그 아래 D8에 새 거래를 따로 입력하면 이 참조에 자동으로 들어간다고 기대해서는 안 된다. 매번 마지막 행 번호를 고치는 작업이 생기는 목록이라면 Excel의 표 기능으로 묶는 편이 낫다.

A1:D7을 선택하고 홈 → 표 서식(Format as Table)에서 스타일을 고른다. 범위가 정확한지 확인하고 ‘머리글 포함’을 선택한다. 기존 표처럼 보이도록 테두리와 배경색만 칠하는 것과 다른 기능이다. Excel 표 만들기

표 안의 셀을 클릭하고 표 디자인의 요약 행(Total Row)을 켠다. 마지막에 생긴 금액 열의 셀에서 드롭다운을 열어 합계(Sum)를 선택한다. Microsoft가 안내하는 기본 합계 수식은 SUBTOTAL의 109를 사용한다. 예제 열 이름이 ‘금액’이면 =SUBTOTAL(109,[금액])처럼 표시된다. 요약 행에는 다른 수식도 넣을 수 있으므로 기존 파일에서는 실제 수식 입력줄을 읽는다. 표의 요약 행 계산

별도 보고 영역에서 참조하려면 표 디자인의 표 이름을 Transactions로 지정하고 =SUBTOTAL(109,Transactions[금액])을 쓴다. 여기서 대괄호 안은 열 이름이다. 표에 데이터를 추가하거나 제거하면 이 구조적 참조가 표의 데이터 범위에 맞춰 조정된다. 표 밖의 빈 곳에 떨어뜨려 붙여 넣은 자료까지 포함한다는 뜻은 아니다. 표 이름과 열 이름으로 참조하기

새 거래를 넣을 때는 실제 표의 데이터 행으로 들어갔는지 본다. 예를 들어 영업·완료 10000원을 추가했다면, 다른 조건과 숨김이 없는 영업 조회 금액은 260000이 되어야 한다. 그대로 250000이라면 새 행이 표 밖인지, 수식이 아직 D2:D7인지, 다른 열의 필터가 그 거래를 제외했는지 차례로 확인한다.

지원팀을 보고 있어도 영업팀 완료액은 남겨 두고 싶다면

SUBTOTAL은 화면의 필터를 따라 바뀌는 합계다. 한쪽에서는 지원팀 거래를 살피면서 다른 칸에는 영업팀 완료액을 계속 보여 줘야 한다면, 조건을 수식에 넣는다. 원래 여섯 거래만 있는 예제에서 다음 식의 결과는 190000이다.

=SUMIFS(D2:D7,B2:B7,"영업",C2:C7,"완료")

첫 범위는 더할 금액, 그다음은 부서 범위와 ‘영업’이라는 조건, 마지막은 상태 범위와 ‘완료’라는 조건이다. 세 범위는 같은 거래 행을 가리키며 크기도 같아야 한다. 이 식은 부서·상태 값을 검사하므로 화면 필터를 지원으로 바꾸는 것만으로 190000이 지원팀 합계가 되지는 않는다. SUMIFS의 조건과 범위

영업 완료 거래는 T-01·T-04·T-06이며 120000+40000+30000이다. T-03의 60000은 영업이지만 대기라서 제외된다. ‘현재 보이는 영업 거래’와 ‘영업 중 완료 거래’는 한 조건이 더 들어간 다른 집계다. 화면을 그대로 둔 채 함수만 바꿔 두 숫자가 같아지기를 기대하면 안 된다.

앞서 만든 Transactions 표를 사용한다면 =SUMIFS(Transactions[금액],Transactions[부서],"영업",Transactions[상태],"완료")로 쓴다. 단, 표에 새 영업 완료 거래가 들어오거나 기존 금액이 수정되면 결과도 갱신된다. ‘필터를 바꿔도 유지’는 ‘어제 제출한 숫자를 영구 보존’과 다르다.

제출 당시 숫자가 필요한 보고서에는 당시 값과 집계 조건, 기준 시점을 별도로 남긴다. 예를 들어 ‘10월 27일 오전 자료, 영업·완료, 190000원’처럼 적어야 나중에 원본이 늘어난 뒤에도 무엇을 제출했는지 알 수 있다. 계속 갱신되는 셀은 계산용으로 남기고, 확정 보고값은 별도의 제출본에서 보존하는 방식이다.

합계가 여전히 작거나 크면 금액과 범위부터 좁힌다

필터 규칙이 맞는데도 일부 금액이 빠진다면 숫자가 문자로 저장됐는지 살핀다. CSV나 다른 시스템에서 복사한 값이 겉으로는 120000이어도 Excel에는 텍스트일 수 있다. SUM은 참조 셀의 텍스트를 더하지 않는다. 합계 번호를 바꾸는 것으로 입력 형식 문제가 해결되지는 않는다.

금액 셀에 오류 표시가 있다면 해당 셀을 선택하고 메뉴의 숫자로 변환(Convert to Number)을 사용한다. 표시가 없거나 원본을 유지하며 비교하려면 옆의 빈 열에 =VALUE(D2)를 넣고 아래로 채워 숫자 변환 결과를 본다. 정상 결과를 확인한 뒤 필요하면 복사 → 선택하여 붙여넣기 → 값으로 금액 열에 반영한다. 식별번호까지 통째로 변환하지 말고 계산할 금액 열만 대상으로 한다. 문자로 저장된 숫자의 변환

예제의 영업 합계 250000에서 정확히 120000이 부족하다면 T-01의 입력값을 먼저 대조할 수 있다. 반면 새로 추가한 거래 금액만 빠졌다면 자료형보다 범위 끝을 먼저 볼 근거가 생긴다. 전체 파일의 수식을 한꺼번에 바꾸기 전에 부족한 차액과 그에 해당하는 거래를 좁히는 방식이다.

중간 소계 행도 별도로 본다. 거래 금액 사이에 넣은 다른 SUBTOTAL 수식은 바깥 SUBTOTAL이 중복 합산하지 않는다. 그러나 ‘소계’라고 적은 옆 칸이 단순 숫자이거나 SUM 수식이라고 해서 똑같이 건너뛰지는 않는다. 이 경우 상세 금액과 소계가 함께 들어갈 수 있으므로 참조 범위 안의 계산 행을 찾아낸다. 원본 거래표와 요약 영역을 분리하면 어떤 행이 거래인지도 분명해진다.

마지막으로 합계 셀 자신이 범위에 들어가면 안 된다. 금액 열 전체를 참조한 수식을 같은 열 안에 두기보다, 처음 예제처럼 옆의 보고 영역이나 표의 요약 행을 사용한다. 데이터 값을 수정한 뒤 필터 목록이 예상과 다르면 필터를 다시 적용하고 남은 거래도 읽는다. 제출할 숫자는 셀 하나만 복사하지 말고 어떤 부서·상태·숨김 처리로 나온 값인지 함께 남긴다.