엑셀 리베이트 정산액이 안 맞을 때: 월매출 SUMIFS 0 나옴·구간요율 INDEX MATCH 검산법

엑셀에서 거래처별 리베이트 정산을 하다 보면 월매출은 분명히 있는데 SUMIFS 결과가 0으로 나오거나, 구간별 요율을 적용했더니 정산액이 ERP와 몇 천 원씩 어긋나는 경우가 있습니다. 특히 전표일이 텍스트로 섞여 있고, 거래처코드가 주문번호 안에 들어가 있거나, 요율표가 매출 구간별로 나뉘어 있으면 단순 합계로는 바로 맞추기 어렵습니다.

이번 예시는 실제 정산 파일에서 자주 만나는 구조로 잡아보겠습니다. 매출원장 시트에서 A열은 주문번호, B열은 전표일, C열은 거래처코드, D열은 거래처명, E열은 상품코드, F열은 수량, G열은 단가, H열은 매출액, I열은 상태입니다. 데이터는 5행부터 5000행까지 있고, N2에는 기준월, O2에는 정산할 거래처코드, P2부터 S2까지 결과를 계산합니다.

왜 SUMIFS는 0이 나오고 리베이트는 틀어질까?

가장 흔한 원인은 날짜입니다. B열 전표일이 2026-07-03처럼 보여도 실제 날짜가 아니라 문자로 저장되어 있으면, SUMIFS에서 7월 1일 이상, 7월 말일 이하 조건에 걸리지 않습니다. 화면에 날짜처럼 보인다고 해서 계산 가능한 날짜라는 뜻은 아닙니다.

두 번째는 거래처코드입니다. C열이 일부 비어 있는데 주문번호 안에는 코드가 들어 있는 파일이 많습니다. 이 상태에서 C열만 조건으로 걸면 실제 매출이 누락됩니다. 세 번째는 요율표입니다. 월매출이 1,000만 원 이상이면 2%, 3,000만 원 이상이면 3%처럼 구간이 올라가는 구조라면, 요율표의 최저월매출 열이 반드시 오름차순이어야 합니다.

날짜·거래처코드·금액을 헬퍼열에서 먼저 정리하자

바로 P2에 긴 SUMIFS를 넣기 전에 J열, K열, L열에 계산용 값을 따로 만들어두면 오류 찾기가 훨씬 쉬워집니다. 원본을 건드리지 않고 옆 열에 정리값을 만드는 방식이라 실무 파일에서도 안전합니다.

J4에는 정리일, K4에는 정리코드, L4에는 검산금액이라고 입력합니다. J5에는 아래 수식을 넣고 5000행까지 복사합니다. B열이 실제 날짜이면 그대로 쓰고, 2026.07.03 또는 2026-07-03 같은 텍스트이면 DATE 함수로 날짜를 다시 만듭니다.

=IFERROR(IF(B5>30000,B5,DATE(LEFT(B5,4),MID(B5,6,2),RIGHT(B5,2))),"")

이 수식에서 B5는 전표일입니다. 전표일 열이 다른 위치라면 B5만 자기 파일에 맞게 바꾸면 됩니다. 결과가 숫자처럼 보이면 셀 서식을 날짜로 바꿔 yyyy-mm-dd 형태로 표시하면 됩니다.

K5에는 거래처코드를 정리합니다. C열에 코드가 있으면 그 값을 사용하고, 비어 있으면 A열 주문번호에서 11번째 글자부터 4글자를 가져옵니다. 예시 주문번호가 SO-202607-C003-P101-009라면 C003이 추출됩니다.

=IF(C5<>"",C5,MID(A5,11,4))

주문번호 형식이 다르면 MID의 시작 위치와 글자 수를 조정해야 합니다. 예를 들어 코드가 항상 뒤에서 13번째부터 4글자라면 MID 대신 RIGHT와 LEFT를 섞어 별도 기준을 잡는 것이 좋습니다. 중요한 것은 SUMIFS 조건에 사용할 거래처코드가 한 열에 모이도록 만드는 것입니다.

