엑셀 숨긴 셀 제외하고 합계 구하는 방법

엑셀에서 데이터를 정리하다 보면 필터를 걸어서 특정 행을 숨기고 보게 되는 경우가 많습니다. 하지만 기본적인 SUM 함수를 사용하면 숨긴 셀까지 포함하여 합계를 계산하게 되는데요, 숨긴 셀을 제외하고 합계를 구하려면 어떻게 해야 할까요?

 

오늘은 SUBTOTAL 함수에 대해 소개하고, 엑셀에서 숨긴 셀의 데이터를 제외하고 합계를 쉽게 구하는 방법에 대해 알려드릴게요!

---

1. SUM 함수의 한계 : 숨긴 셀도 다 더해버리는 함수

📌 SUM 함수란?

SUM 함수는 지정한 범위의 숫자를 모두 더하는 함수입니다.

 

🔹 SUM 함수 기본 기본 수식

=SUM(범위)
*대/소문자 상관없음

 

🔹 특징

✔︎ 선택한 범위 내의 모든 숫자를 더함

✔︎ 필터로 숨김처리한 데이터값까지도 합산됨

 

🔹 예시

만약 A1:A10 범위에 숫자가 입력되어 있다면,

=SUM(A1:A10)

이렇게 입력하면 A1부터 A10까지의 숫자의 합계가 계산됩니다. 실제 사례와 함께 설명해 볼게요.

 

 

위와 같이, 첫 번째 행부터 맨 마지막 행까지 드래그한 후 SUM 함수를 걸어 주면

 

 

모든 숫자의 합계인 32,500,000원이 계산됩니다.

 

그런데, 여기서 1월 지출액만 계산하기 위해 [지출월]에 필터를 걸어 볼게요.

 

 

 

 

분명히 1월에 필터가 걸려 있고, 그렇다면 5백만 원+3백만 원+8백만 원의 합계가 더해져야 하는데

숨긴 행의 숫자까지 다 더해져 버려서, 32,000,000원이 나와 버립니다. 이렇게 되면, 우리는 하나하나 클릭을 해 가며 합계를 더해서 구해야 할까요?

 

물론, 1월 텍스트 부분을 오름차순 or 내림차순으로 정렬해서 더하거나 피벗을 걸 수도 있지만 오늘은 그렇게 하지 않고도 아주 쉽고 빠르게 숨긴 셀을 제외하고 합계를 구할 수 있는 SUBTOTAL 함수에 대해 알려 드릴게요!

---

2. 숨긴 셀은 제외하고 더해주는 SUBTOTAL 함수 활용법

📌 SUBTOTAL 함수란?

SUBTOTAL 함수는 필터로 숨긴 데이터는 제외하고 합계를 구할 수 있는 함수입니다.

 

🔹 SUBTOTAL 함수 기본 수식:

=SUBTOTAL(109, 범위)

여기서 109라는 숫자는, SUBTOTAL 함수 중에서도 숨긴 셀은 제외하고 더할 수 있는 수식입니다. (SUBTOTAL 함수에 종류가 많거든요!)

---

3. SUBTOTAL 함수 활용해서 숨긴 셀 제외하고 합 구해보기

그럼 SUBTOTAL 함수 활용 예시를 함께 볼게요.

좀 전 2번 예시에서 위 이미지처럼, SUM 함수를 사용했더니, 숨긴 셀까지 다 더해져서 결괏값이 32,000,000원이 되었던 거 기억하시나요? 이제 동일한 조건에서 SUM이 아닌 SUBTOTAL 함수를 사용해 볼게요.

 

 

=SUBTOTAL(109, 셀 범위)를 선택해 주었습니다. 이제 엔터를 눌러볼게요.

 

 

위와 같이, 숨긴 셀은 제외한 보여지는 3개 셀의 합만 구해집니다. 여기서 필터를 해제하면, 모든 값이 다시 포함되어 계산됩니다. 정말 유레카 아닌가요? ㅎㅎㅎ 저는 몇년 전, 이 함수를 처음 발견했을때 얼마나 반가웠는지 몰라요! 역시 엑셀은 없는 함수가 없는 똑똑이입니다. (엑셀 맹신자)

---

4. SUM 함수와 SUBTOTAL 함수 비교

합계를 구하는 두 개의 함수인 SUM / SUBTOTAL 함수를 비교해 보면 이렇습니다.

함수	수식	필터 숨긴값 더하기
SUM	=SUM(A1:A10)	❌ 필터를 걸어 셀을 숨겨도, A1부터 A10까지숨겨진 셀까지의 모든 합이 계산됨
SUBTOTAL	=SUBTOTAL(109,A1:A10)	⭕️ 필터를 걸어 셀을 숨기면, A1부터 A10까지 중에서 필터에 걸려있는 숫자의 합만 계산됨

---

[Summary]

✅ SUM 함수는 숨긴 셀을 포함하여 합계를 계산합니다.

✅ 필터로 숨긴 데이터만 제외하고 합계를 구하려면 SUBTOTAL(109, 범위)를 사용합니다.

 

앞으로는 엑셀에서 데이터를 정리할 때 숨긴 데이터를 포함할지, 제외할지를 고려해서 위 두 함수를 적절히 활용해 원하는 값을 계산해 보세요!