엑셀 함수 정리 SUMPRODUCT 함수 다중조건 통계 활용하기

엑셀 sumprouduct 함수는 다중조건의 개수, 합계를 구할 때 유용하게 사용하는 함수입니다. 판매내역에서 특정일 또는 월의 특정 품목의 판매수량이나 판매금액을 구할 수 있습니다. 엑셀 파일예제를 첨부했으니 엑셀파일을 함께 보면 이해가 빠를 겁니다.

 

엑셀 SUMPRODUCT 함수 형식

=SUMPRODUCT((셀:셀="조건1")*(셀:셀="조건2")*(셀:셀)="조건3")) 이런 식으로 사용하면 조건의 교집합에 해당하는 값의 개수를 찾을 수 있습니다. 

=SUMPRODUCT((셀:셀="조건1")*(셀:셀="조건2")*(셀:셀)) 이런 식으로 사용하면 조건의 교집합에 해당하는 수량(금액)의 합계를 구할 수 있습니다. 

다중조건에 개수, 합계를 계산할 수 있는 유용한 함수여서 자주 사용하게 됩니다. 

 

엑셀 SUMPRODUCT 함수로 중복값 찾기

엑셀 데이터에서 중복된 값을 찾는 방법은 여러가지가 있는데 그 중에서 SUMPRODUCT 함수를 이용해서 중복값을 찾는 방법이 있습니다. 
거래처, 사업자등록번호, 대표 등의 데이터를 기준으로 중복값을 체크했습니다. 

 

중복값1

사업자등록번호를 기준으로 중복값을 체크했는데
단일 항목의 중복 값은 COUNTIF 함수로도 찾을 수 있습니다. 
=SUMPRODUCT(($B$3:$B$8=$B3)*($B$3:$B$8>0))
해당 항목의 범위에서 b3셀을 지정했고, SUMPRODUCT함수 형식을 맞추기 위해서 
($B$3:$B$8>0) 0보다 큰 값을 지정했습니다. 
=COUNTIF(B3:B8,B3) 수식도 같은 결과을 얻을 수 있습니다.

 

중복값2

중복값2는 거래처, 사업자등록번호, 대표 항목을 기준으로 중복값을 체크했습니다. 
=SUMPRODUCT(($A$3:$A$8=$A4)*($B$3:$B$8=$B4)*($C$3:$C$8=$C4))
2개 이상 항목의 중복 값을 찾을 때 주로 사용하며 단일항목은 중복값1의 형식으로 지정해줘야 합니다. 

 

중복값 결과가 1인 경우에는 중복없음
중복값 결과가 2 이상인 경우에는 해당 수만큼 중복이 있다는 결과입니다. 

 

데이터필터 기능을 사용해서 내림차순으로 정렬하면 중복값을 한눈에 볼 수 있습니다. 

 

SUMPRODUCT 함수로 통계 구하기

SUMPRODUCT 함수로 각 품목의 판매량, 판매금액 또는 년도, 월을 기준으로 판매량 등을 구할 수 있습니다. 
=sumproduct((범위:범위=조건)*(범위:범위=조건)*(범위:범위=조건)*(범위:범위))
형식으로 수식을 지정하면 됩니다. 

​
엑셀 SUMPRODUCT 함수로 통계 구하기

통계1

각 거래처의 거래내역에서 천리마마트의 2019년 1월 판매금액의 통계를 구하는 수식입니다. 
=SUMPRODUCT(($A$12:$A$17="천리마마트")*(MONTH($B$12:$B$17)=1)*($F$12:$F$17))
거래처 항목에서 천리마마트 지정, 거래일자에서 month 함수로 1월을 지정, 판매수량을 지정하면 해당 값을 구할 수 있습니다. 
각 조건은 (범위:범위=조건) 형식으로 구하고자 하는 값이 숫자인 경우에는 해당 숫자(범위:범위)를 지정해 줍니다. 
"천리마마트"처럼 직접 텍스트를 입력해도 되고, 해당 값의 셀을 지정해줘도 됩니다. 
단, 텍스트가 동일해야 하며 빈칸이 있는 경우나 숫자, 텍스트 등의 형식이 다른 경우에 인식하지 못할 수 있습니다. 
(예 : 천리마마트, 천리마마트 는 띄어쓰기로 인해 다른 값으로 인식)
SUMRPODUCT의 기본 형식을 이해하고 다른 함수와 병행해서 사용하는 방법을 이해한다면 다른 통계도 쉽게 구할 수 있습니다. 

