엑셀 SUMPRODUCT 함수로 다중 조건일때 개수와 합계구하기

Question

사원의 이름, 성별, 부서, 실적이 입력되어 있는 목록이 있습니다.

사원의 부서와 성별에 따라 실적의 합계를 구하려고 하는데, SUMIF함수로는 조건이 두 개이므로 구할 수가 없습니다. 배열 수식을 사용하지 않고 조건이 여러개 일때 합계를 구하는 방법이 있나요?

 

Answer

있습니다. SUMPRODUCT 함수를 사용하면 됩니다.

SUMPRODUCT 함수는 인수로 지정한 셀 범위의 각 셀을 곱한 다음 곱한 값들의 합계를 구할 때 사용합니다. 예를 들어 수식 =SUMPRODUCT(A1:A3, B1:B3)은 A1:A3의 각 셀에 1,2,3이 입력되어 있고 B1:B3의 각 셀에 4,5,6이 입력되어 있을 때 1*4+2*5+3*6으로 계산됩니다.

이 함수를 응용하면 조건이 여러 개일 때 합계를 구할 수 있습니다.

 

다중 조건.xlsx

0.01MB

차근 차근 해보기

1. 아래의 워크시트에 B2:E12 영역은 첫 행에 입력된 텍스트가 각 열의 이름으로 정의되어 있습니다.

이름 부서는 D3:D12 영역을 의미하고 이름 성별은 C3:C12 이름 실적은 E3:E12 영역을 의미합니다.

2. I3:I6 영역에는 부서와 성별로 인원을 구하기 위해 수식 =SUMPRODUCT((부서=G3)*(성별=H3))이 입력되어 있습니다. =SUMPRODUCT(조건1*조건2)형식으로 조건1(부서 검사)과 조건2(성별 검사)가 모두 참(TRUE)일 때 1*1, 두 조건 중 하나라도 거짓(FALSE)이면 1*0 또는 0*1로 계산됩니다.

계산 결과로 나온 1또는 0을 모두 더하면 두 조건을 만족하는 경우의 개수(인원)을 구할 수 있습니다.

3. J3:J6 영역은 부서와 성별로 실적 합계를 구하기 위해 수식 =SUMPRODUCT((부서=G3)*(성별=H3),실적)이 입력되어 있습니다.

=SUMPRODUCT(조건1*조건2),실적) 형식으로 조건1과 조건 2가 모두 참 일때만 1*실적이 되어 부서와 성별이 모두 맞을 때 실적 합계를 구합니다.