엑셀 SUMPRODUCT 함수의 기본 완전정복!

여러분은 엑셀에서 유용하게 쓰시는 함수가 무엇인가요? 대표적으로 IF나 SUMIF 같은 것이 있습니다.

그런데, 그만큼 실무에서 유용하게 쓰이는 함수인 SUMPRODUCT 함수를 아시나요?

​

이번 포스팅에서는 이 SUMPRODUCT 함수의 기초를 완전 정복해 보는 시간을 한번 가져 보겠습니다.


오늘의 자료는 각 제품의 가격과 개수가 쓰여 있는 표입니다.

오른쪽에 있는 '총합' 란에 모든 제품의 가격의 총합을 구하려고 합니다.

​

단순히 수식을 이용하면 =B3*C3+B4*C4+...+B7*C7 로 하면 되지만, 데이터가 수십 개를 넘어가는 경우 이렇게 하는 방법은 그저 단순 노동에 불과합니다. 더 좋은 방법이 없을까요?

​

그 방법이 바로 오늘 소개할 엑셀 SUMPRODUCT 함수입니다.

​

수식: =SUMPRODUCT(범위1, 범위2, ...)

기능: 크기와 모양이 같은 여러 범위에서 대응되는 셀끼리의 곱의 합을 구합니다.

​

그냥 단순한 설명으로는 이해하기 어렵지만 실제 예시로 이해하면 이해하기 쉬운 함수에 속하니, 바로 예를 들어 설명하겠습니다.


위 그림에서 =SUMPRODUCT(B2:B6, C2:C6) 수식의 결과값은 다음과 같이 계산할 수 있습니다.

=B2*C2+B3*C3+B4*C4+B5*C5+B6*C6

잘 보면, 크기와 모양이 서로 같은 두 범위 B2:B6과 C2:C6의 서로 대응되는 셀인 B2와 C2의 곱, B3과 C3의 곱, ..., B6과 C6의 곱의 합을 나타내는 수식임을 알 수 있습니다. 이 수식을 계산하면 결과값은 130이 됩니다.

​

즉, =A1*B1+A2*B2+... 이나 =A1*B1*C1+A2*B2*C2+... 라는 수식을 손쉽게 계산하기에 알맞은 함수라는 것을 눈치채셨을 것입니다.

 

자, 그럼 이제 아까의 그림으로 돌아가 볼까요?

 

여기서 우리가 구하고자 하는 값은 가격에 개수를 곱한 값의 합, 즉 B3*C3+B4*C4+...+B7*C7입니다. 이것을 SUMPRODUCT 함수로 쓰면 =SUMPRODUCT(B3:B7, C3:C7)이 된다는 것은 금방 알겠죠?


이 수식을 계산하면 다음과 같습니다.

50000*2+10000*3+3000*4+5000*5+20000*6 = 100000+30000+12000+25000+120000 = 287000

따라서 최종 결과값은 위와 같이 287,000이 됩니다.


이번에는 문제를 조금 더 복잡하게 해서, 단순히 개수가 아니라 묶음 수와 묶음 당 개수를 이용해서 가격의 총합을 구해 보겠습니다.

가격의 총합은 (가격) * (묶음 수) * (묶음당 개수)의 합이므로, B3*C3*D3+B4*C4*D4+...+B7*C7*D7이 됩니다.

이것을 함수로 나타내면 =SUMPRODUCT(B3:B7, C3:C7, D3:D7)이 되고, 그 결과값은 위와 같습니다.


이번에는 또 다른 문제로 넘어가 볼까요?

위 그림의 표를 이용하여 총점을 구하고자 합니다. 이때 총점은 점수에 반영 비율을 곱한 값들의 합입니다.

즉, C3*D3+C4*D4+...+C7*D7이 우리가 구하고자 하는 총점이 됩니다.


별로 어렵지 않죠?

정답은 =SUMPRODUCT(C3:C7, D3:D7) 입니다. 결과는 85점입니다.

