엑셀 중복값 표시, COUNTIF 하나로 같은 주문번호 잡는 초보 체크리스트

엑셀 중복값 표시를 검색하는 분들이 가장 많이 만나는 상황은 주문번호, 거래처코드, 사번처럼 원래 한 번만 있어야 하는 값이 여러 번 들어간 경우입니다. 특히 COUNTIF 함수로 중복 개수를 세려고 했는데 전부 1로 나오거나, 복사했더니 범위가 같이 밀려서 결과가 이상해지는 일이 자주 생깁니다.

이번 글은 어려운 기능 없이 COUNTIF와 IF만으로 같은 주문번호가 몇 번 나왔는지 확인하고, 중복이면 바로 표시하는 방법을 차근차근 정리해 보겠습니다. 실제 파일에서 어느 셀에 입력해야 하는지, 범위는 어디부터 어디까지 잡아야 하는지까지 초보자 기준으로 설명하겠습니다.

어떤 표에서 중복을 찾는 상황일까?

예를 들어 쇼핑몰 주문내역을 정리하는 파일이 있다고 하겠습니다. 데이터는 3행부터 시작하고, 2행은 제목 행입니다. A열은 주문일, B열은 주문번호, C열은 고객명, D열은 상품명, E열은 금액, F열은 중복확인 열로 사용할 예정입니다.

이 표에서는 B열 주문번호가 중복인지 확인해야 합니다. 눈으로 보면 O-1001과 O-1002가 두 번씩 보이지만, 실제 업무 파일은 행이 수백 개라서 눈검토로 찾기 어렵습니다.

COUNTIF는 무슨 뜻이고 어디에 입력할까?

COUNTIF는 조건에 맞는 셀의 개수를 세는 함수입니다. 말로 풀면 지정한 범위 안에서 이 값이 몇 번 나왔는지 세어 줘라는 뜻입니다.

이번 예제에서는 주문번호가 들어 있는 범위가 B3:B8입니다. 그래서 F3 셀에 아래 수식을 입력하고, F8까지 아래로 복사하면 됩니다.

=COUNTIF($B$3:$B$8,B3)

여기서 $B$3:$B$8은 전체 주문번호 범위입니다. 앞뒤에 붙은 달러 표시($)는 수식을 아래로 복사해도 범위가 움직이지 않게 고정한다는 뜻입니다.

반면 뒤쪽의 B3은 현재 행의 주문번호입니다. F3에서는 B3의 주문번호를 세고, F4로 복사하면 자동으로 B4를 세게 됩니다. 이 차이가 처음에는 가장 헷갈립니다.

왜 달러 표시를 안 붙이면 결과가 이상해질까?

초보자분들이 가장 많이 하는 실수는 아래처럼 범위를 그냥 입력하는 것입니다.

=COUNTIF(B3:B8,B3)

F3에만 있을 때는 맞아 보입니다. 하지만 이 수식을 F4로 복사하면 엑셀이 알아서 범위를 B4:B9로 바꿔 버립니다. F5에서는 B5:B10처럼 계속 내려갑니다.

그러면 위쪽에 있던 주문번호가 계산 범위에서 빠지기 때문에 중복인데도 1로 나오거나, 마지막 쪽 값만 이상하게 계산될 수 있습니다. 중복 확인에서는 전체 목록 범위를 고정해야 하므로 $B$3:$B$8처럼 달러 표시를 붙이는 것이 안전합니다.

숫자만 보이면 불편할 때, 중복이라고 표시하려면?

COUNTIF 결과가 2이면 같은 주문번호가 2번 있다는 뜻이고, 1이면 한 번만 있다는 뜻입니다. 그런데 실무에서는 숫자보다 중복 또는 정상이라고 표시하는 편이 더 보기 쉽습니다.

F3 셀에 아래 수식을 입력한 뒤 F8까지 복사해 보세요.

=IF(COUNTIF($B$3:$B$8,B3)>1,"중복","정상")

이 수식은 주문번호가 전체 범위에서 1번보다 많이 나오면 중복이라고 표시합니다. 그렇지 않으면 정상이라고 표시합니다. 여기서 >1은 1보다 크다는 뜻입니다.

실제 파일에서 데이터가 3행부터 500행까지라면 범위만 아래처럼 바꾸면 됩니다.

=IF(COUNTIF($B$3:$B$500,B3)>1,"중복","정상")

중요한 것은 첫 번째 범위는 전체 데이터 범위로 고정하고, 두 번째 값은 현재 행의 주문번호 셀로 둔다는 점입니다. 내 파일에서 주문번호가 C열에 있다면 $C$3:$C$500, C3처럼 열만 바꿔 주면 됩니다.

중복값 확인 전에 어디를 먼저 봐야 할까?

