엑셀 SUMPRODUCT 함수로 합계 구하기

SUMPRODUCT 함수로 합계 구하기

[테스트 버전 : 엑셀 2019]

 

SUMPRODUCT는 곱하기와 합계를 복합적으로 수행하는 배열 함수이지만, 올바른 조건일 때 연산을 수행하는 조건식도 사용할 수 있어 사용범위가 넓은 함수 중 하나인데요. 이번 포스팅에선 SUMPRODUCT 함수에 복잡한 예제를 다루기보단, 기본 설명과 예제를 다루어 좀 더 자세한 사용법을 익힐 수 있도록 알려드리겠습니다.

 

SUMPRODUCT 함수로 합계 구하기

 

SUMPRODUCT 함수 구조

SUMPRODUCT 함수는 여러 배열(범위)이 있을 경우 A1 x B1, A2 x B2와 같은 연산을 수행하고, 그 수행된 결과의 ((A1 x B1) + (A2 x B2)) 합계를 구하는 함수입니다. (예제에서 좀 더 자세히 다루도록 하겠습니다)

 

=SUMPRODUCT(배열 인수 1, 배열 인수 2......)

• 배열 인수 1은 필수 항목이고, 배열 인수 2부터 선택 항목입니다. 
• 배열 1만 기입된 경우, 배열 1에 대한 합계만 계산됩니다. 
• 배열은 2 ~ 255개까지 지정할 수 있습니다.
• 함수 자체가 배열 함수라, Ctrl + Shift + Enter 안 해도 됩니다
• 숫자가 아닌 배열 항목은 0처럼 처리됩니다
• 이 함수는 배열끼리 곱한 값의 합계를 구하는 방법이라, 행 개수가 일치해야 됩니다. 예를 들면 SUMPRODUCT(A3:A5, B4:B6)은 시작점은 다르지만 행(줄) 수가 같기 때문에 문제가 없고, SUMPRODUCT(A3:A5, B4:B5)는 행(줄) 수 자체가 다르기 때문에 #VALUE! 오류가 발생됩니다.

 

SUMPRODUCT 예제

아래에선 2 배열의 합계, 3 배열의 합계, 단일 조건을 포함한 합계를 구하는 3가지 예제를 설명드리겠습니다.

 

SUMPRODUCT 함수 예제

 

이 함수의 기본형은 2개의 인수를 사용한 곱셈과 합계 예제입니다.

 

=SUMPRODUCT(C4:C11, D4:D11)

• 연산 과정은 (C4 x D4) + (C5 x D5) +.....(C11 x D11)이고, 숫자로 표현하면 (100 x 2) + (110 x 4) +.... (110 x 5)입니다.
• 이렇게 계산된 SUMPRODUCT 합계 결과는 2245입니다. 

 

 

예제2

 

3개의 배열 인수를 사용해 곱셈과 덧셈 연산을 진행한 예제입니다

 

=SUMPRODUCT(C4:C14, D4:D14, E4:E14)

• 연산 과정은 (C4 x D4 x E4) + (C5 x D5 x E5) +.... (C14 x D14 x E14)이고
• 합계 결과는 393.9입니다.

 

 

SUMPRODUCT 조건식 사용하기

 

배열 함수이기 때문에 배열 수식을 지정하지 않더라도 배열 조건을 기입할 수 있고, 조건에 따른 곱하기와 합계 연산을 진행할 수 있습니다. 그리고 아래 예제에선 조건식을 먼저 설명드리기 위해 조건을 먼저 다루었습니다.

 

=SUMPRODUCT((B4:B11=D12)....

• 조건은 B4:B11범위 각각의 셀이 "55인치 TV"인지를 확인하는 조건이며,
• B4=D12와 같으면 TRUE 아니면 FALSE....
• B11=D12와 같으면 TRUE 아니면 FALSE 와 같이 조건을 비교합니다.
• 참고로 TRUE는 1, FALSE는 0이라고 보시면 됩니다.

 

 

조건식 사용한 예제 결과

 

조건을 지정했다면 곱셈과 합계를 구할 범위를 지정한 후 엔터를 치면 연산된 결과가 표시됩니다.

 

=SUMPRODUCT((B4:B11=D12)*C4:C11*D4:D11)

• (B4=D12)는 일치하지 않기 때문에 FALSE란 결과가 나오고, 0 x C4가 돼 0이란 값을 리턴합니다.
• (B5=D12)는 일치하기 때문에 TRUE란 값을 얻을 수 있고, 1 x C5 x D5란 식이 형성돼 440이란 결과가 나옵니다.
• 곱셈으로 얻어진 결과치를 합계한 1430이 최종 결과로 표시됩니다.
• 참고로 조건식 바로 다음에 나오는 배열은 "*"를 사용해 이어줘야 오류가 발생되지 않습니다.

 

연산 과정이 이해 안 가시는 분들이 계실 것 같아 조금 더 자세한 과정을 다루었으며, 중간 과정을 생략한 앞 2개, 마지막 연산 과정, 곱셈된 값들에 대한 최종 합계 과정을 다루었습니다.

• 4행 곱셈 : (B4=D12)*C4*D4 = 0
• 5행 곱셈 : (B5=D12)*C5*D5 = 440
• .
• 11행 곱셈 : (B11=D12)*C11*D11=550
• 4~11행 결과치 합계 : 0 + 440 + 0 + 0 + 0 + 0 + 440 + 550 = 1430