L5에는 검산금액을 만듭니다. 원본 H열 매출액을 그대로 더할 수도 있지만, 수량과 단가 기준으로 정산하는 회사라면 ROUND를 써서 금액 기준을 맞춰두는 편이 좋습니다.

=ROUND(F5*G5,0)

만약 회사 기준이 원 단위 절사나 십 원 단위 반올림이라면 ROUND의 두 번째 인수를 바꿔야 합니다. 원 단위 반올림은 0, 십 원 단위 반올림은 -1을 사용합니다.

기준월과 거래처코드로 월매출을 계산하는 SUMIFS

이제 조건 셀을 정합니다. N2에는 기준월을 2026-07처럼 입력하고, O2에는 정산할 거래처코드 C003을 입력합니다. P2에는 해당 거래처의 기준월 매출액을 계산합니다.

=SUMIFS($L$5:$L$5000,$K$5:$K$5000,$O$2,$J$5:$J$5000,">="&DATE(LEFT($N$2,4),RIGHT($N$2,2),1),$J$5:$J$5000,"<="&EOMONTH(DATE(LEFT($N$2,4),RIGHT($N$2,2),1),0),$I$5:$I$5000,"매출")

이 수식은 L열 검산금액을 더하되, K열 정리코드가 O2와 같고, J열 정리일이 기준월의 1일부터 말일까지이며, I열 상태가 매출인 행만 합산합니다. 기준월이 바뀌어도 N2만 바꾸면 시작일과 말일이 자동으로 바뀝니다.

SUMIFS가 여전히 0이라면 바로 합계 수식을 의심하지 말고, 건수부터 확인하는 것이 빠릅니다. T2에 아래 COUNTIFS를 넣어 조건에 걸리는 행이 실제로 있는지 확인합니다.

=COUNTIFS($K$5:$K$5000,$O$2,$J$5:$J$5000,">="&DATE(LEFT($N$2,4),RIGHT($N$2,2),1),$J$5:$J$5000,"<="&EOMONTH(DATE(LEFT($N$2,4),RIGHT($N$2,2),1),0),$I$5:$I$5000,"매출")

T2가 0이면 합계 문제가 아니라 조건 문제입니다. 이때는 O2 거래처코드가 K열 값과 정확히 같은지, J열 정리일이 진짜 날짜인지, I열 상태에 매출 대신 정상매출 같은 다른 문구가 들어 있는지 확인해야 합니다.

거래처등급과 구간요율은 어떻게 가져올까?

리베이트 요율은 보통 거래처등급과 월매출 구간을 동시에 봅니다. 예시에서는 Y4:AA100에 거래처 마스터가 있고, U4:W9에 요율표가 있다고 가정하겠습니다.

Q2에는 거래처등급을 가져옵니다. Microsoft 365 또는 Excel 2021 이상에서 XLOOKUP을 사용할 수 있다면 아래처럼 쓰면 됩니다.

=XLOOKUP($O$2,$Y$5:$Y$100,$AA$5:$AA$100,"코드확인")

XLOOKUP을 사용할 수 없는 버전이라면 VLOOKUP으로도 충분합니다. 거래처코드가 Y열에 있고 등급이 AA열, 즉 선택 범위의 세 번째 열에 있으므로 아래 수식을 사용합니다.

=IFERROR(VLOOKUP($O$2,$Y$5:$AA$100,3,FALSE),"코드확인")

R2에는 월매출 P2와 등급 Q2를 기준으로 요율을 가져옵니다. 여기서는 INDEX와 MATCH를 조합합니다. MATCH($P$2,$U$5:$U$9,1)은 P2 월매출보다 작거나 같은 최저월매출 구간을 찾고, MATCH($Q$2,$V$4:$W$4,0)은 등급 열을 찾습니다.

=IFERROR(INDEX($V$5:$W$9,MATCH($P$2,$U$5:$U$9,1),MATCH($Q$2,$V$4:$W$4,0)),0)

