엑셀 중복 주문번호 때문에 합계가 안 맞을 때: COUNTIF로 첫 행만 더하는 초보 체크법

엑셀에서 주문번호별 합계를 냈는데 ERP보다 금액이 크게 나오거나, 배송비 합계가 이상하게 불어나는 경우가 있습니다. 특히 쇼핑몰 주문내역처럼 주문번호는 같은데 상품이 여러 줄로 내려오는 파일에서 자주 생기는 문제입니다.

초보자 입장에서는 “SUM으로 더했는데 왜 틀리지?” 하고 헷갈리기 쉽습니다. 엑셀은 같은 주문번호인지 아닌지를 알아서 판단하지 않고, 보이는 숫자를 전부 더하기 때문입니다. 오늘은 엑셀 중복 주문번호 합계 안 맞음 상황에서 COUNTIF로 첫 행만 표시하고, 중복 행은 제외해서 합계를 맞추는 방법을 차근차근 정리해 보겠습니다.

왜 주문번호 중복 때문에 합계가 커질까?

예를 들어 한 고객이 한 번 주문하면서 상품을 2개 샀다고 해보겠습니다. 주문번호는 하나인데, 상품이 2개라서 엑셀에는 2줄로 표시됩니다. 이때 상품금액은 각 상품별로 다르니 줄마다 더하는 것이 맞지만, 배송비나 쿠폰할인처럼 주문 1건에 한 번만 적용되는 금액은 첫 줄에서만 더해야 합니다.

그런데 원본 파일에는 배송비가 같은 주문번호의 모든 줄에 반복해서 들어오는 경우가 많습니다. 이 상태에서 배송비 열을 그냥 SUM 하면 배송비가 2배, 3배로 잡힙니다.

예시는 1행에 제목이 있고, 실제 데이터는 2행부터 8행까지 있다고 가정하겠습니다. 즉 원본 데이터 범위는 A2:F8이고, 새로 계산할 열은 G2:H8입니다.

이 표에서 F열 배송비를 그냥 더하면 19,500원이 됩니다. 하지만 실제 주문번호 기준 배송비는 O-1001 3,000원, O-1002 3,000원, O-1003 2,500원, O-1004 3,000원이므로 총 11,500원이 맞습니다.

어떤 열을 먼저 확인해야 할까?

먼저 중복을 판단할 기준 열을 정해야 합니다. 이 예제에서는 A열 주문번호가 기준입니다. 주문번호가 같으면 같은 주문으로 보고, 같은 주문번호 중 가장 위에 처음 나온 행만 “첫행”이라고 표시하겠습니다.

여기서 COUNTIF 함수가 나옵니다. COUNTIF는 “정해진 범위에서 조건에 맞는 셀이 몇 개인지 세는 함수”입니다. 그런데 오늘은 전체 개수를 세는 용도보다, 현재 행까지 같은 주문번호가 몇 번 나왔는지 확인하는 용도로 사용합니다.

G2 셀에 아래 수식을 입력합니다. 위치는 데이터 첫 줄인 2행입니다.

=IF(COUNTIF($A$2:A2,A2)=1,"첫행","중복")

이 수식은 처음 보면 $A$2:A2 부분이 헷갈립니다. 앞의 $A$2는 고정되어 있고, 뒤의 A2는 아래로 복사하면서 A3, A4처럼 늘어납니다. 그래서 G5까지 내려가면 COUNTIF($A$2:A5,A5)처럼 바뀌어, 2행부터 현재 행까지 같은 주문번호가 몇 번 나왔는지 세게 됩니다.

결과가 1이면 처음 나온 주문번호이므로 “첫행”, 2 이상이면 이미 위에 같은 주문번호가 있었으므로 “중복”이라고 표시합니다. G2 수식을 입력한 뒤, G8까지 아래로 복사합니다.

배송비는 첫 행만 남기고 나머지는 0으로 만들기

이제 H열에 정산배송비를 만들겠습니다. H열은 실제로 합계에 사용할 배송비입니다. G열이 “첫행”이면 F열 배송비를 가져오고, “중복”이면 0으로 처리합니다.

H2 셀에 아래 수식을 입력합니다.

=IF(G2="첫행",F2,0)

이 수식은 아주 단순합니다. G2가 첫행이면 F2의 배송비를 그대로 표시하고, 아니면 0을 표시합니다. H2 수식을 H8까지 복사하면 중복 주문번호의 반복 배송비가 사라집니다.

전체 배송비 합계를 확인할 결과 셀을 예를 들어 K2라고 하겠습니다. K2에는 아래 수식을 입력합니다.

=SUM(H2:H8)

결과는 11,500원이 나와야 합니다. 만약 F열을 그냥 SUM 했다면 19,500원이 나오므로, 중복 주문번호 때문에 8,000원이 과하게 잡힌 셈입니다.

체크리스트: 합계가 계속 안 맞을 때 어디를 봐야 할까?

수식을 넣었는데도 결과가 이상하면 아래 순서대로 확인해 보세요. 초보자 파일에서 실제로 가장 많이 걸리는 부분들입니다.

특히 COUNTIF 범위를 $A$2:$A$8처럼 전체 고정 범위로 넣으면 원하는 결과가 나오지 않습니다. 오늘 방식은 “현재 행까지” 세야 하므로 $A$2:A2처럼 시작점만 고정해야 합니다.

월별 배송비 합계까지 보고 싶다면?