통계2
고구마, 전체판매수량의 통계를 구합니다. 
=SUMPRODUCT((C12:C17="고구마")*(E12:E17))

 

통계3
가나마켓, 딸기, 2019년, 판매금액의 통계를 구합니다. 
=SUMPRODUCT((A12:A17="가나마켓")*(YEAR(B12:B17)=2019)*(C12:C17="딸기")*(F12:F17))

통계4
사과, 고구마, 2019년, 판매금액의 합계를 구합니다. 
=SUMPRODUCT((YEAR(B12:B17)=2019)*(C12:C17="사과")*(F12:F17))+SUMPRODUCT((YEAR(B12:B17)=2019)*(C12:C17="고구마")*(F12:F17))

통계5 
회전문마트, 전체판매금액의 합계를 구합니다. 
=SUMPRODUCT((A12:A17="회전문마트")*(F12:F17))

 

엑셀 SUMPRODUCT 함수로 통계 구하기2

이번에는 살짝 응용을 해서 활용해 보는 방법을 알아보겠습니다. 
건설공사 계약 현황 자료로 통계를 구하는 방법으로 통계구하기1과 다른 점은 
수의계약(2,000만원 미만 공사)에 따른 조건을 지정해 주는 방법입니다. 

통계1
다모아건설, 2018년 3월, 수의계약 건수의 통계를 구합니다. 
계약건수는 조건에 맞는 계약의 건수를 계산하면 되는데, 
수의계약의 경우 계약금액 항목이 2,000만원 미만인 계약으로 (계약금액범위:계약금액범위<20000000) 형식으로 
지정해 주면 해당하는 건수를 구할 수 있습니다. 
=SUMPRODUCT((A27:A32="다모아건설")*(MONTH(B27:B32)=3)*(F27:F32<20000000))
다모아 건설이 2018년 3월에 계약한 공사 중 계약금액이 2,000만원 보다 작은 건수를 구하면 곧 수의계약 건수가 됩니다. 

통계2
다모아건설, 2018년 3월, 수의계약 금액은 통계1의 수식에 계약금액의 범위를 추가해 주면 됩니다. 
=SUMPRODUCT((A27:A32="다모아건설")*(MONTH(B27:B32)=3)*(F27:F32<20000000)*(F27:F32))
각 조건에 맞고, 계약금액이 2,000만원 미만인 공사의 계약금액의 합계를 구하는 수식입니다. 
조건을 지정하고, 계산할 숫자의 위치를 지정해 주면 SUMPRODUCT 함수로 통계를 손쉽게 구할 수 있습니다. 

통계3
본청, 토공, 수의계약 금액의 통계를 구합니다. 
=SUMPRODUCT((C27:C32="본청")*(E27:E32="토공")*(F27:F32<20000000)*(F27:F32))

통계4
본청, 철콘, 낙찰계약금액의 통계를 구합니다. (낙찰계약의 경우 2,000만원 이상 공사)
=SUMPRODUCT((C27:C32="본청")*(E27:E32="철콘")*(F27:F32>=20000000)*(F27:F32))

통계5
본청, 2018년, 전체 계약금액의 통계를 구합니다. 
=SUMPRODUCT((C27:C32="본청")*(YEAR(B27:B32)=2018)*(F27:F32))

 

 

엑셀 SUMPRODUCT 함수 예제 파일

예제파일 첨부했으니 참고하세요. 

엑셀SUMPRODUCT함수활용.xlsx

0.01MB