조건별 보이는 셀 합계와 개수 공식
지원 버전 자세히 보기
엑셀 2013 이전엑셀 2016엑셀 2019엑셀 2021엑셀 2024M365웹 엑셀
필터하거나 숨긴 행을 빼고 화면에 보이는 행만 조건별로 합계·개수를 구하는 공식입니다. SUMPRODUCT 함수와 SUBTOTAL 함수로 조건과 화면 표시 상태를 함께 판정합니다.
인수 설명
=SUMPRODUCT ( --( 조건범위=조건 ) * SUBTOTAL ( 103, OFFSET ( 첫번째셀, ROW ( 집계범위 ) - ROW ( 첫번째셀 ), 0, 1 ) ) )
=SUMPRODUCT ( --( 조건범위=조건 ) * SUBTOTAL ( 109, OFFSET ( 첫번째셀, ROW ( 집계범위 ) - ROW ( 첫번째셀 ), 0, 1 ) ) )
인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
인수구분설명
조건범위필수조건을 비교할 값이 입력된 범위입니다. 집계범위와 같은 행 수로 지정합니다.
조건필수조건범위에서 찾을 값이 입력된 셀입니다.
집계범위필수개수 또는 합계를 계산할 값이 입력된 범위입니다.
첫번째셀필수집계범위의 맨 위 셀입니다.
A
B
C
D
E
F
G
1
매장
매출
화면 상태
집계 결과
값
2
서울점
120
표시
서울점 개수
2
3
부산점
90
표시
서울점 합계
200
4
서울점
150
숨김
5
대전점
110
표시
조건
서울점
6
서울점
80
표시
7
부산점
130
숨김
8
서울점
200
숨김
9
이 공식이 사용하는 함수
동작 원리
ROW 함수와 OFFSET 함수가 행별 참조를 만들고 SUBTOTAL 함수가 숨긴 행을 0으로 바꾼 뒤 SUMPRODUCT 함수가 조건에 맞는 값만 집계합니다.
조건별 보이는 셀 집계의 예제 시트에서 F2 셀이 2, F3 셀이 200을 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
1
ROW 함수가 첫 셀부터의 간격을 계산합니다
ROW 함수는 B2:B8의 행 번호에서 첫번째셀 B2의 행 번호를 빼서 0부터 6까지의 간격을 반환합니다.
=ROW ( B2:B8 ) - ROW ( B2 )
= {0;1;2;3;4;5;6}
B2에서 각 집계 셀까지 이동할 행 수입니다. 2
SUBTOTAL 함수가 보이는 셀의 개수 배열을 만듭니다
OFFSET 함수는 B2에서 간격만큼 내려가 B2:B8을 한 셀씩 참조합니다. SUBTOTAL 함수의 103은 보이는 비어 있지 않은 셀을 1, 숨긴 4·7·8행을 0으로 반환합니다.
=SUBTOTAL ( 103, OFFSET ( B2, {0;1;2;3;4;5;6}, 0, 1 ) )
= {1;1;0;1;1;0;0}
표시된 행은 1, 숨긴 행은 0인 배열입니다. 3
SUBTOTAL 함수가 보이는 셀의 값 배열을 만듭니다
SUBTOTAL 함수의 109는 보이는 셀의 값을 그대로 반환하고 숨긴 행은 0으로 반환합니다. 따라서 합계 계산에는 화면에 남은 매출만 전달됩니다.
=SUBTOTAL ( 109, OFFSET ( B2, {0;1;2;3;4;5;6}, 0, 1 ) )
= {120;90;0;110;80;0;0}
표시된 행의 매출만 남긴 배열입니다. 4
SUMPRODUCT 함수가 조건에 맞는 결과를 집계합니다
조건범위가 서울점과 같은 행은 {1;0;1;0;1;0;1}입니다. 이 배열에 보이는 셀 개수 또는 값을 곱하면 서울점이면서 화면에 보이는 두 행만 남습니다.
=SUMPRODUCT ( --( A2:A8=F5 ) * SUBTOTAL ( 103, OFFSET ( B2, ROW ( B2:B8 ) - ROW ( B2 ), 0, 1 ) ) )
= 2
{1;0;0;0;1;0;0}의 합계입니다. =SUMPRODUCT ( --( A2:A8=F5 ) * SUBTOTAL ( 109, OFFSET ( B2, ROW ( B2:B8 ) - ROW ( B2 ), 0, 1 ) ) )
= 200
{120;0;0;0;80;0;0}의 합계입니다. ![[도구 업데이트] 투명 인감 도장 PNG를 만들어보세요!](https://www.oppadu.com/wp-content/uploads/2026/09/투명-도장-생성기-안내.png)
![[직원 채용] 오빠두엑셀이 정규직 동료를 모십니다](https://www.oppadu.com/wp-content/uploads/2026/08/직원-채용-1_R.png)
아직 자세히 들여다 보질 못해서 이해 못하고 있었습니다.
이제 원리를 이해할 수 있겠군요.
연습 많이해 보겠습니다.
그런데, 눈에 보이는 셀의 조건별 합계를 구하는 공식을 다른 사이트에서 찾아서 적용해 본 결과, 동일한 결과가 도출되는데, 아래 함수에서 첫번째 인수에 "+0"을 붙인 게 "--"랑 같은 역할을 하나요?
=SUMPRODUCT((criteriarange=criteria)+0,SUBTOTAL(109,OFFSET(sumrange,ROW(sumrange)-MIN(ROW(sumrange)),0,1,1)))
네 맞습니다. -- 와 동일한 역할을 합니다.
신촌이 들어간 단어를 찾으라고 할땐 어찌해야되나요 ?
”*”&신촌&”*” 으로 해보았는데 제대로 카운터를 못하네요..
본 수식은 배열수식이므로 특정단어 포함 조건을 아래와 같이 작성해주셔야 합니다. 아래 공식을 활용해보세요
( ISNUMBER(FIND("신촌",조건범위)) )
Subtotal 함수 계산방식에 대한 자세한 설명은 아래 링크를 한번 확인해보시겠어요?:) 감사합니다.
https://www.oppadu.com/엑셀-subtotal-함수/
혹시 구글에서 해당 함수를 사용하려면 어떻게 해야될까요?
본 수식은 엑셀에서만 잘 동작하고 구글시트에서는 사용할 수 없습니다.
형태로 조건1과 조건2에 신촌점과 홍대점을 입력해서 사용해보세요.
엑셀 2021 / M365 이전 버전에서는 배열이 동적으로 반환되지 않기때문에, 배열이 반환되는 넓은 범위를 우선 선택한 후, Ctrl +Shift + Enter로 수식을 입력해야 합니다.
자세한 내용은 아래 영상 강의를 참고하세요.
https://www.oppadu.com/%EC%A7%84%EC%A7%9C%EC%93%B0%EB%8A%94-%EC%8B%A4%EB%AC%B4%EC%97%91%EC%85%80-7-4-1/
감사합니다.