SUMIFS 0, VLOOKUP #N/A 같이 날 때: 월마감 수수료 정산표 안전하게 맞추는 법

월마감 수수료 정산표를 만들다 보면 SUMIFS 합계는 0으로 나오고, VLOOKUP은 #N/A가 뜨는 상황이 같이 터질 때가 있습니다. 원장은 분명히 있는데 거래처별 월매출이 안 잡히고, 수수료율표도 맞게 만든 것 같은데 조회가 안 되는 경우입니다.

이 문제는 함수가 어려워서라기보다, 실무 원장에 섞여 들어온 날짜 형식, 상품코드 자르기, 조회키 중복, 구간코드 기준이 한 번에 엉키면서 생깁니다. 이번 글에서는 월마감 수수료 정산 업무를 예시로, 사람들이 많이 쓰는 SUMIFS, COUNTIFS, VLOOKUP, XLOOKUP, INDEX, MATCH, IF, IFERROR, LEFT, TEXT, DATE, EOMONTH, ROUND 조합으로 안전하게 처리하는 흐름을 정리해 보겠습니다.

실무에서 자주 터지는 사례: 거래처별 월매출은 있는데 SUMIFS가 0으로 나옴

예시는 판매원장 시트와 수수료율표 시트를 나눠서 설명하겠습니다. 판매원장 시트에는 2행부터 5000행까지 데이터가 있고, 각 열은 아래처럼 구성되어 있다고 가정합니다.

수수료율표 시트는 2행부터 200행까지 사용한다고 보겠습니다. 월, 거래처, 상품군, 구간코드에 따라 수수료율이 달라지는 구조입니다.

여기서 구간코드는 거래처와 상품군의 월매출 합계에 따라 정한다고 하겠습니다. 월매출이 500만 원 미만이면 C01, 500만 원 이상 1000만 원 미만이면 C02, 1000만 원 이상이면 C03입니다.

왜 문제가 되는지: 날짜와 조회키가 눈으로는 같아도 엑셀은 다르게 봅니다

가장 흔한 실수는 주문일 A열에 있는 2026.06.03을 날짜처럼 보고 바로 SUMIFS 조건에 넣는 것입니다. 하지만 외부 시스템에서 내려받은 파일은 날짜가 실제 날짜가 아니라 문자로 들어오는 경우가 많습니다. 이 상태에서 기준월 셀 N2에 2026-06-01을 넣고 SUMIFS를 걸면, 눈으로는 같은 6월 데이터인데 합계가 0으로 나올 수 있습니다.

두 번째 실수는 VLOOKUP 조회값을 거래처명만으로 만드는 것입니다. 이번 업무에서는 거래처만 같다고 같은 수수료율이 아닙니다. 정산월, 거래처명, 상품군, 구간코드가 모두 같아야 정확한 수수료율을 가져올 수 있습니다.

그래서 바로 최종 수식을 만들기보다, 판매원장에 보조열을 추가해서 중간 계산값을 눈으로 확인하는 방식이 안전합니다. 실무에서는 이 보조열이 나중에 오류 원인을 찾는 증거가 됩니다.

안전한 처리법: 판매원장에 월, 상품군, 월매출, 구간코드부터 만들기

판매원장 시트에서 G열부터 L열까지 보조열과 결과열을 추가하겠습니다. G열은 정산월, H열은 상품군, I열은 거래처·상품군별 월매출, J열은 구간코드, K열은 수수료율, L열은 수수료액입니다.

먼저 F2에는 매출액을 계산합니다. 이미 매출액이 원본에 있다면 이 열은 건너뛰어도 됩니다.

=ROUND(D2*E2,0)

G2에는 주문일을 정산월로 바꾸는 수식을 넣습니다. A열이 실제 날짜이면 YEAR, MONTH로 처리되고, 문자 날짜이면 LEFT, MID로 연도와 월을 잘라 DATE로 다시 조립합니다.

=IFERROR(DATE(YEAR(A2),MONTH(A2),1),DATE(LEFT(A2,4),MID(A2,6,2),1))

H2에는 상품코드 앞 3자리로 상품군을 만듭니다. 예를 들어 ABC-1001-BL이면 ABC가 상품군입니다.

=LEFT(C2,3)

