엑셀 배송비 정산 SUMIFS 0 나옴? 송장번호 앞자리 0·출고일 텍스트·권역 운임표까지 검산하기

엑셀 배송비 정산 파일에서 SUMIFS 합계가 0으로 나오거나, 택배사 청구금액과 내부 계산금액이 몇 천 원씩 계속 어긋나는 경우가 있습니다. 특히 출고일이 20260703처럼 숫자처럼 들어오고, 거래처코드나 송장번호 앞자리 0이 사라진 파일에서는 눈으로 봐서는 맞아 보이는데 수식은 전혀 다른 값으로 계산합니다.

이번 예제는 물류팀이나 온라인몰 정산 담당자가 자주 만나는 배송비 검산 상황입니다. 단순히 SUMIFS 하나만 넣는 것이 아니라, 날짜 정리 → 거래처코드 정리 → 권역 운임표 조회 → 조건별 합계 → 차이 검증 순서로 안전하게 잡아보겠습니다.

실무에서 자주 터지는 배송비 정산 오류 상황

예를 들어 출고내역 시트에 아래와 같은 표가 있다고 하겠습니다. 데이터는 2행부터 5000행까지 있고, A열부터 G열까지 원본 자료가 들어옵니다.

오른쪽에는 정리용 열을 추가합니다. H열은 정상출고일, I열은 정리거래처코드, J열은 계산배송비, K열은 운임조회키로 사용할 예정입니다.

왜 SUMIFS가 0이 나오거나 정산금액이 틀릴까?

배송비 정산 오류의 대부분은 계산식 자체보다 조건값의 형태가 서로 다르기 때문입니다. 화면에는 2026-07-03처럼 보이지만 실제 값은 문자일 수 있고, 거래처코드 001245가 어느 행에서는 1245로 저장되어 있을 수 있습니다.

예를 들어 P2셀에 거래처코드 001245, Q2셀에 기준월 2026-07-01을 입력해 두고 아래처럼 SUMIFS를 쓰면 결과가 0으로 나올 수 있습니다.

=SUMIFS($G:$G,$C:$C,$P$2,$A:$A,>=&$Q$2,$A:$A,<=&EOMONTH($Q$2,0))

이 수식이 틀린 이유는 간단합니다. C열에는 1245와 001245가 섞여 있고, A열 출고일은 날짜가 아니라 20260703이라는 8자리 값으로 들어온 행이 있기 때문입니다. 그래서 먼저 원본을 직접 고치기보다, 옆 열에 검산용 정리값을 만드는 방식이 안전합니다.

출고일이 20260703으로 들어온 경우 DATE·LEFT·MID·RIGHT로 날짜 정리

H2셀에 정상출고일을 만들겠습니다. A열의 출고일이 8자리 숫자 또는 문자라면 DATE, LEFT, MID, RIGHT로 연월일을 분리하고, 이미 날짜로 들어온 값은 날짜값으로 유지합니다.

=IFERROR(IF(LEN(A2&"")=8,DATE(LEFT(A2&"",4),MID(A2&"",5,2),RIGHT(A2&"",2)),A2*1),"")

이 수식을 H2에 입력한 뒤 H5000까지 복사합니다. 수식의 핵심은 A2&""로 값을 문자처럼 다룬 다음, 길이가 8자리이면 앞 4자리는 연도, 가운데 2자리는 월, 오른쪽 2자리는 일로 잘라 DATE 함수에 넣는 것입니다.

H열의 표시 형식은 yyyy-mm-dd로 바꿔두면 확인하기 좋습니다. H열에 빈칸이 생긴다면 A열 출고일에 공백, 점, 슬래시가 섞였는지 먼저 확인해야 합니다.

거래처코드 앞자리 0 사라짐은 TEXT로 6자리 고정

이번에는 I2셀에 정리거래처코드를 만듭니다. 거래처코드는 항상 6자리라고 가정하겠습니다. C열에 1245처럼 숫자로 들어온 값도 001245로 맞춰야 P2 조건과 SUMIFS가 제대로 만납니다.

=IFERROR(TEXT(C2,"000000"),C2&"")

이 수식을 I2부터 I5000까지 복사합니다. 거래처코드가 숫자로 저장되어 있으면 TEXT 함수가 6자리로 맞춰줍니다. 만약 거래처코드에 영문이 섞인 회사라면 자리수 규칙이 다를 수 있으므로, 그때는 코드 체계를 먼저 확인한 뒤 적용해야 합니다.

권역별 운임표를 XLOOKUP 또는 INDEX MATCH로 연결하기

배송비 검산은 단순 합계만으로 끝나지 않습니다. 택배사 청구배송비가 맞는지 보려면 내부 운임표를 기준으로 계산배송비를 만들어야 합니다. 예제에서는 운임표 시트에 M열부터 R열까지 아래 구조로 자료가 있다고 하겠습니다.

운임표 시트의 R2셀에는 아래 수식을 넣고 R20까지 복사합니다. 기준월과 권역코드를 붙여서 조회키를 만드는 방식입니다.

