엑셀 배열수식으로 다중 조건의 개수 구하기
질문
경주사랑학원의 수강생 현황을 작성한 표에서 성별과 과목별로 수강 인원을 구하려고 합니다.
배열 수식을 이용하여 조건이 여러 개일 때 개수를 구하는 방법이 궁금합니다.
답변
조건이 하나이면 COUNTIF 함수를 사용하는 것이 편리한 방법이겠지만 두 개 이상의 조건을 검사해야 할 경우 배열 수식을 이용하여 개수를 구합니다.
SUM함수를 이용하면 조건이 몇 개이든지 모든 조건을 만족하는 경우의 개수를 쉽게 구할 수 있습니다.
1. 아래 워크시트에서 D16:F17 영역은 성별과 과목별로 수강 인원을 구한 것입니다.
2. D16 셀은 수식 =SUM($D$3:$D$12=$C16)*($E$3:$E$12=D$15))를 입력한 다음 Ctrl + Shift + Enter 를 누른 것입니다. 그리고 D16 셀의 채우기 핸들을 D17 셀까지 끌고 다시 F17 셀까지 끌며 나머지 셀에 배열 수식을 복사했습니다.
- 개수를 구하는 배열 수식은 =SUM(조건1*조건2)의 형태로 되어 있습니다.
- 조건 1은 $D$3:$D$12=$C16으로 성별을 검사합니다. 주의할 점은 D3:D12 범위는 절대 주소로 참조하고 C16 셀은 열 부분만 $ 기호로 고정시킨 혼합 주소로 참조해야 한다는 것입니다.
수식을 아래로 복사할 경우 $C16은 $C17로 변합니다.
- 조건2는 $E$3:$E$12=D$15로 과목을 검사합니다.
E3:E12는 절대 주소로 참조하고 D15는 행 번호만 $ 기호로 고정시킨 혼합 주소로 참조합니다.
수식을 오른쪽으로 복사했을 때 D$15는 E$14, F$15로 변합니다.
- 조건1과 조건2의 결과는 각각 TRUE 또는 FALSE로 계산됩니다.
두 조건의 결과를 각각 곱하면 TRUE*TRUE 로 두 조건이 모두 참 일때만 1로 계산되고 나머지 경우는 모두 0으로 계산됩니다.
SUM 함수로 1 또는 0으로 계산된 값들을 모두 더하면 결국 개수가 됩니다.