같은 열에서 여러 조건을 만족하는 개수 구하기

다음과 같은 자료에서 '사과'의 개수를 구하려면 COUNTIF 함수를 사용하면 됩니다.

=COUNTIF(B3:B14,"사과") → 3

 

만약 '사과' 또는 '바나나'의 개수를 구하려면 어떻게 해야 할까요? 여러 조건을 만족하는 것의 개수를 구하는 COUNTIFS 함수를 사용하면 될까요?

COUNTIFS 함수는 다음과 같이 각각 다른 열에 있는 값의 조건을 COUNT하므로 위와 같이 같은 열에서 여러 조건을 만족하는 것의 개수를 구할 수는 없습니다.

다음과 같이 같은 열을 조건으로 해서 개수를 구하면 0이 나옵니다. 같은 열에서 '사과'이면서  '바나나'인 것은 없으므로 결과는 당연히 0이 나옵니다.

=COUNTIFS(B3:B14,"사과",B3:B14,"바나나") → 0

 

COUNTIF 함수를 여러 번 사용

같은 열에서 '사과' 또는 '바나나'인 과일의 개수를 구하려면 COUNTIF 함수로 '사과'의 개수와 '바나나'의 개수를 각각 더하면 됩니다.

[D4] 셀에 다음 수식을 입력합니다.

=COUNTIF(B3:B14,"사과")+COUNTIF(B3:B14,"바나나")

 

다음과 같이 '사과'의 개수 3, '바나나'의 개수 2를 더해서 5가 구해집니다.

이렇게 간단하게 구할 수 있지만 COUNT하려는 항목의 개수가 많아지면 수식이 길어지고 복잡해지는 문제가 있으니 배열수식으로 구해보겠습니다.

 

 

배열수식 사용

COUNTIF 함수를 여러 번 사용하는 것보다 SUMPRODUCT 함수를 사용한 배열수식을 사용하면 간단히 한번에 구할 수 있습니다.

[D7] 셀에 다음 수식을 입력합니다.

=SUMPRODUCT(COUNTIF(B3:B14,{"사과","바나나"}))

 

다음과 같이 [B3:B14] 범위에서 '사과' 또는 '바나나'인 과일의 개수 5가 구해집니다.

 

배열 수식이라서 이해하기 쉽지 않습니다.

수식이 계산되는 단계를 살펴보겠습니다.

위 시트에서 [D7] 셀을 선택한 후 리본 메뉴 [수식] 탭 → [수식 분석]그룹 → [수식 계산]을 누르면...

 

다음과 같이 [수식 계산] 대화 상자가 나타납니다.

<계산> 버튼을 누르면 밑줄이 그어진 부분이 계산이 됩니다.

 

COUNTIF 함수가 사과의 개수 3, 바나나의 개수 2를 구해주고 수식은 다음과 같이 바뀝니다.

=SUMPRODUCT({3,2})

<계산> 버튼을 누릅니다.

 

숫자를 곱하고 합계를 구해주는 SUMPRODUCT 함수가 3과 2를 더해서 5를 구해줍니다.

배열 수식은 복잡해서 이해하기 어려운 단점이 있지만 다음과 같이 계산 조건이 계속 추가될 경우 사용하면 간단히 구할 수 있는 장점이 있습니다.

'사과' 또는 '바나나' 또는 '망고'인 것의 개수 구하기
=SUMPRODUCT(COUNTIF(B3:B14,{"사과","바나나","망고"})) → 7

 

 

배열수식 사용 - 조건을 참조하여 계산

수식에 {"사과","바나나"}와 같이 배열을 입력할 수도 있지만 다음과 같이 범위에 조건을 입력하고 참조하는 방식으로도 사용할 수 있습니다.

[D4] 셀에 다음 수식을 입력합니다.

=SUMPRODUCT(COUNTIF(B3:B14,D7:D8))

 

다음과 같이 [B3:B14] 범위에서 '사과' 또는 '바나나'인 과일의 개수 5가 구해집니다.

 

 

---

관련 글