​
엑셀 함수 왕국의 3번째 포스팅! 이번 포스팅은 AND, OR 함수를 활용하는 방법입니다.
엑셀에서 많이 쓰이는 논리 함수라고 하면 바로 떠올라야 할 함수 2개!
지금부터 정복해 볼까요?

1. AND 함수

수식: =AND(A, B, C, ...)
A, B, C... 가 모두 참이면 참(TRUE), 하나라도 거짓이면 거짓(FALSE) 이 나옵니다.
이때 A, B, C는 수가 될 수도, 수식이 될 수도, TRUE 또는 FALSE가 될 수도 있습니다. 수식인 경우 단순히 1+2=3, A1+A2=A3 같은 것뿐만 아니라 함수를 이용한 수식도 가능합니다.
이것은 이어서 소개할 OR 함수도 마찬가지입니다.

2. OR 함수
수식: =OR(A, B, C, ...)
AND 함수가 주어진 것들이 모두 참이면 참이 나오게 한다면, OR 함수는 1개라도 참이면 참(TRUE)이고 모두 거짓이어야지 거짓(FALSE)이 나옵니다.
역시나 AND 함수처럼 A, B, ... 같은 것은 수가 될 수도, 수식이 될 수도 있습니다. 함수가 포함된 식도 수식으로 볼 수 있고 역시 A, B, C, ... 가 될 수 있습니다.

예를 들어 위와 같이 수식을 작성해 보았습니다.
3>2, 4>3은 참이므로 AND 안의 괄호에 있는 2개가 모두 참이 됩니다. 모두 참이고 거짓에 해당하는 것은 1개도 없으므로, 결론도 TRUE, 즉 참으로 나옵니다. 이거 마음에 들죠?

이번에는 3>2를 3>6으로 바꿔 보았습니다. 3>2는 참이지만 3>6은 거짓이므로 결과는 FALSE가 됩니다. 4>3은 참이지만 '거짓' 에 해당하는 3>6이 있으므로 결과값으로 FALSE가 나오는 것입니다.

하지만 여기서 AND를 OR로 살짝 바꾸면 어떻게 될까요?
3>6과 4>3 중 하나라도 참이면 결론적으로 참이 나오는! 그래서 TRUE가 됩니다.
4>3이 참이므로 OR도 역시 참!

이번에는 =OR(3>6, 4>7) 로 바꿔 보았습니다.
3>6과 4>7이 모두 거짓이므로 결과는 당연히 거짓(FALSE)이 됩니다.

자, 이제 '응용' 으로 넘어가겠습니다.

위 그림에서 A > B > C, 즉 B3>C3>D3이면 TRUE, 아니면 FALSE라고 합시다.
여기서는 =AND(B3>C3>D3) 또는 단순히 =(B3>C3>D3) 이라고 해도 같은 결과가 나오지만, AND 함수 안에 2개의 항목을 넣는 방법으로 한다면 =AND(B3>C3, C3>D3) 도 가능합니다.

하지만 고작 이런 것을 위하여 AND, OR 함수가 존재할 뿐! NO!

A회사에서는 자사 제품인 A제품의 판매량이 B제품의 판매량보다 200건 이상 많고, A, B회사는 서로 협력 관계이지만 A, C회사는 협력 관계가 아니기 때문에 B회사 제품이 C회사 제품보다 200개 이상 팔린 달을 '대박 달' 이라고 한다고 합시다.

즉, (A회사) >= (B회사) + 200 이고 (B회사) >= (C회사) + 200 일 때를 대박 달이라고 하는 것입니다.
여기서 '>=' 는 왼쪽이 오른쪽보다 크거나 같다는 것을 의미합니다.

이때는 A > B > C 하는 식으로 하기 어렵기 때문에 위와 같이 AND 함수를 이용하여 수식을 작성하는 것입니다. C4>=D4+200, D4>=E4+200의 두 조건을 모두 만족해야 TRUE가 됩니다.
굳이 하나로 하자면 =AND(C4>=D4+200>=E4+400) 처럼 해도 되지만, 계산까지 해야 하니까 복잡합니다.

