좋은정보모아

엑셀 SUBTOTAL 함수 1분이면 끝! 필터 통계(합계·평균·개수) 이렇게 쉬웠어?

2026-07-05 · 교육 · 읽는데 5분

엑셀에서 필터를 걸었는데 SUM 함수로 구한 합계가 변하지 않아 당황한 적이 있으신가요? 필터로 데이터를 걸러내도 SUM 함수는 숨겨진 셀까지 모두 포함해 계산하기 때문입니다. 이럴 때 SUBTOTAL 함수를 사용하면 화면에 보이는 데이터만 골라 합계, 평균, 개수 등을 바로 구할 수 있습니다. 사용법은 생각보다 간단해서 1분이면 익힐 수 있습니다.

필터 걸린 데이터만 합계를 구해야 할 때, SUM 말고 이걸 써야 하는 이유

필터를 적용하면 원하는 조건의 데이터만 화면에 보여줍니다. 그런데 엑셀의 기본 SUM 함수는 숨겨진 행까지 전부 더해버리기 때문에, 화면에 보이는 값들의 합계와 일치하지 않습니다. 예를 들어 '서울' 지역 주문만 필터링했는데도 전체 판매 금액이 그대로 표시되는 식이죠.

SUBTOTAL 함수는 이런 상황을 정확히 해결해 줍니다. 필터로 가려진 행이나 수동으로 숨긴 행을 제외하고, 오직 눈에 보이는 데이터만 계산 대상으로 삼습니다. SUM 외에도 평균, 개수, 최대값, 최소값 등 11가지 계산 방식을 지원합니다.

SUBTOTAL 함수의 핵심, function_num만 알면 끝

SUBTOTAL 함수의 구조는 다음과 같습니다.

=SUBTOTAL(function_num, ref1, ...)

첫 번째 인수인 function_num이 어떤 계산을 할지 결정합니다. 1부터 11까지의 숫자(또는 101부터 111까지)를 넣으면 됩니다.

  • 1 (또는 101): AVERAGE (평균)
  • 2 (또는 102): COUNT (숫자 개수)
  • 3 (또는 103): COUNTA (비어 있지 않은 셀 개수)
  • 4 (또는 104): MAX (최대값)
  • 5 (또는 105): MIN (최소값)
  • 6 (또는 106): PRODUCT (곱)
  • 7 (또는 107): STDEV (표본 표준편차)
  • 8 (또는 108): STDEVP (모집단 표준편차)
  • 9 (또는 109): SUM (합계)
  • 10 (또는 110): VAR (표본 분산)
  • 11 (또는 111): VARP (모집단 분산)

여기서 1-11번과 101-111번의 차이는 숨겨진 행 처리 방식에 있습니다. 1-11번은 필터로 숨겨진 행은 제외하지만, 수동으로 숨긴 행은 포함합니다. 반면 101-111번은 필터와 수동 숨김 모두 적용하여 숨겨진 모든 행을 제외합니다. 대부분 필터용으로는 9(SUM) 또는 1(AVERAGE)을 쓰면 됩니다.

두 번째 인수 ref1은 계산할 범위를 지정합니다. 예를 들어 =SUBTOTAL(9, D2:D100)이라고 입력하면 D2부터 D100까지의 범위에서 필터링된 셀의 합계를 구합니다.

실제로 따라 해 보는 SUBTOTAL 적용법

  1. 합계를 표시할 셀(예: I4)을 선택합니다.
  2. =SUBTOTAL(9, 까지 입력합니다. (9는 SUM 기능)
  3. 합계를 구할 판매 금액 열의 전체 범위(예: I2:I100)를 마우스로 드래그하여 선택합니다.
  4. 괄호를 닫고 Enter 키를 누릅니다. 완성된 수식은 =SUBTOTAL(9, I2:I100)과 같은 형태가 됩니다.
  5. 이제 데이터 탭에서 필터를 적용하고, 지역 열에서 '서울'만 선택해 보세요. 합계 값이 필터링된 결과에 맞춰 즉시 변하는 것을 확인할 수 있습니다.

참고로 표가 길 경우, 합계 셀을 표 상단에 두면 필터를 변경할 때마다 스크롤 없이 값을 바로 확인할 수 있어 편리합니다.

필터링된 상태에서 순번 자동 매기기

SUBTOTAL 함수는 순번을 매기는 용도로도 자주 쓰입니다. 필터를 걸면 화면에 보이는 순서대로 1, 2, 3... 번호를 다시 매겨야 할 때가 있죠. 이때는 function_num에 3(COUNTA)을 사용합니다.

  1. 순번을 표시할 첫 번째 셀(예: A2)에 =SUBTOTAL(3, $A$2:A2)라고 입력합니다.
  2. 셀의 채우기 핸들을 더블클릭하거나 아래로 드래그하여 나머지 셀에 수식을 복사합니다.
    • 여기서 $A$2:A2처럼 범위의 첫 번째 셀을 절대 참조로 고정하는 것이 중요합니다.
  3. 필터를 적용하면 화면에 보이는 행만 1부터 순서대로 번호가 매겨집니다. 필터 조건을 바꾸면 번호도 자동으로 다시 계산됩니다.

SUBTOTAL 함수 FAQ

Q. SUBTOTAL과 SUM의 차이가 무엇인가요?

A. SUM은 지정한 범위의 모든 셀을 더합니다. 필터로 숨겨진 데이터도 포함됩니다. 반면 SUBTOTAL은 기본적으로 필터링되어 보이지 않는 데이터는 계산에서 제외합니다. function_num을 101-111로 설정하면 수동으로 숨긴 행까지도 제외합니다.

Q. SUBTOTAL 함수는 다른 함수 안에 중첩해서 쓸 수 있나요?

A. SUBTOTAL 함수는 다른 SUBTOTAL 함수의 결과값을 계산에 포함하지 않습니다. 이중으로 집계되는 것을 방지하기 위한 설계입니다. 따라서 SUBTOTAL 결과를 IF나 VLOOKUP 같은 다른 함수 안에서 인수로 사용하는 것은 가능하지만, SUBTOTAL 안에 또 다른 SUBTOTAL을 넣는 것은 권장되지 않습니다.

Q. 필터를 해제하면 SUBTOTAL 결과값은 어떻게 되나요?

A. 필터를 해제하면 모든 행이 다시 화면에 보이게 되므로, SUBTOTAL 함수는 전체 데이터를 대상으로 다시 계산합니다. 즉, SUM 함수로 구한 값과 동일한 결과를 보여줍니다.