엑셀 SUMPRODUCT 함수로 다양한 조건의 통계 값 계산하기

 

 

엑셀 SUMPRODUCT 함수 기본 이해

SUMPRODUCT 함수의 경우

저는 주로 특정 조건에 맞는 값을 계산하거나 통계를 사용할 때 사용합니다.

​

엑셀 SUMPRODUCT

SUMPRODUCT함수는

=SUMPRODUCT((조건범위1=조건1)*(조건범위2=조건2)*(조건범위N=조건N))

이런 식으로 다중조건에 따른 데이터를 조건에 맞춰 계산할 수 있습니다.

​

아래 표와 같은 로우데이터가 있다면 각 항목의 조건에 맞는 통계를 구할 수 있습니다.

​

엑셀 SUMPRODUCT

​

12월 1일 ~ 12월3일까지, 우리마트, 공급가액 합계를 계산하려면

=SUMPRODUCT(($A$4:$A$8>=DATE(2020,12,1))*($A$4:$A$8<=DATE(2020,12,3))*($B$4:$B$8="우리마트")*($G$4:$G$8))

일자항목에서 12월 1일, 12월 3일일 저정하고 거래처항목에서 "우리마트"(또는 해당 셀 지정)공급가액의 해당하는 수치의 범위를 지정합니다.

​

=SUMPRODUCT((일자셀범위>=시작일)*(일자셀범위<=종료일)*($거래처범위="우리마트")*(공급가액범위))

이렇 듯 각 조건에 맞는 항목의 셀범위를 지정하고, 조건을 지정해 줍니다.

각 조건은 각 조건범위의 셀형식과 일치해야 하며 날짜를 지정할 때는 날짜 형식이어야 하기에 DATE(2020.12.1)으로 날짜 형식으로 지정하고 수식을 계산할 때는 셀 형식이 숫자 형식이어야 합니다. (텍스트 형식은 오류 발생)

​

아래 예시 이미지를 보면 이해가 더 쉬울겁니다. 기본 원리만 이해하면 다양하게 활용할 수 있습니다.

​

엑셀 SUMPRODUCT 함수 예시

---

​엑셀 SUMPRODUCT 함수 응용한 여러 데이터의 다중조건 값 구하기

SUMPRODUCT 함수를 이해하면 여러 시트의 로우데이터를 기반으로 다양한 값을 구할 수 있습니다.

​아래 로우데이터는 각 회사의 제품별재료배합비율, 배합비율에 따른 재료사용량, 일자별 제품 생산현황의 로우데이터입니다.

로우 데이터

주어진 로우데이터를 기반으로 엑셀 SUMPRODUCT 함수를 응용해서 재료사용량을 각 회사, 제품, 재료, 일자에 맞춰 계산해 보겠습니다.

​

엑셀 SUMPRODUCT 함수

이해를 돕기 위해 일자는 12월1일을 기준으로 했고 각 제품생산량, 재료배합비율, 재료사용량을해당 로우데이터에서 구하고 나서 총재료사용량을 계산했습니다.

​

아래 표의 제품생산량은

=SUMPRODUCT(($A$47:$A$51=$A$19)*($B$47:$B$51=$B$19)*($C$46:$F$46=D$18)*($C$47:$F$51))

제품생산현황에서 회사, 제품, 일자 조건에 맞는 생산량을 계산합니다.

​

$A$47:$A$51 - 해당 셀 범위 고정 (드래그 시 고정)

$B$19 - 해당 셀 고정 (드래그 시 고정)

D$18 - 해당 열 고정 (열(세로) 드래그 시 고정

$D$18 - 해당 행 고정 (행(가로) 드래그 시 고정

​

SUMPRODUCT 함수와 $$를 이해하면 쉽게 사용할 수 있습니다.

​

제품생산량

 

이런 식으로 재료배합 비율을 계산하면

=SUMPRODUCT(($A$31:$A$35=$A$19)*($B$31:$B$35=$B$19)*($C$30:$F$30=$C19)*($C$31:$F$35))

각 조건에 맞는 조건을 지정해 주면 됩니다. 재료배합비율은 %이기에 소숫점으로 표기되도록 했습니다.

​

재료배합비율

이렇듯 SUMPRODUCT 함수로 제품생산량, 재료배합비율, 재료사용량을 계산하면 총재료 사용량을 이를 모두 곱해주면 되겠지요.

​

총재료사용량은

=SUMPRODUCT(($A$47:$A$51=$A$19)*($B$47:$B$51=$B$19)*($C$46:$F$46=D$18)*($C$47:$F$51))*SUMPRODUCT(($A$31:$A$35=$A$19)*($B$31:$B$35=$B$19)*($C$30:$F$30=$C19)*($C$31:$F$35))*SUMPRODUCT(($A$39:$A$43=$A$19)*($B$39:$B$43=$B$19)*($C$38:$F$38=$C19)*($C$39:$F$43))

​

길어보이지만

=제품생산량*재료배합비율*재료사용량

수식일 뿐입니다.

​

총재료사용량

이렇듯 SUMPRODUCT함수는 각 조건에 맞는 수를 계산하거나 각 조건에 맞는 개수를 구할 수 있습니다.

​

엑셀파일 참조했으니 함수가 적용된 파일을 보고 공부를 하시면 금방 이해가 될겁니다.

 

엑셀SUMPRODUCT함수.xlsx

0.01MB

 

 

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