=TEXT(M2,"yyyymm")&N2

출고내역 시트에서도 같은 방식의 키가 필요합니다. K2셀에 아래 수식을 입력하고 K5000까지 복사합니다.

=IF(H2="","",TEXT(H2,"yyyymm")&F2)

이제 J2셀에 계산배송비를 구합니다. 아래 수식은 K열 조회키로 운임표에서 기본운임, 기본박스, 추가박스운임을 가져온 뒤 박스수가 기본박스보다 많으면 추가 운임을 더합니다.

=IFERROR(XLOOKUP(K2,운임표!$R$2:$R$20,운임표!$P$2:$P$20)+IF(E2>XLOOKUP(K2,운임표!$R$2:$R$20,운임표!$O$2:$O$20),(E2-XLOOKUP(K2,운임표!$R$2:$R$20,운임표!$O$2:$O$20))*XLOOKUP(K2,운임표!$R$2:$R$20,운임표!$Q$2:$Q$20),0),0)

XLOOKUP을 쓰기 어려운 환경이라면 INDEX와 MATCH로도 같은 계산을 할 수 있습니다. 수식은 조금 길지만, 실무 파일 호환성을 생각하면 알아두면 좋습니다.

=IFERROR(INDEX(운임표!$P$2:$P$20,MATCH(K2,운임표!$R$2:$R$20,0))+IF(E2>INDEX(운임표!$O$2:$O$20,MATCH(K2,운임표!$R$2:$R$20,0)),(E2-INDEX(운임표!$O$2:$O$20,MATCH(K2,운임표!$R$2:$R$20,0)))*INDEX(운임표!$Q$2:$Q$20,MATCH(K2,운임표!$R$2:$R$20,0)),0),0)

여기서 중요한 점은 권역코드와 기준월을 따로따로 찾지 않고, 202607A처럼 하나의 조회키로 만들어 찾는다는 점입니다. 실무에서는 같은 권역이라도 월별 운임이 달라지는 경우가 많기 때문에, 권역만으로 VLOOKUP을 걸면 과거 운임이나 다음 달 운임이 잘못 들어갈 수 있습니다.

조건별 합계는 원본열이 아니라 정리열 기준으로 SUMIFS

이제 요약 영역을 만들겠습니다. P2셀에는 조회할 거래처코드 001245, Q2셀에는 기준월 2026-07-01을 입력합니다. R2셀에는 내부 계산배송비 합계, S2셀에는 택배사 청구배송비 합계, T2셀에는 차이를 표시하겠습니다.

R2셀에는 계산배송비 합계를 구합니다. 날짜 조건은 H열 정상출고일을 기준으로 잡고, 거래처 조건은 I열 정리거래처코드를 기준으로 잡습니다.

=ROUND(SUMIFS($J:$J,$I:$I,$P$2,$H:$H,>=&$Q$2,$H:$H,<=&EOMONTH($Q$2,0)),0)

S2셀에는 실제 청구배송비 합계를 구합니다.

=ROUND(SUMIFS($G:$G,$I:$I,$P$2,$H:$H,>=&$Q$2,$H:$H,<=&EOMONTH($Q$2,0)),0)

T2셀에는 차이를 계산합니다.

=R2-S2

차이가 0이면 내부 운임표 기준 계산금액과 택배사 청구금액이 일치합니다. 차이가 양수이면 내부 계산금액이 더 큰 것이고, 음수이면 택배사 청구금액이 더 큰 것입니다.

흔한 실수: 필터로 보이는 행만 보고 합계가 맞다고 판단하기

배송비 정산에서 자주 하는 실수는 필터로 거래처와 월을 걸어놓고, 화면에 보이는 금액만 대충 더해보는 것입니다. 필터 상태에서는 숨은 행, 잘못된 날짜, 코드 앞자리 0 문제를 놓치기 쉽습니다.

U2셀에는 해당 조건에 들어온 출고 건수를 COUNTIFS로 확인해 보세요. 합계가 안 맞을 때는 금액보다 건수 검산이 먼저입니다.

=COUNTIFS($I:$I,$P$2,$H:$H,>=&$Q$2,$H:$H,<=&EOMONTH($Q$2,0))

추가로 송장번호 중복도 확인해야 합니다. L2셀에 중복확인 열을 만들고 아래 수식을 넣으면 같은 송장번호가 몇 번 등장했는지 볼 수 있습니다.

=COUNTIFS($B:$B,B2)

L열 값이 2 이상인 행은 중복 청구 또는 분할 출고 가능성이 있습니다. 단, 실제로 한 주문이 여러 박스로 나뉜 정상 케이스도 있으므로 송장번호, 주문번호, 박스수 기준을 함께 확인해야 합니다.

점검 포인트: 어디를 바꾸면 내 파일에 맞을까?

이 예제에서 가장 먼저 바꿔야 할 곳은 범위입니다. 출고내역이 5000행보다 많다면 H2:K5000까지가 아니라 실제 마지막 행까지 수식을 복사해야 합니다. 운임표도 R20까지만 있는 것이 아니라면 수식 안의 운임표!$R$2:$R$20 같은 범위를 실제 운임표 마지막 행에 맞게 늘려야 합니다.

