거래처별 매출 목록에 필터를 걸어 특정 지역만 남긴 다음, 화면 하단에 합계를 넣어봤는데 필터 전과 똑같은 숫자가 나온 경험 있으실 겁니다. SUM 함수는 필터로 숨겨진 행까지 그대로 계산에 포함하기 때문입니다. 이럴 때 필요한 것이 엑셀 SUBTOTAL 함수입니다.
엑셀 SUBTOTAL 함수, SUM이랑 뭐가 다른가요
SUBTOTAL 함수는 필터로 숨겨진 행을 계산에서 자동으로 제외하는 함수입니다. SUM은 화면에 보이든 숨겨져 있든 지정한 범위의 모든 셀을 더하지만, SUBTOTAL은 필터를 걸어 숨겨진 행을 무시하고 현재 화면에 보이는 값만 계산합니다. 그래서 필터를 바꿀 때마다 합계나 평균이 자동으로 그 상태에 맞게 갱신됩니다.
수식 구조는 다음과 같습니다.
=SUBTOTAL(함수번호, 범위)예를 들어 A2:A100 범위의 합계를 구하려면 =SUBTOTAL(9, A2:A100)이라고 입력합니다. 여기서 9는 SUM에 해당하는 함수번호입니다.
함수번호, 숫자마다 뭐가 다른가요
함수번호는 크게 두 그룹으로 나뉩니다. 111번은 수동으로 숨긴 행까지 포함해서 계산하고, 101111번은 수동으로 숨긴 행은 제외하고 필터로 숨겨진 행만 제외합니다. 실무에서는 필터 작업이 많으므로 101번대를 쓰는 경우가 더 많습니다.
주요 함수번호는 다음과 같습니다.
- 1 / 101: AVERAGE (평균)
- 2 / 102: COUNT (숫자 개수)
- 3 / 103: COUNTA (비어있지 않은 셀 개수)
- 4 / 104: MAX (최대값)
- 5 / 105: MIN (최소값)
- 9 / 109: SUM (합계)
예를 들어 거래처 목록에서 특정 담당자 행을 마우스로 직접 숨기고 필터도 함께 쓰는 상황이라면, 담당자 행은 계산에 포함하고 필터로 제외된 행만 빼려면 9번을, 둘 다 빼려면 109번을 씁니다. 실무에서 헷갈리기 쉬운 부분이라 처음 쓸 때는 숫자를 하나씩 바꿔보면서 결과가 달라지는지 확인해보시는 걸 권합니다.
실무 예시로 확인하는 SUBTOTAL 활용법
거래처별 월 매출이 정리된 표가 있다고 가정해봅니다. B열에 지역, D열에 매출액이 있고, 표 아래 D101 셀에 합계를 넣으려는 상황입니다.
=SUBTOTAL(109, D2:D100)이렇게 입력한 뒤 B열 필터에서 “경기”만 선택하면 D101의 합계가 경기 지역 매출만 더한 값으로 자동 바뀝니다. 필터를 “전체”로 되돌리면 다시 전체 합계로 돌아갑니다. 매번 필터를 바꿀 때마다 수식을 새로 만들거나 범위를 다시 선택할 필요가 없습니다.
평균 근무시간을 구하고 싶다면 함수번호를 1이나 101로 바꾸면 됩니다.
=SUBTOTAL(101, C2:C100)부서별 인원수를 세고 싶을 때는 102번을 씁니다.
=SUBTOTAL(102, A2:A100)한 가지 주의할 점은, SUBTOTAL로 이미 만든 소계 행 위에 다시 SUBTOTAL을 씌우면 그 소계 행은 자동으로 계산에서 제외된다는 점입니다. 이 특성 덕분에 부서별로 중간중간 SUBTOTAL 소계를 넣고 표 맨 아래에 전체 합계를 SUBTOTAL로 한 번 더 넣어도, 중복으로 두 번 더해지는 일이 생기지 않습니다.
엑셀 표(Ctrl+T)와 함께 쓰면 더 편합니다
엑셀 표 기능을 적용한 상태에서 표 하단의 “요약 행”을 켜면, 셀마다 SUBTOTAL 함수가 자동으로 들어갑니다. 함수를 직접 입력할 필요 없이 셀을 클릭하면 합계, 평균, 개수 중 원하는 계산 방식을 드롭다운으로 바로 고를 수 있습니다. 표 서식을 자동으로 지정하는 방법은 엑셀 표 서식 자동 지정하는 법 글에서 더 자세히 다뤘습니다.
또한 필터 걸린 상태에서 조건부 서식으로 이상치나 중복값을 함께 확인하고 싶다면 엑셀 조건부 서식으로 데이터 이상치·중복 한눈에 찾기 글을 참고하시면 도움이 됩니다. 반대로 필터가 아니라 데이터 자체가 안 걸리는 문제라면 엑셀 필터 안될 때, 원인 4가지부터 확인하세요 글에서 원인을 먼저 점검해보시길 권합니다.
SUBTOTAL 대신 AGGREGATE를 써야 할 때도 있습니다
SUBTOTAL은 오류가 섞인 범위에서는 계산이 되지 않고 에러를 그대로 반환합니다. 만약 범위 안에 #N/A나 #DIV/0 같은 오류값이 섞여 있는데도 나머지 값들만 정상적으로 합산하고 싶다면 AGGREGATE 함수를 쓰는 것이 더 적합합니다. AGGREGATE는 함수번호와 함께 “옵션” 인수를 하나 더 받아서 오류값 무시, 숨겨진 행 무시 등을 세밀하게 조절할 수 있습니다. 다만 일반적인 필터 합계 용도로는 SUBTOTAL이 더 간단하고 널리 쓰입니다.
실무에서 자주 반복되는 건 지역별, 부서별, 담당자별로 필터를 바꿔가며 수치를 확인하는 작업입니다. SUBTOTAL 하나만 제대로 넣어두면 이 과정에서 수식을 매번 손댈 필요가 없어지고, 보고서나 대장 양식에 한 번만 세팅해두면 다음 담당자도 필터만 바꿔서 바로 쓸 수 있습니다. 이런 반복 집계 업무가 여러 시트나 여러 파일에 걸쳐 있어서 SUBTOTAL만으로는 한계가 느껴진다면, 픽셀앤코드에서는 엑셀 자동화부터 데이터 가공까지 실무 환경에 맞춰 함께 점검해드리고 있습니다.