Skip to content
조회 수 14095 추천 수 0 댓글 0
?

단축키

Prev이전 문서

Next다음 문서

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

단축키

Prev이전 문서

Next다음 문서

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

[엑셀] 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)크리에이티브 커먼즈 라이센스

로그인 후 댓글쓰기가 가능합니다.

?

http://urin79.com

우린친구블로그

sketchbook5, 스케치북5

sketchbook5, 스케치북5

나눔글꼴 설치 안내


이 PC에는 나눔글꼴이 설치되어 있지 않습니다.

이 사이트를 나눔글꼴로 보기 위해서는
나눔글꼴을 설치해야 합니다.

설치 취소