I2에는 거래처명, 상품군, 정산월이 모두 같은 매출액을 합산합니다. 원본 주문일 A열을 직접 조건으로 쓰지 않고, 정리된 G열 정산월을 조건으로 쓰는 것이 포인트입니다.

=SUMIFS($F$2:$F$5000,$B$2:$B$5000,B2,$H$2:$H$5000,H2,$G$2:$G$5000,G2)

J2에서는 월매출 합계에 따라 구간코드를 붙입니다. 기준 금액은 회사 정책에 맞게 바꾸면 됩니다.

=IF(I2<5000000,"C01",IF(I2<10000000,"C02","C03"))

VLOOKUP #N/A를 막는 조회키 만들기

수수료율표 시트로 이동해서 F열에 조회키를 만들겠습니다. F2에 아래 수식을 넣고 200행까지 복사합니다. 정산월은 TEXT 함수로 yyyymm 형태로 맞춰야 판매원장 쪽 키와 안정적으로 비교됩니다.

=TEXT(A2,"yyyymm")&"|"&B2&"|"&C2&"|"&D2

그리고 G열에는 조회용 수수료율을 한 번 더 복사해 둡니다. G2에 아래처럼 넣고 아래로 복사하면 됩니다.

=E2

이제 판매원장 시트 K2에서 수수료율을 가져옵니다. XLOOKUP을 사용할 수 있다면 아래 수식이 가장 읽기 쉽습니다.

=IFERROR(XLOOKUP(TEXT(G2,"yyyymm")&"|"&B2&"|"&H2&"|"&J2,수수료율표!$F$2:$F$200,수수료율표!$E$2:$E$200),0)

Excel 버전에 따라 XLOOKUP이 없다면 VLOOKUP으로 처리해도 됩니다. 이때 조회키가 표 범위의 가장 왼쪽 열에 있어야 하므로, 수수료율표의 F:G 범위를 사용합니다.

=IFERROR(VLOOKUP(TEXT(G2,"yyyymm")&"|"&B2&"|"&H2&"|"&J2,수수료율표!$F$2:$G$200,2,FALSE),0)

INDEX와 MATCH 조합을 선호한다면 아래처럼 써도 됩니다. 조회 범위와 반환 범위를 따로 지정하므로, 중간에 열이 추가되어도 비교적 안전합니다.

=IFERROR(INDEX(수수료율표!$E$2:$E$200,MATCH(TEXT(G2,"yyyymm")&"|"&B2&"|"&H2&"|"&J2,수수료율표!$F$2:$F$200,0)),0)

L2에는 최종 수수료액을 계산합니다. 원 단위 반올림이 필요하면 ROUND를 사용합니다.

=ROUND(F2*K2,0)

흔한 실수: 수식은 맞는데 결과가 계속 틀리는 지점

첫째, 수수료율표에 같은 조회키가 두 번 들어가면 VLOOKUP은 위에 있는 값만 가져옵니다. 그래서 수수료율표 H2에 중복 확인용 수식을 넣어두는 것이 좋습니다.

=COUNTIFS($F$2:$F$200,F2)

H열 값이 2 이상이면 같은 월, 거래처, 상품군, 구간코드가 중복된 것입니다. 이 상태에서는 어떤 수수료율이 맞는지 엑셀이 판단할 수 없으니, 수수료율표를 먼저 정리해야 합니다.

둘째, 상품코드 앞에 보이지 않는 공백이 있으면 LEFT(C2,3)이 엉뚱한 값을 가져올 수 있습니다. H열 상품군이 ABC, DEF처럼 정상적으로 보이는지 필터로 한 번 확인해 보세요. 상품군 목록에 빈칸이나 한 글자짜리 값이 섞여 있으면 원본 코드 정리가 먼저입니다.

셋째, 기준월이 날짜가 아니라 문자로 입력되어 있으면 검증용 SUMIFS가 또 0으로 나올 수 있습니다. 정산월 G열은 반드시 2026-06-01 같은 실제 날짜로 맞추고, 표시 형식만 yyyy-mm으로 바꾸는 방식이 안전합니다.

검증 방법: 조건 셀을 따로 두고 합계와 건수를 같이 확인

정산 결과가 맞는지 보려면 판매원장 오른쪽에 검증 영역을 만들어 두면 편합니다. 예를 들어 N2에는 기준월, O2에는 거래처명, P2에는 상품군을 입력합니다.

