엑셀함수 스페셜 : SUMPRODUCT 함수가 COUNTIFS함수보다 나은점

이전 글에서 2007에 들어와서 편리하게 사용할 수 있는 함수인 COUNTIFS함수를 배웠습니다. SUMPRODUCT함수에 비해서는 많이 간편해 졌습니다. 하지만 COUNTIFS함수가 항상 SUMPRODUCT함수보다 나은것은 아닙니다.

1. COUNTIFS함수는 조건에 맞는 셀의 개수만 셀 수 있지만, SUMPRODUCT함수는 셀의 개수를 셀 수 도 있고, 조건에 맞는 셀에 들어있는 값의 합계를 낼 수도 있다.

이전 시간에 COUNTIFS함수를 설명하면서 사용했던 표입니다. 여기서 국영수모두 80점 이상인 학생의 수를 구하는 식을 SUMPRODUCT함수로 만들면 다음과 같았습니다.

그런데 이 학생들의 총점합계를 알고 싶으면 어떻게 수식을 만들어야할까요? COUNTIFS함수는 셀의 개수만을 세기에, 셀의 합계를 낼 수는 없습니다. 나중에 설명하게될 SUMIFS함수를 사용해야합니다. 하지만, 개수를 구하는 위에 쓴 SUMPRODUCT함수를 약간만 수정하면 값의 합을 구하는 수식으로 사용할 수 있습니다. 즉, 다음과 같이 됩니다.

개수를 구하는 식에서 한가지 조건만이 추가되었습니다.

*($E$2:$E$11))

이 부분인데요, 범위와 비교연산자(부등호)가 함께 있었던 그 앞의 인수들 (예, ($B$2:$B$11>=80)) 과는 달리, 여기는 범위만 주어져 있습니다.

즉, SUMPRODUCT함수에서 인수를 범위와 비교연산자를 함께 주면, 그 조건에 맞는 개수를 구하라는 의미이고, 범위만 주면 해당 조건에 맞는 셀의 합을 구하라는 의미인것입니다.

SUMPRODUCT함수를 처음 배웠을때 이 원리를 알고서 서광이 비치는 느낌이었습니다. (^^*)

E열은 학생들의 총점을 보여주는 열이고, 앞의 다른 인수들과는 논리곱(*)으로 연결되어있기 때문에, 결국 전체 수식의 의미는
국어, 영어, 수학 점수가 모두 80점 이상인 학생들의 총점의 합계를 구하라는 의미가 됩니다. 개수를 구할때는 COUNTIFS함수, 합계를 구할때는 SUMIFS함수로 구분할 필요없이, SUMPRODUCT함수면 다 해결됩니다 ^^


2. SUMPRODUCT함수는 논리곱 외에 논리합도 표현할 수 있다.
다음과 같이 학생들의 주소록이 있습니다. 이 중에서 일원동, 이원동, 문정동에 사는 학생들의 수를 구하고 싶다면 어떻게 할까요?

=COUNTIFS($B$2:$B$11,"일원동",$B$2:$B$11,"문정동",$B$2:$B$11,"이원동")

으로 하면 값이 0으로 나옵니다. COUNTIFS함수는 논리곱으로 모든 인수가 연결되어있습니다. 즉, 각각의 셀이 주어진 조건을 모두 만족해야만 카운트를 하는 것입니다. 따라서 위의 수식은 일원동, 문정동, 이원동에 동시에 모두 살고 있는 학생의 수를 구하라는 의미입니다. 0이 나오는 것은 당연하겠지요.

따라서, 우리는 여기서 인수들간의 논리합을 구해야합니다. 논리합은 이전의 글에서 이야기했지만, 주어진 조건중 하나라도 만족하면 되는 것입니다.

논리합으로 인수들을 연결하고 싶으면 SUMPRODUCT함수를 사용합니다. 세 인수가 +로 연결되어있네요. 일원동, 문정동, 이원동중에서 한 곳에라도 살고 있으면 카운트합니다.

SUMPRODUCT함수를 사용하지 않다면, COUNTIFS가 아니라 COUNTIF함수를 사용하여 더해주는 방법이 있습니다.

3. COUNTIFS함수는 인수의 제약이 있지만, SUMPRODUCT함수는 적다

처음에는 제약이 "없다"라고 하려다가 그래도 뭔가 제약이 있는데 제가 모르는 것은 아닐까해서 수정했습니다.
위에서 본것과 비슷한 주소록인데, 앞에 시와 구의 이름이 붙어있습니다. 여기서 일원동 사는 학생과 문정동에 사는 학생의 수를 합한 숫자를 알고 싶습니다.

=COUNTIFS(MID($B$2:$B$11,8,3),"일원동",MID($B$2:$B$11,8,3),"문정동")
 
COUNTIFS함수를 써서 위와 같이 수식을 입력하면 수식에 오류가 있으니 고치라는 오류 메시지가 출력됩니다.

=SUMPRODUCT((MID($B$2:$B$11,8,3)="일원동")+(MID($B$2:$B$11,8,3)="문정동"))

위의 수식처럼 해야합니다.

다음에 설명하겠지만, 여기서 사용된 MID함수는 셀에서 몇 번째 위치에서 몇 글자를 출력하라는 함수입니다.
MID($B$2:$B$11,8,3) 는 주어진 범위(B2:B11)의 각 셀에서 8번째 위치에서 3글자를 출력하라는 의미입니다.

이처럼, COUNTIFS함수는 범위를 줄때 자유롭지 못합니다. 반드시 범위는 A1:A10의 형태로 주어야지, 범위안에 수식이 포함되어있으면 안됩니다.

* 언급한 내용들은 COUNTIFS에만 적용되는 것이 아닙니다. 다음에 설명하게 될 SUMIFS, AVERAGEIFS등에도 적용됩니다.

2008/10/10 - [컴퓨터/엑셀] - 인문 자연 섞여있는 표에서 계열별 순위산출하기
2009/04/05 - [컴퓨터/엑셀] - 1일1함수 (11) COUNTIF 함수
2009/04/15 - [컴퓨터/엑셀] - 1일1함수 (16) COUNTIFS함수 : 주어진 여러 조건에 맞는 셀의 개수 구하기
2009/04/05 - [컴퓨터/엑셀] - 1일1함수 (12) sumproduct함수 - 동점일때 다른 기준으로 순위매기기
2009/03/17 - [컴퓨터/엑셀] - 여러 조건에 맞는 셀의 평균 구하기 (AVERAGEIFS 함수)
2008/10/11 - [컴퓨터/엑셀] - sumproduct연구2
2008/03/16 - [컴퓨터/엑셀] - 두 가지 조건을 사용한 값 추출(sumproduct)
2009/03/28 - [컴퓨터/엑셀] - 1일1함수(7) : AND 함수 OR 함수 (논리곱과 논리합)