[엑셀] SUMPRODUCT 함수 이용, 조건에 따른 중복항목 제외 후 카운트하는 함수
http://flogsta.tistory.com/427
예를 들면 수식 =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)크리에이티브 커먼즈 라이센스