SUMPRODUCT : 곱한 후에 더하는 엑셀 함수

 

1. SUMPRODUCT 함수 개요

SUMPRODUCT() 는 개념적으로는 "곱한 후에 더한다" 입니다.

더 정확히는 "대응되는 값끼리 곱한 후에 더한다" 입니다.

A열, B열에 아래와 같이 값이 있다고 합시다.

SUMPRODUCT( A열, B열) 하면, 
같은 행에 있는 A열 값, B열 값을 곱한 후, 4개의 값을 더합니다.
(10 * 0.1) + (11 * 0.2) + (12 * 0.3) + (13 * 0.4)
함수를 사용할 때는 =SUMPRODUCT(A1:A4, B1:B4)  이렇게 합니다.

이것이 SUMPRODUCT() 함수의 기본 동작입니다.

4개의 열을 각 행별로 곱한 후 더하고 싶다면, 인수에 범위만 추가하면 됩니다.

 

 

2.  SUMPRODUCT()  인수를 나열하는 2가지 방법

첫번째 방벙은  "A1:A4, B1:B4, C1:C4, D1:D4 "와 같이 쉼표를 구분자로 나열하는 방법입니다.

두번째 방법은 곱하기 연산자 "*"을 구분자로, 배열 인수들을 나열하는 것입니다.

"A1:A4 * B1:B4 * C1:C4 * D1:D4 "   이렇게 곱하기를 합니다.

두 방법의 결과는 같습니다.

 

 

3.  SUMPRODUCT  사용시 주의할 점

 

1)번, 2)번 수식의 결과값이 달라집니다.  

1)번 식의 결과는 0, 
2)번 수식의 결과는 21입니다.

 

1)번 식에서는 TRUE, FALSE 를 1 과 0  으로 변환하지 않고 곱하기 때문에 안되는 것입니다.
2)번 식에서는 TRUE, FALSE 를 1 과 0  으로 자동 변환하여 곱하기 때문에 원하는 결과를 주는 것이고,

그래서, 이런 경우에는 2)번 수식처럼,  인수 곱하기 방식만 사용해야 합니다.

1)번 식을 다음과 같이 수정하면 원하는 결과가 나옵니다.

=SUMPRODUCT(B1:B4, --C1:C4)

즉, 변환 연산자(--)을 사용하면 됩니다.

이 사례만 보더라도,  SUMPRODUCT() 함수에서, 인수 나열은 곱하기 연산자를 사용하는 것이 편리합니다.

 

4.   SUMPRODUCT( (조건식) * (숫자 범위))

가장 많이 사용하는 형태입니다.

아래 표에서, A열 값이 13보다 작은 행에 있는 E열 값을 모두 더하라고 하면,
인수를  (A1:A4 < 13) * E1:E4   이렇게 만들면 됩니다.
더하고 싶은 값이 들어 있는 범위인 E1:E4  는 괄호를 쳐도 되고 안해도 됩니다.

이 예제에서  E1:E4 와 같은 합계범위(sum range)의 값들은 숫자여야 한다는 점을 기억하세요.

 

 

5.  조건을 추가하는 방법

 

조건이 여러 개라면, (조건식)을 추가해서 곱해주면 됩니다.
아래 표와 같이 값이 있을 때, 3개의 조건을 만족하는 E열의 값만 더하려면 어떻게 할까요?

조건1:  A열 값이 13보다 작다
조건2:  B열 값이 "가가" 이다.
조건3:  C열 값이 2022/12/31 보다 작다.

이렇다면,
(A1:A4 < 13) * (B1:B4 = "가가") * (C1:C4 < "2022-12-31")  이렇게 조건식들을 곱하면 됩니다.
그리고, 맨 끝에 합계영역(sum range)를 곱해주면 됩니다.

 

이 예제는 타이핑해서 여러번 연습해보세요.  연습할수록 이해가 쉽게 될 것입니다.
이와 같이,  SUMPRODUCT()는 다양한 조건을 AND 연산할 때에 강력한 힘을 자랑하는 함수입니다.
예제들은 항상 직접 입력해서 확인하시기 바랍니다.. 연습하세요~

 

6. 인수가 1개이면,  SUM()  과 같음

SUMPRODUCT(A1:A4) 이것과 SUM(A1:A4) 이것의 결과는 같습니다.

그림처럼 실행해보면 알 수 있습니다.

 

 

7. 조건이 1개뿐이라면, SUMIF() 와 같음

 

조건:  A열 값이 12 보다 작은 행의 B열 값을 더하라.

조건이 1개이므로 SUMIF()를 사용해도 됩니다.
조건식을 만드는 방식이 다르다는 점을 다시 한번 언급합니다.

 

 

8. 조건이 2개 이상이라면, SUMIFS()로 대체 가능

조건1 :  A열의 값이 12보다 작다
조건2 : B열의 값이 "나나"가 아니다

 

조건이 위와 같이 2개라고 할 때, 

SUMPRODUCT 함수로는
SUMPRODUCT((A1:A4<12) * (B1:B4 <>"나나" ) * C1:C4) 

이렇게 하면 됩니다.

SUMIFS 함수를 사용한다면  
SUMIFS(C1:C4, A1:A4, "< 12",  B1:B4, "<>"& "나나" )

이렇게 하면 됩니다.