메뉴
실무 위키›응용 공식›오류를 무시한 조건별 합계 공식

오류를 무시한 조건별 합계 공식

지원 버전 자세히 보기
엑셀 2013 이전엑셀 2016엑셀 2019엑셀 2021엑셀 2024M365웹 엑셀

조건에 맞는 값 중 오류 셀을 빼고 합산하는 공식입니다.

예제파일 다운로드
오류를 무시한 조건별 합계 공식 예제 미리보기
예제 미리보기 크게 보기

인수 설명

01SUM + IF 배열 수식 (모든 버전)

{=SUM ( IF ( ( 조건범위=조건 ) * NOT ( ISERROR ( 합계범위 ) ), 합계범위, 0 ) )}
인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
인수구분설명
조건범위필수조건을 검사할 범위입니다. 합계범위와 같은 크기여야 합니다.
조건필수조건범위에서 찾을 조건입니다.
합계범위필수조건을 만족할 때 합산할 숫자 범위입니다. 오류 셀은 계산에서 제외됩니다.
D2=B2/C2
G3{=SUM(IF(($A$2:$A$8=$G$2)*NOT(ISERROR($D$2:$D$8)),$D$2:$D$8,0))}
A
B
C
D
E
F
G
H
1
부서
금액
나눗값
계산금액
검색 조건
입력값
2
영업팀
120
1
120
부서
영업팀
3
개발팀
80
1
80
합계
470
4
영업팀
1
0
#DIV/0!
5
인사팀
90
1
90
6
영업팀
150
1
150
7
영업팀
200
1
200
8
개발팀
1
0
#DIV/0!
9
영업팀 행에서 D4 셀의 #DIV/0! 오류를 제외하면 120+150+200=470이므로 G3 셀은 470을 반환합니다. 엑셀 2019 이하에서는 Ctrl+Shift+Enter로 입력합니다.

02FILTER 함수 활용 (엑셀 2021 이후)

=SUM ( IFERROR ( FILTER ( 합계범위, 조건범위=조건 ), 0 ) )
인수 설명 자세히 보기
인수구분설명
합계범위필수조건을 만족할 때 합산할 숫자 범위입니다. 오류 셀은 계산에서 제외됩니다.
조건범위필수조건을 검사할 범위입니다. 합계범위와 같은 크기여야 합니다.
조건필수조건범위에서 찾을 조건입니다.
D2=B2/C2
G3=SUM(IFERROR(FILTER($D$2:$D$8,$A$2:$A$8=$G$2),0))
A
B
C
D
E
F
G
H
1
부서
금액
나눗값
계산금액
검색 조건
입력값
2
영업팀
120
1
120
부서
영업팀
3
개발팀
80
1
80
합계
470
4
영업팀
1
0
#DIV/0!
5
인사팀
90
1
90
6
영업팀
150
1
150
7
영업팀
200
1
200
8
개발팀
1
0
#DIV/0!
9
FILTER 함수가 영업팀의 계산금액을 추리고 IFERROR 함수가 #DIV/0! 오류를 0으로 바꾼 뒤 SUM 함수가 G3 셀에 470을 반환합니다.
이 공식이 사용하는 함수
SUM 함수 IF 함수 NOT 함수 ISERROR 함수 IFERROR 함수 FILTER 함수

동작 원리

01SUM + IF 배열 수식 (모든 버전)

조건 비교 결과와 오류 검사 결과를 곱해 합산할 값만 남기고 SUM 함수가 합계를 계산합니다.

SUM + IF 배열 수식의 예제 시트에서 G3 셀이 470을 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.

1

조건과 같은 행을 찾습니다

A2:A8의 각 부서가 G2 셀의 영업팀과 같은지 비교합니다. 영업팀인 첫 번째, 세 번째, 다섯 번째, 여섯 번째 행이 TRUE입니다.

=A2:A8=G2
= {TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE} 영업팀인 행만 TRUE입니다.
2

오류가 없는 값을 표시합니다

ISERROR 함수는 D2:D8에서 오류가 있는 세 번째와 일곱 번째 값을 TRUE로 반환하고, NOT 함수가 이를 반대로 바꿉니다.

=NOT ( ISERROR ( D2:D8 ) )
= {TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE} 오류가 없는 값만 TRUE입니다.
3

IF 함수가 합산할 값만 남깁니다

두 배열을 곱하면 조건을 만족하면서 오류가 없는 첫 번째, 다섯 번째, 여섯 번째 행만 1이 됩니다. IF 함수는 해당 행의 금액을 남기고 나머지는 0으로 바꿉니다.

=IF ( ( A2:A8=G2 ) * NOT ( ISERROR ( D2:D8 ) ), D2:D8, 0 )
= {120;0;0;0;150;200;0} 조건과 오류 검사를 모두 통과한 값입니다.
4

SUM 함수가 값을 합산합니다

SUM 함수는 남은 값 120, 150, 200을 더해 조건별 합계를 계산합니다.

{=SUM ( IF ( ( A2:A8=G2 ) * NOT ( ISERROR ( D2:D8 ) ), D2:D8, 0 ) )}
= 470 120+150+200=470입니다.

02FILTER 함수 활용 (엑셀 2021 이후)

FILTER 함수는 조건에 맞는 값을 추리고, IFERROR 함수가 오류를 0으로 바꾼 뒤 SUM 함수가 합산합니다.

2021 이후 버전 공식의 예제 시트에서 G3 셀이 470을 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.

1

조건과 같은 행을 찾습니다

A2:A8의 각 부서가 G2 셀의 영업팀과 같은지 비교합니다. 영업팀인 첫 번째, 세 번째, 다섯 번째, 여섯 번째 행이 TRUE입니다.

=A2:A8=G2
= {TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE} 영업팀인 행만 TRUE입니다.
2

FILTER 함수가 조건에 맞는 값을 추립니다

FILTER 함수는 조건 배열이 TRUE인 행의 계산금액을 원래 순서대로 반환합니다. 조건에 맞더라도 오류 셀은 이 단계에서 그대로 남습니다.

=FILTER ( D2:D8, A2:A8=G2 )
= {120;#DIV/0!;150;200} 영업팀 행의 계산금액입니다.
3

IFERROR 함수가 오류를 0으로 바꿉니다

IFERROR 함수는 FILTER 함수가 반환한 배열의 #DIV/0! 오류를 0으로 바꿉니다.

=IFERROR ( FILTER ( D2:D8, A2:A8=G2 ), 0 )
= {120;0;150;200} 오류가 0으로 바뀐 배열입니다.
4

SUM 함수가 값을 합산합니다

SUM 함수는 오류가 제거된 네 값을 더해 조건별 합계를 계산합니다.

=SUM ( IFERROR ( FILTER ( D2:D8, A2:A8=G2 ), 0 ) )
= 470 120+0+150+200=470입니다.
댓글 0
아직 댓글이 없습니다. 첫 번째 댓글을 작성해보세요!
스크랩 완료