[쉽게 배우는 엑셀 함수] SUMPRODUCT 활용법

 

※ SUMPRODUCT 함수의 기본 원리는 아래 글에서 확인해주시기 바랍니다.

 

[MS EXCEL functions] - [간단한 엑셀 함수] SUMPRODUCT함수 사용법

 

 

｜SUMPRODUCT 함수의 활용

 

SUMPRODUCT 함수는 매우 유용한 함수입니다.

 

배열함수, 피벗테이블의 기능을 대체할 수 있는 함수입니다.

폐쇄적인 다른 함수들보다 확장성이 좋기 때문에 많은 분들이 SUMPRODUCT함수를 사용합니다.

 

SUMPRODUCT 함수는 배열수식과 기능이 같다고 할 수 있습니다.

 

 

 

｜SUMPRODUCT 함수의 이해

 

 

 

먼저 SUMPRODUCT의 가벼운 사용예시를 확인해보겠습니다.

 

 

위와 같은 좌측의 영역에 그에 대응하는 우측의 영역의 값을 곱한 총 합계를 구하고 싶을때,

 

 

 

 

좌측의 영역과 우측의 영역을 곱하는 작업을 한 뒤,

 

SUM함수를 사용하여 10,000이라는 값을 구할 수 있습니다.

 

 

 

 

하지만, SUMPRODUCT 함수를 사용하면

 

 

 

 

위처럼 곱연산의 과정을 생략하고, 훨씬 가볍게 같은 결과 값을 구할 수 있습니다.

 

({=SUM((B5:D8)*(F5:H8))} 라는 배열수식을 사용하는것과 같은 결과입니다.)

 

리소스를 훨씬 적게 사용하기 때문에, 엑셀사용에 편리함을 더할 수 있습니다.

 

 

 

｜SUMPRODUCT 함수 따라하기

 

SUMPRODUCT 함수는 확장성이 매우 뛰어납니다.

 

SUMPRODUCT 함수의 활용을 설명하기 위해, 화장품을 매입하는 유통업체에 근무한다고 가정하겠습니다.

 

 

 

위의 파일은 7월에 우리가 매입한 내역을 적어 둔 매입내역서입니다.

 

B열에는 화장품 매입처가 적혀있고, C열에는 매입한 상품이 적혀있습니다.

 

D열에는 단가가 적혀있고, E열에는 매입량이 적혀있습니다.

 

 

하나의 예로 "LG생활건강"으로부터 매입한 "치약"의 매입금액(단가×수량)이 얼마인지 알고 싶을때, 

 

 SUMPRODUCT 함수를 활용할 수 있습니다.

 

 

 

우리가 여기서 알고 싶은 '다중조건'은 아래와 같습니다.

 

-조건① : 매입처가 "LG생활건강"

 

-조건② : 매입상품군이 "치약"

 

 

 

 

=SUMPRODUCT((B2:B17=B19)*(C2:C17=C19),D2:D17,E2:E17)

 

이 표를 이해하기 위해서, SUMPRODUCT 함수를 하나씩 따라가보겠습니다.

 

=SUMPRODUCT(B2:B17,C2:C17,D2:D17,E2:E17)

 

위의 함수를 셀에 적어보면 0을 반환합니다.

 

그 이유는 B2:B17 , C2:C17에 적힌 모든 값이 숫자가 아닌 문자이기 때문에, 

 

∑(0×0×단가×수량)=0이 됩니다. (이전글의 '주의사항' 참고)

 

 

그럼 조건①과 조건②는 아래와 같이 지정할 수 있습니다. 

 

구분	의미	함수
조건①	매입처 = "LG생활건강"	(B2:B17=B19)
조건②	매입상품군 = "치약"	(C2:C17=C19)

 

위의 조건을 지정하고 "*"로 묶어주게 되면,

 

"LG생활건강"으로부터 매입한 "치약"의 매입금액(단가×수량)를 구한다는 의미가 됩니다.

 

 

먼저, 상단에 함수가 적혀있는 영역에서, "B2:B17=B19" 부분을 드래그 하여 F9를 눌러보게 되면,

 

드래그 한 영역이 아래처럼 변하는 모습을 볼 수 있습니다.

 

FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;

 

2번째, 6번째, 10번째, 14번째 부분은 조건에 맞기 때문에 TRUE로 표시됩니다.

 

또, 상단에 함수가 적혀있는 영역에서, "C2:C17=C19" 부분을 드래그 한 뒤, F9를 눌러보게 되면,

 

FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;

 

2번째, 5번째, 8번째, 11번째, 14번째 부분은 조건에 맞기 때문에 TRUE로 표시됩니다.

 

TRUE는 1, FALSE는 0으로 치환되기 때문에,

 

 

SUMPRODUCT((B2:B17=B19)*(C2:C17=C19),D2:D17,E2:E17)

 

위 표는

 

매입처	매입상품군	단가	수량	소계	합계
0	0	6,000	1,000	0	2,200,000
1	1	2,000	700	1,400,000
0	0	8,000	900	0
0	0	1,000	100	0
0	1	7,000	300	0
1	0	9,000	1,000	0
0	0	5,000	100	0
0	1	2,000	400	0
0	0	8,000	900	0
1	0	8,000	200	0
0	1	5,000	900	0
0	0	6,000	200	0
0	0	8,000	500	0
1	1	2,000	400	800,000
0	0	9,000	200	0
0	0	8,000	1,000	0

 

 

이런 의미를 가지기 때문에 "*"를 활용하여 다양한 다중조건을 엮어서 결과를 뽑아낼 수 있습니다.