엑셀 SUMPRODUCT 함수 이해하기

 

 

 

엑셀 SUMPRODUCT 함수 기초

SUMPRODUCT 함수는 로우 데이터로 통계를 낼 때 주로 사용합니다.

=SUMPRODUCT (array1,[array2],[array3]...) 형식으로 함수가 구성됩니다.

범위를 지정해 주고 해당하는 조건을 배열해서 원하는 값을 구하는 함수입니다.

 

위 거래내역은 로우데이터를 기준으로 각 품목과 규격의 상품의 판매수에 따른 판매금액을 구합니다.

단가의 경우

우측(파란색) 품목, 규격, 단가 표에서 해당하는 상품의 단가를 SUMPRODUCT 함수를 이용해서 구했습니다.

=SUMPRODUCT(($J$8:$J$15=$B8)*($K$8:$K$15=$C8)*($L$8:$L$15))

품목 = 사과 규격 = 10kg 단가

즉, 품목은 사과, 규격은 10kg에 해당하는 단가를 합산하는 것입니다.

해당하는 품목, 규격은 1개이므로 해당하는 단가를 불러올 수 있습니다.

​

판매금액 역시 단가를 산출하는 수식에 X 판매수를 해줘서 구했습니다.

=SUMPRODUCT(($J$8:$J$15=$B9)*($K$8:$K$15=$C9)*($L$8:$L$15))*F9

F9는 판매수

판매금액 확인은 =단가 X 판매수 이며 검산을 위해 추가했습니다.

​

엑셀 SUMPRODUCT 함수는 둘 이상의 셀에서 조건을 지정해서 원하는 값을 얻을 수 있습니다.

​

=SUMPRODUCT((B8:B16="사과")*(C8:C16="10kg"))

이런 형식으로 직접 텍스트를 입력하여 조건을 지정할 수 도 있으며 해당 함수는

사과, 10kg의 조건에 해당하는 개수를 구할 수 있습니다.

​

지정된 셀에 (($J$8:$J$15=$B8) $가 붙는 경우가 있는데

$J$8:$J$15 > 해당 셀 고정 (드래그 시 지정된 셀 유지) - 단축키 F4

$B9 > 해당 셀 고정 (가로 드래그 시 지정된 셀 유지)

B$9 > 해당 셀 고정 (세로 드래그 시 지정된 셀 유지)

​

---

엑셀 SUMPRODUCT 함수 활용

SUMPRODUCT 함수의 기본을 이해하면 로우 데이터를 이용해서 원하는 값을 손쉽게 구할 수 있습니다.

로우데이터의 텍스트와 SUMPRODUCT 함수의 지정하는 텍스트가 동일해야 값을 구할 수 있으며

텍스트 형식이나, 텍스트 자체가 다른 경우 인식하지 못할 수 있으니 오류가 나는 경우에 각 텍스트를 확인해 주세요.

(예 : 거래내역 - 10kg, (C8:C16="10KG") > 오류 발생)

거래내역 로우 데이터 기반으로

하단 조건에 맞는 값을 구했습니다.

​

사과, 10kg - =SUMPRODUCT((B8:B16="사과")*(C8:C16="10kg"))

해당 조건에 맞는 범위를 지정해주고 구하는 조건을 입력하면 됩니다.

(텍스트를 직접 입력하는 경우에는 ""를 추가해 줘야 합니다.)

​

미소마트, 사과, 판매수 - =SUMPRODUCT(($D$8:$D$16="미소마트")*($B$8:$B$16="사과")*($F$8:$F$16))

미소마트 - 구매처

사과 - 품목

판매수 - 판매수

판매수의 경우 수치로 되어 있기에 별다른 조건 없이 ($F$8:$F$16) 해당 셀을 지정했습니다.

​

이런 식으로 해당하는 조건에 맞는 셀을 지정하고, 구하는 조건을 입력해 주면 원하는 값을 구할 수 있습니다.

​

거래내역에서 1만원 이하 품목수량을 알고 싶으면

=SUMPRODUCT((B8:B16>0)*(E8:E16<=10000))

품목 > 0 인 경우(값 입력 상태 - 없으면 0보다 작게 인식), 단가 10,000원 이하의 개수를 구합니다.

countif 함수를 사용할 수 도 있으나, SUMPRODUCT함수를 응용하기 위해서 해당 함수로 구해봤습니다.

=COUNTIF(E8:E16,"<=10000")

​

1만원 초과의 경우

=SUMPRODUCT((B8:B16>0)*(E8:E16>10000)) 지정해 주면 됩니다.

​

이상 >=

이하 <=

초과 >

미만 <

이렇게 조건을 지정해 주면 됩니다.

​

포스팅 내용의 함수를 손쉽게 확인할 수 있도록 엑셀파일 첨부했으니

확인해보세요.

 

엑셀SUMPRODUCT함수 (1).xlsx

0.01MB

 

 

본 포스팅은 쿠팡파트너스 활동을 통해 일정액의 수수료를 제공받을 수 있습니다.