엑셀 SUBTOTAL로 필터 뒤 남은 수량을 더하려면 =SUBTOTAL(109,D2:D7)을 사용한다. 필터로 제외한 행은 계산에서 빠지고, 직접 숨긴 행도 제외한다. 직접 숨긴 행은 합계에 남겨야 한다면 109 대신 9를 사용한다.
필터를 바꿔도 전체 합계가 그대로인 SUM이 잘못된 것은 아니다. ‘원본 전체’와 ‘현재 표시된 항목’은 서로 다른 결과이다. 두 결과를 함께 두면 필터 적용 중인지 확인하기 쉽다.
이 글은 가상 처리 목록과 별도 산술로 확인한 예상값을 사용한다. 실제 Excel 앱의 필터·인쇄 실행 결과가 아니다. Microsoft 365·Excel 2024·2021·2019·2016 및 공식 도움말에 명시된 Mac용 제품을 기준으로 하며, 웹앱과 메뉴가 완전히 같다고 가정하지 않는다.
엑셀 SUBTOTAL을 넣기 전에 집계 칸부터 분리한다
원본은 A1:D7, 집계는 B10:D13처럼 데이터 행 밖에 둔다. 필터는 행을 가리므로 원본 오른쪽 같은 행에 결과를 놓으면 집계 셀도 함께 사라질 수 있다. Microsoft의 데이터 배치 지침도 중요한 정보는 데이터 범위 위나 아래에 배치하도록 안내한다.
다음은 실제 주문이나 고객 정보가 아닌 연습용 목록이다. A1에는 ID, B1에는 구분, C1에는 상태, D1에는 수량을 입력한다. D7은 숫자 0이 아니라 아무것도 입력하지 않은 빈 셀로 둔다.
| 행·ID | 구분 | 상태 | 수량 |
|---|---|---|---|
| 2·P01 | 문서 | 완료 | 12 |
| 3·P02 | 장비 | 완료 | 8 |
| 4·P03 | 문서 | 대기 | 5 |
| 5·P04 | 문서 | 완료 | 0 |
| 6·P05 | 장비 | 대기 | 7 |
| 7·P06 | 문서 | 완료 | 빈 셀 |
집계 칸에는 다음 수식을 따로 입력한다. 수식의 참조 범위에 집계 셀 자신을 넣지 않는다.
- D10:
=SUM(D2:D7)— 전체 수량, 예상값 32 - D11:
=SUBTOTAL(9,D2:D7)— 필터 제외 행만 빼기, 예상값 32 - D12:
=SUBTOTAL(109,D2:D7)— 수동 숨김 행도 빼기, 예상값 32 - D13:
=SUBTOTAL(103,A2:A7)— 표시된 ID 개수, 예상값 6
아직 필터와 숨김을 적용하지 않은 상태라 세 합계는 같다. 12+8+5+0+7=32로 대조한다. 빈 수량은 합계에 더해지지 않아도 입력 누락일 수 있으므로, 합계 하나만 맞았다고 목록 전체가 완성됐다고 판단하지 않는다.
9와 109는 같은 원본에서 세 번 비교한다
필터와 직접 숨기기를 섞지 말고, 각 연습은 모두 표시한 원본 상태에서 시작한다. 공식 SUBTOTAL 설명에 따르면 필터로 빠진 행은 어느 함수 번호든 제외된다. 9와 109의 차이는 직접 숨긴 행에서 나타난다.
1. 구분에서 문서만 남긴다
- 제목을 포함한 A1:D7을 선택하고 데이터 → 필터를 실행한다.
- B1의 필터 화살표에서 문서만 선택한다.
- ID가 P01·P03·P04·P06 네 개 남았는지 확인한다.
- D11·D12는 모두 17, D13은 4인지 대조한다. D10은 전체 수량 32이다.
예상 합계는 12+5+0=17이다. 필터 화살표가 없거나 일부 행이 남는다면 수식보다 필터 범위와 작동 문제를 먼저 확인한다. 메뉴 동작은 Microsoft의 필터 안내를 기준으로 한다.
2. 필터를 풀고 원본 2행만 직접 숨긴다
문서 조건을 해제해 여섯 ID를 모두 표시한 뒤, 2행 P01을 직접 숨긴다. 숨길 대상은 원본 행이며 D12가 놓인 결과 행이 아니다.
예상값은 D10 32, D11 32, D12 20, D13 5이다. 9는 직접 숨긴 12개를 포함하지만 109는 제외하므로 32-12=20이 된다. 한 행을 삭제한 것이 아니므로 숨기기를 취소하면 원본과 결과를 다시 확인할 수 있다.
3. 숨김을 풀고 문서·완료만 남긴다
2행 숨김을 취소하고 모두 표시한 상태를 확인한다. B열을 문서, C열을 완료로 필터한다. 남는 ID는 P01·P04·P06 세 개이며 D12는 12, D13은 3이다.
합계는 12이지만 세 항목 모두 수량 입력이 끝난 것은 아니다. 여기서 건수와 수량의 차이를 한 번 더 확인한다.
엑셀 SUBTOTAL의 0과 빈칸을 구분한다
ID가 있는 건수는 103, 숫자 수량이 입력된 칸 수는 102로 확인할 수 있다. 마지막 필터 상태에서 =SUBTOTAL(102,D2:D7)의 예상값은 2이다. 수량 12와 0은 숫자지만 P06은 빈칸이다.
| 확인할 것 | 계산 범위·번호 | 예상값 |
|---|---|---|
| 남은 항목 수 | ID열·103 | 3 |
| 숫자 입력 칸 수 | 수량열·102 | 2 |
| 입력된 수량 합계 | 수량열·109 | 12 |
연습표는 모든 행에 ID가 있고, 수량은 숫자 또는 진짜 빈칸만 있다는 조건이다. 따라서 3건 중 숫자 입력은 2건이므로 한 건을 점검해야 한다. 0은 수량이 없다고 입력한 값이고 빈칸은 아직 확인하지 않은 값일 수 있다.
숫자처럼 보이는 문자나 ="" 수식이 섞이면 이 단순 비교의 전제가 달라진다. 개수 차이가 나면 오류라고 단정하지 말고 해당 셀의 실제 내용을 확인한다. 숫자로 변환하기 전에는 원본을 보존한다.
합계 0은 ‘남은 항목 없음’과 다르다
0으로 입력된 항목 한 개가 남아 있어도 합계는 0이다. 연습을 따로 확인하려면 필터를 모두 지우고 수동 숨김을 해제한 다음, A열 ID에서 P04만 선택한다. 예상값은 D12의 합계 0, D13의 ID 개수 1, =SUBTOTAL(102,D2:D7)의 숫자 입력 개수 1이다.
합계 0만 보고 항목이 없다고 결론 내리지 않는다. P04의 수량은 빈칸이 아니라 숫자 0이므로 입력된 숫자 한 칸으로 센다. 확인 후 A열 ID 필터를 지우고 전체 수량 32, ID 6건으로 돌아오는지 대조한다. 위 값은 공식 함수 규칙에 따른 가상 예제의 예상값이며 실제 앱 시험 결과가 아니다.
엑셀 SUBTOTAL 합계가 다를 때 확인 순서
번호를 바꾸기 전에 표시 상태와 참조 주소부터 확인한다. 원인을 숨기려고 오류를 곧바로 0으로 치환하지 않는다.
- 대상 행: 필터 제외인지 직접 숨김인지 구분한다. 화면에서 사라졌다는 결과만으로는 같지 않다.
- 범위: 예제는 7행까지이다. 8행에 새 데이터를 추가하면 모든 집계 수식의 끝 주소를 함께 수정해야 한다.
- 데이터 종류:
12개와 숫자 12는 다르다. 오류 셀·문자·누락 입력을 점검한다. - 합계 위치: 필터 범위에 집계 행까지 들어갔거나 자기 자신을 참조하지 않았는지 확인한다.
- 배치 방향: 109는 세로 데이터의 숨긴 행을 다룬다. 가로 표에서 열을 숨겨도 같은 제외 효과를 기대하지 않는다.
다른 SUBTOTAL 결과가 참조 범위에 포함되면 중첩 소계의 이중 계산을 피하도록 처리된다. 반면 일반 숫자로 붙여 넣은 ‘중간 합계’는 원본 숫자와 함께 더해질 수 있다. 소계 행이 섞인 표에서는 함수 이름과 실제 셀 수식을 확인한다.
필터 합계와 조건 합계, 그룹 소계는 나눠 고른다
현재 화면을 따라갈 값이면 SUBTOTAL, 화면과 무관하게 조건을 고정할 값이면 SUMIF를 검토한다. 담당자가 필터를 바꿔도 ‘완료 전체 수량’을 유지해야 하는 보고서는 SUMIF 조건 합계가 더 맞을 수 있다. 조건별 항목 수는 COUNTIF에서 이어 볼 수 있다.
부서별 소계 행을 자동 삽입하는 데이터 탭의 부분합 명령은 수식 한 칸을 넣는 작업과 다르다. 공식 부분합 안내는 그룹 기준 정렬과 별도 명령을 설명한다. 여러 집단의 결과를 한눈에 비교하려면 피벗테이블도 검토한다.
엑셀 SUBTOTAL 결과를 전달할 때는 ‘전체 32’와 ‘문서·완료 표시분 12, ID 3건, 숫자 입력 2건’처럼 필터 조건과 누락 확인 기준을 함께 적는다. 합계 숫자만 복사하면 상대방이 어떤 항목이 빠진 결과인지 알 수 없다.