실무에서는 전체 배송비만 보는 경우보다, 특정 월의 배송비만 보고 싶은 경우가 많습니다. 이때는 H열 정산배송비를 만든 뒤 SUMIFS로 월 조건을 걸면 됩니다.

예를 들어 J2 셀에 기준월의 시작일인 2026-07-01을 입력해 둔다고 하겠습니다. K2 셀에는 7월 정산배송비 합계를 표시합니다. 날짜는 B열 주문일을 기준으로 보고, 금액은 H열 정산배송비를 더합니다.

=SUMIFS(H2:H8,B2:B8,">="&J2,B2:B8,"<"&DATE(YEAR(J2),MONTH(J2)+1,1))

이 수식에서 H2:H8은 더할 금액 범위입니다. B2:B8은 날짜 조건을 검사할 범위입니다. J2는 기준월 시작일입니다. 자기 파일에서 데이터가 500행까지 있다면 H2:H8은 H2:H500, B2:B8은 B2:B500처럼 바꾸면 됩니다.

DATE(YEAR(J2),MONTH(J2)+1,1)은 다음 달 1일을 만드는 부분입니다. 그래서 조건은 “J2 이상이고, 다음 달 1일보다 작은 날짜”가 됩니다. 7월 1일부터 7월 31일까지를 안전하게 잡는 방식입니다.

주문 건수도 같이 세고 싶다면?

중복 제거 후 주문 건수를 세고 싶을 때도 G열을 활용하면 됩니다. 주문번호가 몇 줄인지가 아니라, 실제 주문이 몇 건인지 보고 싶다면 “첫행” 개수를 세면 됩니다.

예를 들어 K3 셀에 전체 주문 건수를 표시하려면 아래 수식을 입력합니다.

=COUNTIF(G2:G8,"첫행")

예제에서는 O-1001, O-1002, O-1003, O-1004 총 4건이므로 결과는 4가 나와야 합니다. 상품 줄 수는 7줄이지만 주문 건수는 4건이라는 점을 구분해야 합니다.

월별 주문 건수까지 보고 싶다면 COUNTIFS를 사용합니다. J2에 기준월 시작일이 있을 때, K4 셀에 7월 주문 건수를 표시하는 수식은 아래와 같습니다.

=COUNTIFS(G2:G8,"첫행",B2:B8,">="&J2,B2:B8,"<"&DATE(YEAR(J2),MONTH(J2)+1,1))

COUNTIFS는 조건을 여러 개 걸어서 개수를 세는 함수입니다. 여기서는 첫 번째 조건으로 G열이 “첫행”인지 보고, 두 번째와 세 번째 조건으로 주문일이 기준월 안에 있는지 확인합니다.

초보자가 자주 헷갈리는 포인트

첫 번째는 상품금액과 배송비를 같은 방식으로 더하는 실수입니다. 상품금액은 상품 줄마다 발생하는 금액이므로 E열을 그대로 SUM해도 되는 경우가 많습니다. 하지만 배송비, 주문쿠폰, 결제수수료처럼 주문 단위 금액은 중복 여부를 먼저 봐야 합니다.

두 번째는 정렬을 바꾸면 첫행이 달라질까 봐 걱정하는 경우입니다. 오늘 방식은 같은 주문번호 중에서 현재 정렬 기준으로 가장 먼저 나온 행을 첫행으로 봅니다. 배송비가 같은 주문번호마다 동일하게 반복되어 있다면 어느 행이 첫행이 되어도 합계에는 문제가 없습니다.

세 번째는 주문번호가 비어 있는 행입니다. 빈 주문번호가 여러 줄 있으면 빈칸도 같은 값으로 인식되어 첫 번째 빈칸만 첫행, 나머지는 중복으로 표시될 수 있습니다. 원본에 빈 행이나 합계 행이 섞여 있다면 먼저 삭제하거나 제외한 뒤 수식을 넣는 것이 좋습니다.

내 파일에 적용할 때 바꿔야 하는 셀 주소

예제에서는 주문번호가 A열, 배송비가 F열, 보조열이 G열과 H열이었습니다. 내 파일에서 주문번호가 C열에 있다면 COUNTIF 수식의 A열 부분을 C열로 바꿔야 합니다. 배송비가 J열에 있다면 H열 수식에서 F2 대신 J2를 사용하면 됩니다.

셀 주소를 바꿀 때는 한 번에 여러 곳을 고치기보다, G열 첫행여부가 제대로 나오는지 먼저 확인하고 H열 정산배송비를 확인한 뒤 마지막에 합계를 내는 것이 안전합니다. 중간 보조열을 눈으로 확인할 수 있어서 초보자에게는 이 방식이 훨씬 덜 헷갈립니다.

최종 체크

배송비 합계가 안 맞을 때는 무작정 SUMIFS 수식만 고치기보다, 먼저 같은 주문번호가 여러 줄인지 확인해 보세요. 주문번호가 중복되어 있고 배송비가 반복 표시되어 있다면, COUNTIF로 첫 행만 남기는 보조열을 만드는 것이 가장 빠르고 안전합니다.

핵심은 세 가지입니다. 주문번호 기준 열을 정하고, COUNTIF로 첫행과 중복을 나누고, 실제 합계는 원본 배송비가 아니라 정산배송비 열로 계산하는 것입니다. 이 구조만 잡아두면 주문 건수, 월별 배송비, 고객별 정산금액까지 같은 방식으로 확장할 수 있습니다.