또 하나는 거래처코드 자리수입니다. 예제는 6자리라서 TEXT(C2,"000000")을 사용했습니다. 거래처코드가 8자리라면 00000000으로 바꿔야 하고, 지점코드까지 붙는 구조라면 본사코드와 지점코드를 분리할지 먼저 정해야 합니다.

마지막으로 기준월 Q2셀은 반드시 날짜값이어야 합니다. 2026-07이라고 문자로 입력하면 EOMONTH가 의도대로 작동하지 않을 수 있습니다. Q2에는 2026-07-01처럼 월의 첫날을 입력한 뒤 표시 형식만 yyyy-mm으로 바꾸는 편이 안전합니다.

반복 정산 파일은 VBA로 정리열 자동 채우기

매달 택배사에서 받은 파일을 붙여넣고 H열부터 K열까지 수식을 다시 채우는 작업이 반복된다면 VBA가 잘 맞습니다. 아래 코드는 출고내역 시트와 운임표 시트 이름이 그대로 있고, 출고내역의 A:G열 구조가 위 예제와 같을 때 사용할 수 있습니다.

실행 전에는 파일을 반드시 복사본으로 저장해 두세요. 매크로 실행 후에는 일반적인 Ctrl+Z 되돌리기가 되지 않습니다. 되돌려야 할 때는 저장하지 않고 파일을 닫거나, 실행 전 백업본을 다시 열어야 합니다.

Sub 배송비정산_정리열_자동채우기()
    Dim ws As Worksheet
    Dim rateWs As Worksheet
    Dim lastRow As Long
    Dim rateLastRow As Long

    Set ws = Worksheets("출고내역")
    Set rateWs = Worksheets("운임표")

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    rateLastRow = rateWs.Cells(rateWs.Rows.Count, "M").End(xlUp).Row

    If lastRow < 2 Then
        MsgBox "출고내역 데이터가 없습니다."
        Exit Sub
    End If

    ws.Range("H1").Value = "정상출고일"
    ws.Range("I1").Value = "정리거래처코드"
    ws.Range("J1").Value = "계산배송비"
    ws.Range("K1").Value = "운임조회키"
    rateWs.Range("R1").Value = "조회키"

    rateWs.Range("R2:R" & rateLastRow).Formula = "=TEXT(M2,""yyyymm"")&N2"

    ws.Range("H2:H" & lastRow).Formula = "=IFERROR(IF(LEN(A2&"""")=8,DATE(LEFT(A2&"""",4),MID(A2&"""",5,2),RIGHT(A2&"""",2)),A2*1),"""")"
    ws.Range("I2:I" & lastRow).Formula = "=IFERROR(TEXT(C2,""000000""),C2&"""")"
    ws.Range("K2:K" & lastRow).Formula = "=IF(H2="""","""",TEXT(H2,""yyyymm"")&F2)"

    ws.Range("J2:J" & lastRow).Formula = "=IFERROR(XLOOKUP(K2,운임표!$R$2:$R$" & rateLastRow & ",운임표!$P$2:$P$" & rateLastRow & ")+IF(E2>XLOOKUP(K2,운임표!$R$2:$R$" & rateLastRow & ",운임표!$O$2:$O$" & rateLastRow & "),(E2-XLOOKUP(K2,운임표!$R$2:$R$" & rateLastRow & ",운임표!$O$2:$O$" & rateLastRow & "))*XLOOKUP(K2,운임표!$R$2:$R$" & rateLastRow & ",운임표!$Q$2:$Q$" & rateLastRow & "),0),0)"

    ws.Range("H:H").NumberFormat = "yyyy-mm-dd"
    ws.Range("J:J").NumberFormat = "#,##0"

    MsgBox "배송비 정산용 정리열 입력이 완료되었습니다."
End Sub

이 코드는 원본 A:G열을 직접 바꾸지 않고 H:K열에 검산용 값을 채웁니다. 그래서 원본을 훼손할 위험은 낮지만, 기존에 H:K열에 다른 자료가 있었다면 덮어쓰게 됩니다. 실행 전 H:K열이 비어 있는지 꼭 확인하세요.

실무 체크: 합계보다 먼저 형태를 맞추기

배송비 정산에서 SUMIFS가 0으로 나오거나 금액이 계속 안 맞는다면, 수식을 바꾸기 전에 출고일과 거래처코드 형태를 먼저 맞춰야 합니다. 날짜는 H열처럼 실제 날짜값으로, 거래처코드는 I열처럼 자리수를 고정한 값으로 만든 뒤 조건별 합계를 걸면 오류가 훨씬 줄어듭니다.

정산 검산은 계산배송비 합계, 청구배송비 합계, 건수, 중복 송장 순서로 확인하면 빠릅니다. 이 흐름만 잡아두면 다음 달 파일이 와도 수식 범위만 늘려서 같은 방식으로 검산할 수 있습니다.