오류를 무시한 조건별 합계 공식
지원 버전 자세히 보기
조건에 맞는 값 중 오류 셀을 빼고 합산하는 공식입니다.
인수 설명
01모든 버전 호환 배열수식
{=SUM ( IF ( ( 조건범위=조건 ) * NOT ( ISERROR ( 합계범위 ) ), 합계범위, 0 ) )}인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
022021 이후 버전 공식
=SUM ( IFERROR ( FILTER ( 합계범위, 조건범위=조건 ), 0 ) )인수 설명 자세히 보기
동작 원리
01모든 버전 호환 배열수식
조건 비교 결과와 오류 검사 결과를 곱해 합산할 값만 남기고 SUM 함수가 합계를 계산합니다.
모든 버전 호환 배열수식의 예제 시트에서 G3 셀이 470을 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
조건과 같은 행을 찾습니다
A2:A8의 각 부서가 G2 셀의 영업팀과 같은지 비교합니다. 영업팀인 첫 번째, 세 번째, 다섯 번째, 여섯 번째 행이 TRUE입니다.
=A2:A8=G2
= {TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE}
영업팀인 행만 TRUE입니다. 오류가 없는 값을 표시합니다
ISERROR 함수는 D2:D8에서 오류가 있는 세 번째와 일곱 번째 값을 TRUE로 반환하고, NOT 함수가 이를 반대로 바꿉니다.
=NOT ( ISERROR ( D2:D8 ) )
= {TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE}
오류가 없는 값만 TRUE입니다. IF 함수가 합산할 값만 남깁니다
두 배열을 곱하면 조건을 만족하면서 오류가 없는 첫 번째, 다섯 번째, 여섯 번째 행만 1이 됩니다. IF 함수는 해당 행의 금액을 남기고 나머지는 0으로 바꿉니다.
=IF ( ( A2:A8=G2 ) * NOT ( ISERROR ( D2:D8 ) ), D2:D8, 0 )
= {120;0;0;0;150;200;0}
조건과 오류 검사를 모두 통과한 값입니다. SUM 함수가 값을 합산합니다
SUM 함수는 남은 값 120, 150, 200을 더해 조건별 합계를 계산합니다.
{=SUM ( IF ( ( A2:A8=G2 ) * NOT ( ISERROR ( D2:D8 ) ), D2:D8, 0 ) )}
= 470
120+150+200=470입니다. 022021 이후 버전 공식
FILTER 함수는 조건에 맞는 값을 추리고, IFERROR 함수가 오류를 0으로 바꾼 뒤 SUM 함수가 합산합니다.
2021 이후 버전 공식의 예제 시트에서 G3 셀이 470을 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
조건과 같은 행을 찾습니다
A2:A8의 각 부서가 G2 셀의 영업팀과 같은지 비교합니다. 영업팀인 첫 번째, 세 번째, 다섯 번째, 여섯 번째 행이 TRUE입니다.
=A2:A8=G2
= {TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE}
영업팀인 행만 TRUE입니다. FILTER 함수가 조건에 맞는 값을 추립니다
FILTER 함수는 조건 배열이 TRUE인 행의 계산금액을 원래 순서대로 반환합니다. 조건에 맞더라도 오류 셀은 이 단계에서 그대로 남습니다.
=FILTER ( D2:D8, A2:A8=G2 )
= {120;#DIV/0!;150;200}
영업팀 행의 계산금액입니다. IFERROR 함수가 오류를 0으로 바꿉니다
IFERROR 함수는 FILTER 함수가 반환한 배열의 #DIV/0! 오류를 0으로 바꿉니다.
=IFERROR ( FILTER ( D2:D8, A2:A8=G2 ), 0 )
= {120;0;150;200}
오류가 0으로 바뀐 배열입니다. SUM 함수가 값을 합산합니다
SUM 함수는 오류가 제거된 네 값을 더해 조건별 합계를 계산합니다.
=SUM ( IFERROR ( FILTER ( D2:D8, A2:A8=G2 ), 0 ) )
= 470
120+0+150+200=470입니다. 