Q2에는 조건별 월매출 합계를 확인하는 수식을 넣습니다.

=SUMIFS($F$2:$F$5000,$G$2:$G$5000,$N$2,$B$2:$B$5000,$O$2,$H$2:$H$5000,$P$2)

R2에는 해당 조건의 원장 건수를 확인합니다. 합계만 보면 단가가 큰 한 건 때문에 이상 여부를 놓칠 수 있으니, 건수도 같이 보는 습관이 좋습니다.

=COUNTIFS($G$2:$G$5000,$N$2,$B$2:$B$5000,$O$2,$H$2:$H$5000,$P$2)

수수료액까지 검증하려면 S2에 아래 수식을 넣습니다.

=SUMIFS($L$2:$L$5000,$G$2:$G$5000,$N$2,$B$2:$B$5000,$O$2,$H$2:$H$5000,$P$2)

반복 월마감이면 VBA로 수식 채우기 자동화

매달 판매원장 행 수가 달라지고, G:L열 수식을 계속 복사해야 한다면 VBA로 자동화하는 편이 실무적으로 편합니다. 아래 코드는 판매원장 시트의 A열 마지막 행을 기준으로 F:L열 수식을 자동 입력합니다.

실행 전에는 파일을 다른 이름으로 저장해 두고, 시트 이름이 정확히 판매원장, 수수료율표인지 확인하세요. 되돌리고 싶을 때는 실행 직후 Ctrl+Z가 안 될 수 있으므로, 백업 파일을 열어 복구하는 방식이 안전합니다.

Sub 수수료정산_수식채우기()
    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = Worksheets("판매원장")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow < 2 Then
        MsgBox "판매원장에 데이터가 없습니다."
        Exit Sub
    End If

    ws.Range("F2:F" & lastRow).Formula = "=ROUND(D2*E2,0)"
    ws.Range("G2:G" & lastRow).Formula = "=IFERROR(DATE(YEAR(A2),MONTH(A2),1),DATE(LEFT(A2,4),MID(A2,6,2),1))"
    ws.Range("H2:H" & lastRow).Formula = "=LEFT(C2,3)"
    ws.Range("I2:I" & lastRow).Formula = "=SUMIFS($F$2:$F$" & lastRow & ",$B$2:$B$" & lastRow & ",B2,$H$2:$H$" & lastRow & ",H2,$G$2:$G$" & lastRow & ",G2)"
    ws.Range("J2:J" & lastRow).Formula = "=IF(I2<5000000,""C01"",IF(I2<10000000,""C02"",""C03""))"
    ws.Range("K2:K" & lastRow).Formula = "=IFERROR(VLOOKUP(TEXT(G2,""yyyymm"")&""|""&B2&""|""&H2&""|""&J2,수수료율표!$F$2:$G$200,2,FALSE),0)"
    ws.Range("L2:L" & lastRow).Formula = "=ROUND(F2*K2,0)"

    MsgBox "수수료 정산 수식 입력이 완료되었습니다."
End Sub

이 코드는 수식을 값으로 바꾸지 않습니다. 결과를 확인한 뒤 제출용 파일만 값으로 붙여넣기 하면 원본 계산 구조를 보존할 수 있습니다. 특히 수수료율표가 나중에 수정될 가능성이 있다면, 계산용 파일에는 수식을 남겨두는 편이 좋습니다.

점검 포인트: 월마감 전에 이 세 가지만 보면 사고가 줄어듭니다

첫째, 정산월 G열이 모두 같은 형식의 실제 날짜인지 확인합니다. 둘째, 수수료율표 조회키 중복 COUNTIFS 결과가 1인지 확인합니다. 셋째, 검증 영역에서 SUMIFS 합계와 COUNTIFS 건수가 원장 필터 결과와 일치하는지 봅니다.

SUMIFS가 0으로 나오거나 VLOOKUP이 #N/A를 띄울 때는 수식을 더 복잡하게 만들기보다, 날짜를 통일하고 조회키를 분해해서 눈으로 확인하는 것이 빠릅니다. 월마감 정산표는 한 번 맞춰두면 다음 달부터는 행만 늘어나는 업무라서, 보조열 구조와 검증 셀을 같이 남겨두는 방식이 가장 안전합니다.