컴퓨터잡담

[엑셀] SUMPRODUCT 함수 이용, 조건에 따른 중복항목 제외 후 카운트하는 함수

by 디케 posted Oct 09, 2010
?

단축키

Prev이전 문서

Next다음 문서

ESC닫기

크게 작게 위로 아래로 댓글로 가기 인쇄

[엑셀] SUMPRODUCT 함수 이용, 조건에 따른 중복항목 제외 후 카운트하는 함수

http://flogsta.tistory.com/427



주어진 배열에서 해당 요소들을 모두 곱하고 그 곱의 합계를 반환합니다.
 
SUMPRODUCT(array1,array2,array3, ...)

예를 들면 수식 =SUM(A2:B4^2)을 사용하고 Ctrl+Shift+Enter를 눌러 A2:B4 요소들의 제곱의 합을 계산할 수 있습니다.




한 가지 조건을 만족할 경우 그 갯수를 셀 때는 "Countif"를 쓴다. 

(Ex. 5,000불 이상 소득자는 몇명인가?) 


하지만, 그 조건이 여러개일 경우에는 Countif로는 한계가 있고, "Sumproduct"가 유용하다. 

아래 예제 함수수식을 보면 상당히 복잡해보이지만, 실은 간단하다. 


그리고 처음 만들기가 살짝 번거롭지만, 실제로 반복되는 업무인 경우에는 실수를 없앤다는 측면에서도 활용하면 좋을 듯 싶다.


= Sumproduct((해당영역="조건1")*(해당영역="조건2")*......*(해당영역="조건n")*1))


예를들면, 아래와 같은 Data가 있다고 하자.



B15      C15 D15

A16 이름 좋아하는 과일 성별

A17 서울 사과

A18 부산

A19 서울 포도

A20 대전 사과

A21 서울 포도

A22 서울 포도

A23 대전 사과

A24 대전 포도

A25 서울

A26 부산 포도

A27 대전 사과


1. 각 지역별로 좋아하는 과일의 인원 수를 구할 경우는

지역 사과 포도 Total

서울 1 1 3 5

대전 3 0 1 4

부산 0 1 1 2

Total 4 2 5 11


    각 Cell 별 함수는 다음과 같다.

   (서울,사과)=SUMPRODUCT(($B$17:$B$27="서울")*($C$17:$C$27="사과")*1)

   (서울, 배)  =SUMPRODUCT(($B$17:$B$27="서울")*($C$17:$C$27="배")*1)

  (서울,포도)=SUMPRODUCT(($B$17:$B$27="서울")*($C$17:$C$27="포도")*1)

  (대전,사과)=SUMPRODUCT(($B$17:$B$27="대전")*($C$17:$C$27="사과")*1)

   (대전, 배)  =SUMPRODUCT(($B$17:$B$27="대전")*($C$17:$C$27="배")*1)

  (대전,포도)=SUMPRODUCT(($B$17:$B$27="대전")*($C$17:$C$27="포도")*1)

   (부산,사과)=SUMPRODUCT(($B$17:$B$27="부산")*($C$17:$C$27="사과")*1)

  (부산, 배)  =SUMPRODUCT(($B$17:$B$27="부산")*($C$17:$C$27="배")*1)

   (부산,포도)=SUMPRODUCT(($B$17:$B$27="부산")*($C$17:$C$27="포도")*1)


2. 만약, 똑 같이 위 표를 작성하되 여자를 제외할 경우에는

지역 사과 포도 Total

서울 1 1 2 4

대전 2 0 0 2

부산 0 0 0 0

Total 3 1 2 6


   (서울,사과)=SUMPRODUCT(($B$16:$B$26="서울")*($C$16:$C$26="사과")*1)- 

                    SUMPRODUCT(($B$16:$B$26="서울")*($C$16:$C$26="사과")* 

                    ($D$16:$D$26="여")*1)


   (서울, 배)  =SUMPRODUCT(($B$16:$B$26="서울")*($C$16:$C$26="배")*1)-

                    SUMPRODUCT(($B$16:$B$26="서울")*($C$16:$C$26="배")* 

                    ($D$16:$D$26="여")*1)


  (서울,포도)=SUMPRODUCT(($B$16:$B$26="서울")*($C$16:$C$26="포도")*1)-

                    SUMPRODUCT(($B$16:$B$26="서울")*($C$16:$C$26="포도")*

                    ($D$16:$D$26="여")*1)


  (대전,사과)=SUMPRODUCT(($B$16:$B$26="대전")*($C$16:$C$26="사과")*1)-

                    SUMPRODUCT(($B$16:$B$26="대전")*($C$16:$C$26="사과")*

                   ($D$16:$D$26="여")*1)    

   

   (대전, 배)  =SUMPRODUCT(($B$16:$B$26="대전")*($C$16:$C$26="배")*1)-

                    SUMPRODUCT(($B$16:$B$26="대전")*($C$16:$C$26="배")*

                    ($D$16:$D$26="여")*1)


  (대전,포도)=SUMPRODUCT(($B$16:$B$26="대전")*($C$16:$C$26="포도")*1)-

                    SUMPRODUCT(($B$16:$B$26="대전")*($C$16:$C$26="포도")*

                    ($D$16:$D$26="여")*1)


   (부산,사과)=SUMPRODUCT(($B$16:$B$26="부산")*($C$16:$C$26="사과")*1)-

                    SUMPRODUCT(($B$16:$B$26="부산")*($C$16:$C$26="사과")*

                    ($D$16:$D$26="여")*1)


  (부산, 배)  =SUMPRODUCT(($B$16:$B$26="부산")*($C$16:$C$26="배")*1)-

                    SUMPRODUCT(($B$16:$B$26="부산")*($C$16:$C$26="배")*          

                    ($D$16:$D$26="여")*1)    

  

  (부산,포도)=SUMPRODUCT(($B$16:$B$26="부산")*($C$16:$C$26="포도")*1)-

                    SUMPRODUCT(($B$16:$B$26="부산")*($C$16:$C$26="포도")*

                    ($D$16:$D$26="여")*1)크리에이티브 커먼즈 라이센스