수식이 틀린 것 같을 때 바로 지우고 다시 만들기보다, 아래 항목을 먼저 확인하면 원인을 훨씬 빨리 찾을 수 있습니다. 특히 다운로드한 주문내역이나 다른 사람이 만든 파일은 표시 형식과 공백 때문에 결과가 달라질 때가 있습니다.

특정 주문번호가 몇 건인지 따로 보고 싶다면?

중복 표시 열을 만드는 것과 별개로, 특정 주문번호가 몇 번 들어갔는지 따로 확인하고 싶을 때도 많습니다. 예를 들어 H2 셀에 확인할 주문번호를 입력하고, I2 셀에 개수를 표시하는 방식입니다.

H2 셀에는 O-1001처럼 찾을 주문번호를 직접 입력합니다. I2 셀에는 아래 수식을 입력합니다.

=COUNTIF($B$3:$B$8,H2)

이 수식은 B3:B8 범위에서 H2에 적힌 주문번호가 몇 번 나오는지 세어 줍니다. H2 값을 O-1002로 바꾸면 I2 결과도 바로 바뀝니다.

이 방식은 고객센터에서 고객이 주문번호를 알려줬을 때, 해당 주문번호가 파일 안에 몇 건 있는지 빠르게 확인할 때 유용합니다. 주문번호가 한 번만 있어야 하는 업무라면 결과가 2 이상일 때 원본 표를 다시 확인하면 됩니다.

중복된 주문의 금액 합계까지 보고 싶다면?

중복이 있다는 사실만으로는 부족할 때가 있습니다. 같은 주문번호가 두 번 들어갔고, 그 금액까지 중복으로 합산되어 월매출이 부풀었는지 확인해야 할 때입니다.

이럴 때는 SUMIF를 함께 사용할 수 있습니다. H2에 확인할 주문번호가 있고, J2에 해당 주문번호의 금액 합계를 표시한다고 해 보겠습니다.

=SUMIF($B$3:$B$8,H2,$E$3:$E$8)

첫 번째 범위 $B$3:$B$8은 조건을 검사할 주문번호 범위입니다. 두 번째 H2는 찾을 주문번호입니다. 세 번째 $E$3:$E$8은 합계를 낼 금액 범위입니다.

COUNTIF가 건수를 세는 함수라면, SUMIF는 조건에 맞는 금액을 더하는 함수라고 생각하면 됩니다. 같은 구조라서 COUNTIF에 익숙해지면 SUMIF도 훨씬 쉽게 이해됩니다.

색으로 바로 보이게 하고 싶을 때는?

수식 결과를 F열에 표시하는 것도 좋지만, 원본 주문번호 자체에 색이 들어가면 더 빨리 찾을 수 있습니다. 이때는 조건부 서식을 사용하면 됩니다.

B3:B8 범위를 먼저 선택합니다. 그다음 메뉴에서 홈 탭, 조건부 서식, 새 규칙, 수식을 사용하여 서식을 지정할 셀 결정을 차례로 선택합니다. 수식 입력 칸에는 아래 수식을 넣습니다.

=COUNTIF($B$3:$B$8,B3)>1

그다음 서식 버튼을 눌러 채우기 색을 연한 노랑이나 연한 빨강으로 지정하면 됩니다. 이 방식은 B3:B8 중에서 두 번 이상 등장한 주문번호 셀에만 색을 칠해 줍니다.

조건부 서식에서도 원리는 같습니다. 전체 범위는 $B$3:$B$8로 고정하고, 현재 행 기준 셀은 B3으로 둡니다. 만약 선택 범위를 B3부터 시작했는데 수식은 B2로 적으면 색칠 위치가 어긋날 수 있으니 시작 셀을 꼭 맞춰 주세요.

적용 전에 확인할 것

중복값 확인은 함수 자체보다 범위 선택에서 실수가 많이 납니다. 데이터가 3행부터 시작하는지, 주문번호가 어느 열에 있는지, 마지막 행이 어디인지 먼저 확인한 뒤 수식을 넣는 습관을 들이면 좋습니다.

가장 기본형은 =COUNTIF($B$3:$B$8,B3)입니다. 중복이라는 글자로 표시하고 싶으면 IF를 감싸서 =IF(COUNTIF($B$3:$B$8,B3)>1,"중복","정상")처럼 사용하면 됩니다.

내 파일에 적용할 때는 B열을 실제 중복 검사 열로, 3행과 8행을 실제 데이터 시작 행과 마지막 행으로 바꾸면 됩니다. 중복값 표시는 단순한 기능처럼 보이지만, 주문 중복 등록, 이중 청구, 코드 중복 생성 같은 실무 사고를 초기에 잡아 주는 꽤 든든한 확인 도구입니다.