엑셀 중복값 개수 세기, 이렇게 확인하면 돼요

사무2026년 8월 23일약 3분

한눈에 보기

=SUMPRODUCT((COUNTIF(범위, 범위)>1)*1)로 중복에 해당하는 행 수를 세요.
COUNTIF(범위, 범위)가 행마다 등장 횟수를 계산하고, 1보다 큰 것만 세는 원리예요.
'중복된 값의 종류 수'와 '중복에 해당하는 행 수'는 다른 숫자예요.

목차 (5)

명단을 관리하다 보면 같은 이름이 실수로 중복 입력되지 않았는지 확인해야 할 때가 있어요. 몇 명이 몇 번씩 중복됐는지 하나하나 보기 전에, 먼저 '전체 중 몇 개 행이 중복(같은 값이 2번 이상 나온 행)에 해당하는지'부터 한 번에 세고 싶다면 COUNTIF와 SUMPRODUCT를 함께 쓰면 돼요.

중복 개수를 세는 원리

원리는 이래요. 먼저 COUNTIF(범위, 범위)로 각 행의 값이 전체 범위 안에서 몇 번 나오는지를 행마다 배열로 계산해요. 그다음 이 값이 1보다 큰(=2번 이상 나온) 행만 SUMPRODUCT로 세어요.

참가자(A열) 중 이름이 2번 이상 나온 행이 총 몇 개인지 D2에서 세어요.

D2fx=SUMPRODUCT((COUNTIF(A2:A6,A2:A6)>1)*1)

	A	D
1	참가자	중복 행 개수
2	김민준	4
3	이서연
4	김민준
5	박도윤
6	이서연

그대로 복사해서 범위만 바꿔 쓰세요

=SUMPRODUCT((COUNTIF(범위,범위)>1)*1)

한글로 적힌 자리는 직접 바꿔 넣어야 하는 값이에요. 복사한 뒤 자기 파일의 범위·조건으로 고쳐서 쓰세요.

예제 결과 풀어보기

COUNTIF(A2:A6,A2:A6)를 행별로 풀어보면 김민준=2번, 이서연=2번, 김민준=2번, 박도윤=1번, 이서연=2번이 나와요. 이 중 1보다 큰(2번 이상인) 행은 김민준·이서연·김민준·이서연 4개이고, 박도윤만 유일하게 1번이라 제외돼요. 그래서 결과는 4예요.

중복된 행 수와 중복된 값 종류는 다릅니다

이 공식은 '몇 명이 중복됐는지'가 아니라 '중복값이 포함된 행이 총 몇 개인지'를 세는 거예요. 만약 몇 종류(김민준, 이서연처럼 서로 다른 이름 기준)의 값이 중복됐는지 알고 싶다면 =SUMPRODUCT((COUNTIF(A2:A6,A2:A6)>1)/COUNTIF(A2:A6,A2:A6)) 공식을 쓰면 되는데, 같은 예제에 적용하면 결과는 2(김민준, 이서연)예요.

수식 없이 조건부 서식으로 확인하기

중복된 셀을 눈으로 바로 확인하고 싶다면 수식 없이도 가능해요. 범위를 선택한 뒤 홈 > 조건부 서식 > 셀 강조 규칙 > 중복 값을 누르면 버튼 몇 번만으로 중복된 셀에 자동으로 색이 칠해져요. 중복 자체를 아예 지우고 싶을 때는 데이터 탭 > 데이터 도구 > 중복된 항목 제거를 쓰면 돼요.

목적이 '세기'가 아니라 '중복 없는 목록'이라면

지금까지는 중복이 몇 건인지 세는 방법이었어요. 그런데 필요한 게 숫자가 아니라 겹치는 걸 뺀 명단이라면 수식을 이렇게 복잡하게 짤 필요가 없어요. 최신 버전 엑셀(Microsoft 365, Excel 2021 이상)에는 UNIQUE 함수가 있어서 범위만 넣으면 중복을 뺀 목록이 바로 펼쳐져요. 원본을 건드리지 않는다는 게 '중복된 항목 제거'와 결정적으로 달라요 — 지우기 전에 '중복을 빼면 몇 명이 남지?'를 미리 확인해볼 수 있거든요. 쓰는 법과 버전 제약은 엑셀 UNIQUE 함수로 중복 없는 목록만 뽑아내는 법에 정리해뒀어요.

지우는 쪽은 되돌리기가 어려우니 순서를 지키는 게 좋아요 — 엑셀 중복값 찾아서 지우는 법에 미리 확인하는 절차까지 적어뒀어요. 구글시트에서 같은 작업을 한다면 구글시트 중복값 개수 세기를 보세요. 함수는 같지만 메뉴 경로 이름이 달라요.