엑셀에서 매출표를 필터링했는데 SUM 합계는 여전히 전체 매출액으로 표시되는 경우가 있습니다. 필터로 숨겨진 행을 빼고 현재 화면에 보이는 행만 합계를 구하려면 SUBTOTAL 함수를 사용하면 됩니다.
매출 내역 6건 중 '문구'만 선택해 전체 60,000원 → 표시된 37,000원으로 합계가 바뀌는 과정을 설명하겠습니다. 같은 방법으로 보이는 주문 건수와 판매수량도 계산할 수 있습니다.
SUBTOTAL 함수는 SUM과 무엇이 다를까?
SUM 함수는 필터로 행을 숨겨도 지정한 범위의 숫자를 모두 더합니다. 반면 SUBTOTAL은 필터로 제외된 행을 계산에서 뺍니다. 기본 구문은 다음과 같습니다.
=SUBTOTAL(함수번호,계산범위)
첫 번째 인수는 어떤 계산을 할지 정하는 함수번호이고, 두 번째 인수는 매출액처럼 계산할 셀 범위입니다. 합계에 자주 쓰는 번호는 9와 109입니다.
=SUM(E2:E7)
=SUBTOTAL(9,E2:E7)
=SUBTOTAL(109,E2:E7)
필터를 적용하지 않은 상태에서 세 수식은 모두 60,000입니다. 하지만 '문구'만 남기도록 필터를 적용하면 SUM은 60,000을 유지하고, SUBTOTAL은 37,000을 반환합니다.
실습용 매출표 입력하고 필터 적용하기
A1:E7에 아래 표를 그대로 입력합니다. E열 매출액은 실제 숫자로 입력하세요. 쉼표는 표시 형식으로 적용해도 되며, 10000원처럼 글자를 붙여 텍스트로 만들지 않습니다.
| 행 | A 주문번호 | B 분류 | C 상품명 | D 수량 | E 매출액 |
|---|---|---|---|---|---|
| 1 | 주문번호 | 분류 | 상품명 | 수량 | 매출액 |
| 2 | ORD01 | 문구 | 노트 | 2 | 10,000 |
| 3 | ORD02 | 식품 | 과자 | 3 | 9,000 |
| 4 | ORD03 | 문구 | 펜 | 5 | 15,000 |
| 5 | ORD04 | 식품 | 주스 | 2 | 6,000 |
| 6 | ORD05 | 문구 | 파일 | 1 | 12,000 |
| 7 | ORD06 | 생활 | 수건 | 2 | 8,000 |
합계나 건수 수식은 필터 범위 바깥인 H열과 I열에 배치합니다. H1에 집계 항목, I1에 결과를 입력하고, 다음과 같이 설정합니다.
| 셀 | 내용 | I열 수식 |
|---|---|---|
| H2 / I2 | 보이는 매출 합계 | =SUBTOTAL(109,E2:E7) |
| H3 / I3 | 보이는 수량 합계 | =SUBTOTAL(109,D2:D7) |
| H4 / I4 | 보이는 주문 건수 | =SUBTOTAL(103,C2:C7) |
| H5 / I5 | 전체 매출 합계 | =SUM(E2:E7) |
아직 필터를 걸지 않았다면 I2는 60,000원, I3은 15개, I4는 6건, I5는 60,000원입니다. 이제 필터를 적용하겠습니다.
- A1:E7 범위 안의 셀을 선택한 뒤 데이터 → 필터를 클릭합니다.
- B1의 분류 필터 화살표를 클릭합니다.
- 선택 항목에서 문구만 체크하고 확인합니다.
- 2행·4행·6행에 해당하는 문구 상품 3건만 표시되는지 확인합니다.
필터 후 표시되는 행은 노트 10,000원, 펜 15,000원, 파일 12,000원입니다. I2의 SUBTOTAL 합계는 37,000원으로 바뀌지만 I5의 SUM 합계는 60,000원으로 유지됩니다.
필터 조건을 '식품'으로 바꾸면 과자 9,000원과 주스 6,000원만 표시되어 SUBTOTAL 합계는 15,000원이 됩니다. 필터를 해제하면 다시 전체 60,000원으로 돌아옵니다.
같은 범위의 데이터를 두고도 필터 조건만 바꾸면 표시되는 합계가 달라지므로, 상품 분류별 매출을 빠르게 확인할 때 유용합니다. 필터와 관계없이 특정 조건의 합계를 항상 계산하려면 별도의 조건 합계 함수가 더 적합합니다.
필터 후 보이는 행의 개수와 수량 구하기
SUBTOTAL은 합계뿐 아니라 숫자 개수와 비어 있지 않은 셀의 개수도 셀 수 있습니다. 여기서는 I3과 I4가 그 예입니다.
=SUBTOTAL(109,D2:D7)
=SUBTOTAL(103,C2:C7)
첫 번째는 필터 후 보이는 D열 수량을 합산합니다. 두 번째는 C열 상품명이 채워진 행의 개수를 셉니다. 분류를 '문구'로 필터링하면 각각 수량 8개, 주문 3건입니다.
개수에 사용하는 번호 102와 103은 구분해야 합니다. 102는 숫자가 들어 있는 셀을 세는 COUNT이고, 103은 비어 있지 않은 셀을 세는 COUNTA입니다. 상품명처럼 문자인 C열에 =SUBTOTAL(102,C2:C7)를 사용하면 이 예제에서는 0이 나옵니다.
| 구하려는 값 | 수식 | 문구 필터 결과 |
|---|---|---|
| 매출 합계 | =SUBTOTAL(109,E2:E7) | 37,000원 |
| 판매수량 합계 | =SUBTOTAL(109,D2:D7) | 8개 |
| 주문 건수(상품명 기준) | =SUBTOTAL(103,C2:C7) | 3건 |
| 숫자로 입력한 매출 셀 개수 | =SUBTOTAL(102,E2:E7) | 3개 |
상품명 셀이 비어 있는 주문도 있다면 C열 COUNTA 결과는 실제 주문 건수보다 작아질 수 있습니다. 실제 업무에서 건수를 정확히 세려면 모든 주문에 값이 들어 있는 주문번호 열처럼 빠짐없는 기준을 사용하세요. 이 예제에서는 =SUBTOTAL(103,A2:A7)도 3건이 됩니다.
SUBTOTAL 9와 109는 언제 다르게 계산될까?
필터로 숨겨진 행은 9와 109 모두 제외합니다. 차이는 사용자가 행 숨기기로 직접 숨긴 행을 처리할 때 나타납니다.
먼저 앞의 '문구' 필터를 유지한 상태에서 4행(펜·15,000원)을 마우스 오른쪽 버튼의 숨기기로 수동으로 숨긴다고 가정하겠습니다. 그러면 화면에는 노트와 파일만 남습니다.
| 수식 | 수동으로 4행을 숨긴 뒤 결과 | 처리 방식 |
|---|---|---|
=SUBTOTAL(9,E2:E7) | 37,000원 | 필터 제외 행은 무시, 수동 숨김은 포함 |
=SUBTOTAL(109,E2:E7) | 22,000원 | 필터 제외 행과 수동 숨김 모두 제외 |
화면에서 실제로 보이는 매출액만 더하는 것이 목적이라면 109를 사용하는 편이 적절합니다. 수동으로 숨긴 4행을 다시 표시하면 109 결과도 37,000원으로 돌아옵니다.
자주 쓰는 함수번호는 합계 9·109, 숫자 개수 2·102, 비어 있지 않은 셀 개수 3·103입니다. 앞자리 1이 붙은 100번대 코드는 수동으로 숨긴 행까지 제외하는 방식입니다.
실제로 데이터를 관리할 때는 필터로 숨긴 행과 직접 숨긴 행이 혼재할 수 있습니다. 합계가 예상과 다르면 해당 행이 어떤 방식으로 숨겨졌는지부터 확인해 보세요.
구글시트에서 SUBTOTAL 함수 사용하는 방법
Google Sheets에서도 SUBTOTAL 함수와 번호 9·109·102·103을 사용할 수 있습니다. 같은 A1:E7 예제를 입력하고 I2에 아래 수식을 넣으면 됩니다.
=SUBTOTAL(109,E2:E7)
필터는 A1:E7 선택 → 데이터 → 필터 만들기로 추가합니다. B열의 필터 메뉴에서 '문구'만 남기면 I2의 결과가 37,000원으로 바뀝니다.
Google Sheets에서는 다른 편집자도 일반 필터를 변경할 수 있습니다. 공동작업 중 자신의 화면에서만 조건을 바꿔 보고 싶다면 필터 보기 기능도 검토하세요. 다만 이번 실습은 일반 필터를 기준으로 설명합니다.
필터 합계가 예상과 다를 때 확인할 점
필터를 적용했는데 SUM 합계만 변하지 않을 때
=SUM(E2:E7)은 필터로 숨겨진 행도 포함합니다. 현재 보이는 행만 계산하려면 수식을 =SUBTOTAL(109,E2:E7)처럼 변경합니다. 또한 수식을 필터가 적용된 행 안에 놓으면 요약 셀 자체가 숨겨질 수 있으므로, 이 예제처럼 필터 범위 밖에 배치하는 것이 편리합니다.
거래 건수가 매출 행 수보다 적게 나올 때
사용한 함수번호와 대상 열을 확인하세요. 102는 숫자만 세기 때문에 상품명 같은 텍스트 열에 사용하면 0이 나올 수 있습니다. 건수를 세려면 값이 빠지지 않는 주문번호 열에 103을 적용하는 방식이 좋습니다.
표에 새 행을 추가했는데 SUBTOTAL이 반영되지 않을 때
새 데이터가 기존 범위 E2:E7 밖에 추가되었다면 계산에 포함되지 않습니다. 행이 계속 늘어나는 목록은 수식 범위를 늘리거나 Excel 표 기능과 함께 관리할 수 있습니다. 단순한 함수 예제에서는 범위가 어디까지인지 먼저 확인하세요.
또한 특정 조건의 합계를 구한다고 해서 SUBTOTAL과 SUMIF가 같은 기능은 아닙니다. SUBTOTAL은 화면에 표시된 행을 기준으로 계산하고, SUMIF·SUMIFS는 수식에 지정한 조건을 기준으로 계산합니다. 필터 사용과 무관하게 조건을 고정하려면 엑셀·구글시트 SUMIF·SUMIFS 함수 사용법을 참고하면 됩니다.
필터를 바꿀 때마다 보이는 합계도 함께 바뀌게 하려면 =SUBTOTAL(109,E2:E7)을 사용하세요. 개수는 값이 들어 있는 주문번호 열에 =SUBTOTAL(103,A2:A7)을 지정하면 됩니다.





