[펌]SUMPRODUCT 함수 활용법

출처 : 네이버 지식인
2차출처: abyul.com 

SUMPRODUCT 함수에 대해

 도움말을 그대로 인용하자면 주어진 배열에서 해당 요소들을 모두 곱하고 그 곱의 합계를 반환한다. 즉 함수 자체가 배열수식형태로 이미 설정되어 있어 이를 잘 활용하면 다양하게 응용할 수 있다.

  SUMPRODUCT의 장점은 다중조건을 처리할 수 있다는 점이다. 논리연산자를 활용할 경우  보다 더 유연한 검색이 가능하다. 하나 또는 두개 이상의 다중조건을 만족시키는 값들을 추출해 낼 수 있다.

 다중조건을 처리하는 또 다른 방법은 SUM 함수와 IF함수를 중첩한 배열수식으로 처리할 수 있다. 또한 논리연산자 부분의 처리는 대등소이하다.

  (예제파일은 글의 마지막에 링크되어 있습니다.)

1.  SUMPRODUCT 함수사용
2.  SUMPRODUCT 함수 적용사례
3.  SUM,IF를 활용한 배열수식과  비교
4.  맺는 말

1.  SUMPRODUCT 함수사용 (그림참조)



1.1  배열요소들의 곱한 값의 합계
                     / 단가 * 수량 한 값들의 합계

     =SUMPRODUCT(C3:C12,D3:D12)
          A3*D3, A4*D4, A5*D5, ……A12*D12  각 곱한값들의 합을 구함



1.2  단일 조건을 만족하는 건수   
                       / 지역(A열)이 "서울"인 행의 수

   =SUMPRODUCT((A3:A12="서울")*1)       cf: =COUNTIF(A3:A12, "서울")  동일한 결과

        A3="서울",  A4="서울", A5="서울" ...... A12="서울" 의 논리값 참, 거짓으로 -> 합산할 수 없음
        ((A2:A12="서울")*1)  ==>  A열이 "서울"이면 1을 아니면 0을

1.3  단일 조건을 만족하는 수량의 합계
                        / 지역(A열)이 "서울"인 수량(C열)의 합계

   =SUMPRODUCT((A3:A12="서울")*C3:C12)      cf: =SUMIF(A3:A12, "서울", C3:C12) 동일한 결과
   =SUMPRODUCT((A3:A12="서울")*1,C3:C12)  위와 같은 결과

    (A3="서울")*C3, (A4="서울")*C4, (A5="서울")*C5....(A12="서울")*C12
         A열이 "서울"이면 C열값을 아니면 0을



1.4 다중조건의 건  수           
                        / 지역(A열)가 "서울"이고 품목(B열)이 "사과"인 행의 수

    =SUMPRODUCT((A3:A12="서울")*(B3:B12="사과"))
       (A3="서울")*(B3="사과"), (A4="서울")*(B4="사과"), (A5="서울")*(B5="사과"),
        ............(A12="서울")*(B12="사과")          둘다 만족시키면 1을, 아니면 0을



1.5  다중조건의 수량의 합계
                       / 지역(A열)이 "서울"이고 품목(B열)이 "사과"인 수량(C열)의 합계

   =SUMPRODUCT((A3:A12="서울")*(B3:B12="사과")*(C3:C12))
   =SUMPRODUCT((A3:A12="서울")*(B3:B12="사과"),(C3:C12))   위와 같은 결과

       (A3="서울")*(B3="사과")*C3, (A4="서울")*(B4="사과")*C4, (A5="서울")*(B5="사과")*C5,
         ............(A12="서울")*(B12="사과")*C12     두가지 만족할 경우  C열 값을 아니면 0



1.6  부등호를 활용한 다중조건의 건
                       / 지역(A열)가 "서울"이외의 곳에서  금액(E열)이 100만원 이상인 행의 수

   =SUMPRODUCT((A3:A12<>"서울")*(E3:E12>=1000000))

1.7  부등호를 활용한 다중조건의 합계
                       /  지역(A열)이 "서울"이외의 곳에서  금액(E열)이 100만원 이상인 경우의 금액 합계

   =SUMPRODUCT((A3:A12<>"서울")*(E3:E12>=1000000))