이렇게 해서 '대박 달인지' 의 여부를 구해 보았습니다. 위 표에서는 A>B>C? 부분(차이 200 이상만 인정!) 입니다.
표의 나머지 부분이 모두 입력되어 있다면, 맨 위쪽에만 수식을 입력하신 다음, 그 셀의 오른쪽 아래 부분으로 마우스 커서를 이동시키면

커서가 위와 같은 + 모양이 되는데, 이때 더블 클릭하시면 나머지 셀도 채워집니다. (화살표가 없는 + 모양입니다)

이번 자료는 '점수' 자료입니다.

이번에는 '70점대' 인지의 여부를 구해 볼까요?
70점대는 '70점 이상, 80점 미만' 이기 때문에, 위와 같이 =AND(C3>=70, C3<80) 으로 입력하시면 됩니다.
C3은 A의 점수를 의미합니다.

C3>=70과 C3<80의 두 조건을 모두 만족해야 AND 수식이 TRUE가 되기 때문에, 결과는 위 그림과 같습니다. 70점대만 TRUE가 되는 모습입니다.

이번에는 같은 자료를 가지고 '금상' 수상 여부를 결정해 보겠습니다.
90점 이상이지만 100점은 아닌 아쉬운 분들을 위하여!
이번에는 TRUE, FALSE 대신 금상을 받게 된다면 O를 표시해 보겠습니다.

IF 함수를 추가로 이용해야 하는데, IF 함수의 조건 부분에 AND 함수를 넣는 것입니다.
IF 함수의 수식은 =IF(수식, A, B) 로 수식이 참이면 A가 실행되어 결과로 나타나고, 거짓이면 B가 실행되어 결과로 나타납니다.
수식 부분에 AND를 넣은 경우 그 안의 모든 수식이 참이어야 '참' 으로 판단되어 A가 실행됩니다.

따라서 위와 같이 =IF(AND(C3>=90, C3<100), "O", "") 라고 합니다.
"O", ""는 각각 O 표시와 빈칸을 의미합니다. 엑셀에서 텍스트(글자)나 빈칸을 표시할 때는 이렇게 양쪽에 큰따옴표가 필요합니다.

AND(C3>=90, C3<100) 이니까 C3의 값이 90보다 크거나 같다, C3의 값이 100보다 작다는 두 조건을 모두 만족시켜야 합니다.

결과가 나왔군요. 금상을 받을 사람은 4명입니다. 원하는 대로 나왔습니다.

이번에는 여기에 '수상실적' 과 '엑셀SNS' 를 추가해 보았습니다. 즉 '관련 수상 실적' 이 있거나 저처럼 '엑셀 관련 SNS' 를 운영하고 있는지의 여부 및 그 개수를 표시한 것입니다.

수상실적과 엑셀 SNS, 둘 다 있어야지 TRUE가 나오게 하는 경우와, 하나라도 있어야지 TRUE가 나오게 하는 경우, 수식은 어떻게 작성하면 될까요?
여기서는 위 표에서 A라는 사람을 예로 들어 보겠습니다.

2개 다 있어야 TRUE가 나오게 하는 경우는 =AND(D3>0, E3>0) 과 같이 입력해도 되지만, =AND(D3, E3) 과 같이 입력해도 됩니다. 여기서 D3, E3은 각각 수상 실적의 개수와 엑셀 SNS의 개수입니다.

왜일까요? 바로 엑셀에서는 0을 거짓으로 인식하기 때문입니다. 따라서 D3=0이거나 E3=0이면 AND 수식 내의 D3, E3 부분이 각각 '거짓' 으로 판단됩니다.
여기서는 D3=1, E3=0이므로 E3이 0이기 때문에 E3 부분이 거짓으로 나옵니다. 따라서 AND 함수식이 거짓이 됩니다.
따라서 수상 실적, SNS의 개수처럼 값이 0보다 크거나 같아야 하는 경우, D3>0은 D3과 실질적으로 같은 의미입니다. (0이면 거짓으로, 0보다 크면 참으로 판단하기 때문!!)