[엑셀함수#34]중복을 제외한 데이터 개수 구하기(COUNTIF,SUMPRODUCT)

이번 글에서는 COUNTIF와 SUMPRODUCT 함수를 사용하여 중복된 데이터의 개수를 구하는 방법에 대하여 말씀드리겠습니다.

 

1. 중복된 데이터를 제외할 때 써야 하는 상황

• 많은 양의 데이터를 입력하여 분석하다 보면 종종 동일한 데이터가 중복 입력되는 경우가 발견됩니다
• 단순히 COUNT 함수 계열을 이용하여 데이터의 개수를 구하면 중복 입력된 데이터도 개별 데이터로 인식하여 데이터의 개수를 계산합니다.
• 이때 중복된 데이터를 제외한 데이터의 개수를 구하려면 COUNTIF 함수와 SUMPRDUCT 함수를 이용해야 합니다.

 

2.COUNTIF, SUMPRODUCT 사용방법

• 아래 사진을 보시면 2020년 하반기 지점별 재고현황을 지점과 월별로 기록한 엑셀 사진을 보실 수 있습니다.

 

엑셀 1

• 여기서 1번 박스를 보시면 양양과 신림 등 여러 지점들이 중복하여 들어간 것을 확인하실 수 있습니다.

• 이럴 때에는 전체 거래지점 수로는 하나하나 세야 하겠지만, 얼마나 많은 지역의 지점이 있는가를 한눈에 보고 싶을 때에는 중복된 제점을 제거하여 세야 합니다.
• 이럴 때에 사용할 수 있는 것이 바로 COUNTIF와 SUMPRODUCT의 응용입니다.

 

엑셀 2

• 먼저 그냥 전체 거래지점수를 출력하는 방법에 대하여 말씀드리겠습니다.

• 2번 박스로 표시한 셀에 셀 포인터를 위치시켜줍니다.
• =COUNTA(B4:B17)을 입력한 두에 ENTER를 눌러줍니다.
• 여기서 COUNTA는 숫자, 문자 상관없이 셀에 데이터만 존재한다면 그 셀을 COUNT 하는 함수입니다. 숫자 데이터가 입력된 셀만을 세는 COUNT 와는 다른 함수이지요.

 

엑셀3 

• 위 엑셀을 보시면 전체 거래 지점수가 제대로 입력된 것을 보실 수 있습니다.

• 그러면 이제, 중복 제거 거래 지점수를 출력하는 방법에 대하여 말씀드리겠습니다.

 

• 3번 박스로 표시한 셀에 셀 포인터를 위치시켜줍니다.
• =SUMPRODUCT(1/CSPOUNTIF(B4:B17, B4:B17))을 입력하여 줍니다.
• 이 수식은 각 범위에서 각 데이터가 해당 영역에서 몇 개 있는지를 구한 뒤에 중복된 값을 제거하는 방식입니다.

 

엑셀 4

• 여기까지 모두 입력하셨다면 위에 사진의 2번 박스에 보이는 것처럼 중복된 지점은 제거하고 셈을 하여 출력되는 것을 보실 수 있습니다.

이상 여기까지 COUNIF와 SUMPRODUCT 함수를 사용하여 중복을 제외한 데이터 개수를 구하는 방법이었습니다.