2.  SUMPRODUCT 함수 적용사례

2.1 지역/품목별 건수
I3=SUMPRODUCT(($A$3:$A$20=$G5)*($B$3:$B$20=H$14))

2.2 지역/품목별 금액
I12=SUMPRODUCT(($A$3:$A$20=$G15)*($B$3:$B$20=H$14)*$C$3:$C$20)

2.3 품목별/날짜별 매출수량
G21=SUMPRODUCT(($A$3:$A$20=G$20)*($C$3:$C$20=$F21)*$D$3:$D$20)

2.4 품목별 수량구간별 건수

H5=SUMPRODUCT(($B$3:$B$20=$G5)*($C$3:$C$20<=10))
      
I5=SUMPRODUCT(($B$3:$B$20=$G5)*($C$3:$C$20<=20))
    -SUMPRODUCT(($B$3:$B$20=$G5)*($C$3:$C$20<=10))
     
J5=SUMPRODUCT(($B$3:$B$20=$G5)*1)
     -SUMPRODUCT(($B$3:$B$20=$G5)*($C$3:$C$20<=20))


2.5  품목별 수량구간별 수량계    
      
H15=SUMPRODUCT(($B$3:$B$20=$G15)*($C$3:$C$20<=10)*$C$3:$C$20)
      
I15=SUMPRODUCT(($B$3:$B$20=$G15)*($C$3:$C$20<=20)*$C$3:$C$20)
      -SUMPRODUCT(($B$3:$B$20=$G15)*($C$3:$C$20<=10)*$C$3:$C$20)
      
J15=SUMPRODUCT(($B$3:$B$20=$G15)*$C$3:$C$20)
       -SUMPRODUCT(($B$3:$B$20=$G15)*($C$3:$C$20<=20)*$C$3:$C$20)

2.6  거래처/품목별 단가 찾기
상품의 단가가 거래처별로 다르게 적용하는 경우 처리할 수있다.  (찾기함수 대용가능)

그러나 다음과 같은 전제가 필요하다.
  - 해당조건을 만족하는 값은 단 1개만 존재한다.
  - 찾고자 하는 값이 수치이어야 한다. (문자등은 불가능)

I5=SUMPRODUCT(($A$3:$A$14=G5)*($B$3:$B$14=F5)*$C$3:$C$14)

3.  SUM,IF를 활용한 배열수식과  비교

SUMPRODUCT처럼 두개이상의 조건에 따라 값을 추출할 수 있는 방법이 있는데,  SUM 함수와 IF함수를 중첩하고 배열수식형태로 처리하여야 한다. 두가지 경우 모두 논리연산자를 활용한다는 점에서는 같다. 그러나 배열함수를 이용한 경우는 IF값에 따라 그 값들을 설정해 주고 그 값들을 더해주는 형태를 취하여야 한다.


3.1 조건에 맞는 건수
=SUMPRODUCT((A3:A12="서울")*(B3:B12="사과"))
=SUM(IF((A3:A12="서울")*(B3:B12="사과"),1,0))    입력후 Ctrl + Shift + Enter 동시에 누름
   건수를 구하려면 조건에 맞을 경우 1 을 아니면 0으로 처리하고 합산하는 처리 방식이다.


3.2  조건에 맞는 값의  합계

=SUMPRODUCT((A2:A11="서울")*(B2:B11="사과"),(C2:C11))
=SUM(IF((A3:A12="서울")*(B3:B12="사과"),(C3:C12),0))  입력후 Ctrl + Shift + Enter 동시에 누름
   합산처리시에 조건여하에 따라 합하고자 하는 값을 주거나 0으로 처리하여 합산시킨다.

4.  맺는 말

SUMPRODUCT는 논리연산자를 활용한 조건비교로 SUMIF함수나 COUNTIF함수 보다 더 유연한 검색이 가능하다. 또한 배열수식의 기능이 이미 포함되어 있어 두개이상의 다중조건을 쉽게 처리할 수 있다. 조금만 응용하여 사용해도  조건에 따른 다양한 표현이 가능하다.

(엑셀 2003 을 기준으로 작성되었습니다.)

파일보기 :     blog.naver.com/park__jang/60036417761