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

http://flogsta.tistory.com/427

한 가지 조건을 만족할 경우 그 갯수를 셀 때는 "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)크리에이티브 커먼즈 라이센스