이 수식에서 가장 중요한 조건은 U5:U9가 오름차순이어야 한다는 점입니다. 0, 5,000,000, 10,000,000, 30,000,000처럼 작은 금액부터 큰 금액 순서여야 MATCH의 1 옵션이 정상 작동합니다. 정렬이 뒤섞이면 요율이 낮거나 높게 적용될 수 있습니다.

마지막으로 S2에 리베이트 금액을 계산합니다.

=ROUND($P$2*$R$2,0)

요율 셀 R2는 2%라면 실제 값이 0.02여야 합니다. 화면에 2라고 입력해놓고 퍼센트 서식만 적용하지 않은 경우 정산액이 100배로 커집니다. 요율표는 반드시 0.02 또는 2% 형식으로 통일해두세요.

정산액이 맞는지 확인하는 실무 체크리스트

수식이 완성됐더라도 바로 제출하지 말고 아래 순서대로 확인하면 대부분의 오류를 잡을 수 있습니다. 특히 월말 정산 파일은 행이 많아 눈으로 훑는 것보다 검산용 셀을 만들어 확인하는 편이 빠릅니다.

추가로 원본 H열 매출액과 L열 검산금액이 크게 다른 행이 있는지도 확인해보면 좋습니다. M5에 차이금액을 만들고 아래 수식을 복사하면 단가 또는 수량 오류를 빨리 찾을 수 있습니다.

=H5-L5

M열에서 0이 아닌 값만 필터링하면 원본 매출액과 계산 매출액이 다른 행만 볼 수 있습니다. 단순 반올림 차이인지, 단가가 잘못 들어간 것인지, 수량이 음수 반품으로 들어간 것인지 확인해야 합니다.

날짜 텍스트가 매달 반복된다면 선택범위 VBA로 정리하기

파일을 받을 때마다 B열 날짜가 2026.07.03, 2026/07/03, 2026-07-03처럼 섞여 들어온다면 헬퍼열 수식으로 처리해도 되지만, 별도 정리본을 만들 때는 선택 범위만 날짜로 바꾸는 매크로가 편합니다. 실행 전에는 반드시 원본 파일을 복사해두세요. 매크로 실행 후에는 일반적인 Ctrl+Z 되돌리기가 되지 않을 수 있습니다.

아래 코드는 선택한 셀 범위에만 적용됩니다. 예를 들어 B5:B5000을 선택한 뒤 실행하면 해당 범위의 날짜 텍스트를 실제 날짜로 바꾸고 표시 형식을 yyyy-mm-dd로 맞춥니다.

Sub 선택범위_날짜텍스트_실제날짜로()
    Dim c As Range
    Dim s As String

    For Each c In Selection
        If Len(c.Value) > 0 Then
            If IsDate(c.Value) Then
                c.Value = CDate(c.Value)
            Else
                s = Replace(Replace(c.Value, ".", "-"), "/", "-")
                If IsDate(s) Then
                    c.Value = CDate(s)
                End If
            End If
            c.NumberFormat = "yyyy-mm-dd"
        End If
    Next c
End Sub

적용 범위는 선택한 셀뿐입니다. 전표일이 아닌 거래처코드나 주문번호 열을 선택한 상태에서 실행하면 원치 않는 변환이 생길 수 있으니, 반드시 B열 전표일 범위만 선택하고 실행하세요. 되돌려야 할 때를 대비해 실행 전 B열을 다른 빈 열에 복사해두는 것이 가장 안전합니다.

적용 전에 확인할 것

이 방식의 핵심은 원본을 바로 믿지 않고 정리일, 정리코드, 검산금액을 만든 뒤 그 열을 기준으로 SUMIFS를 돌리는 것입니다. 수식이 조금 길어져도 어느 지점에서 문제가 생겼는지 눈으로 확인할 수 있어 정산 업무에서는 오히려 안정적입니다.

마지막으로 세 가지만 꼭 확인하세요. N2 기준월은 2026-07처럼 월까지 입력되어 있는지, O2 거래처코드는 K열 정리코드와 같은 형식인지, U열 요율 구간은 오름차순인지입니다. 이 세 가지가 맞으면 월매출 0 오류, 구간요율 오류, 리베이트 반올림 차이의 대부분은 잡을 수 있습니다.