프로모션 정산 단가가 틀릴 때: SUMIFS로 상품코드·행사월·수량구간까지 한 번에 검산하기

엑셀에서 프로모션 정산표를 만들다 보면 VLOOKUP 안됨, SUMIFS 0 나옴, 조건별 합계는 맞는데 적용 단가가 틀림 같은 문제가 한꺼번에 터질 때가 있습니다. 특히 행사 단가가 상품별로 하나만 있는 게 아니라, 행사월과 판매수량 구간에 따라 달라지는 파일이면 단순 VLOOKUP으로는 거의 반드시 어긋납니다.

오늘 예시는 온라인몰 월말 프로모션 정산 상황입니다. 판매원장에는 주문별 판매수량이 있고, 행사단가표에는 상품코드, 행사월, 최소수량, 최대수량, 정산단가가 따로 있습니다. 정산표에서는 거래처와 기준월을 선택하면 상품별 판매수량, 적용단가, 정산금액까지 자동으로 계산되게 만드는 흐름으로 정리해 보겠습니다.

실무에서 자주 터지는 증상: 같은 상품인데 단가가 엉뚱하게 들어감

먼저 파일 구조를 정확히 잡고 가겠습니다. 판매원장 시트는 A1:H5000 범위를 사용한다고 가정합니다. 원본은 보통 ERP나 쇼핑몰 관리자에서 내려받은 자료라 상품코드 모양이 제각각인 경우가 많습니다.

행사단가 시트는 A1:F200 범위입니다. 여기서 중요한 점은 같은 상품코드라도 판매수량 구간에 따라 단가가 다르다는 것입니다.

정산표 시트에서는 B2에 기준월, B3에 거래처명을 입력합니다. 예를 들어 B2에는 2026-06-01, B3에는 네이처몰을 입력합니다. A6:A50에는 정산할 상품코드를 0007, 0012처럼 넣고, B열부터 결과를 계산합니다.

왜 VLOOKUP만 쓰면 위험한가: 찾을 조건이 1개가 아니라 4개입니다

이 상황에서 VLOOKUP으로 상품코드만 찾아오면 첫 번째로 만나는 0007 단가가 들어갑니다. 그런데 실제로는 행사월이 2026년 6월인지, 상품코드가 0007인지, 판매수량이 최소수량 이상인지, 판매수량이 최대수량 이하인지까지 모두 맞아야 합니다.

즉 찾는 조건이 상품코드 하나가 아니라 행사월, 상품코드, 최소수량, 최대수량까지 총 4개입니다. 그래서 단가표가 조금만 늘어나도 VLOOKUP의 근사값 옵션이나 정렬 상태에 기대는 방식은 월마감 파일에서 사고가 나기 쉽습니다.

먼저 상품코드부터 안전하게 맞추기: LEFT, MID, TEXT, IFERROR 조합

판매원장 C열의 상품코드 원본이 PRD0007-BK처럼 들어온다면, 정산표의 상품코드 0007과 바로 비교할 수 없습니다. 판매원장 H2에 아래 수식을 넣고 H5000까지 복사합니다.

=IF(LEFT(C2,3)="PRD",MID(C2,4,4),IFERROR(TEXT(C2*1,"0000"),C2))

이 수식은 C2의 앞 3글자가 PRD이면 4번째 글자부터 4자리만 잘라 0007을 만듭니다. 만약 원본이 숫자 7처럼 들어왔다면 TEXT(C2*1,"0000")로 0007 형태를 맞춥니다. 코드 앞자리 0이 사라진 파일이라면 이 한 줄이 정산 오류를 크게 줄여 줍니다.

주문번호 뒤 4자리로 중복 주문을 빠르게 눈검토하고 싶다면 I2에 아래처럼 보조값을 만들 수도 있습니다. 필수는 아니지만, 같은 주문번호가 여러 번 내려온 의심 건을 볼 때 유용합니다.

=RIGHT(B2,4)

정산표 판매수량 계산: 시간값 때문에 빠지는 주문 막기

정산표 C6에는 A6의 상품코드, B2의 기준월, B3의 거래처 조건으로 판매수량을 합산합니다. 주문일에 시간이 섞여 있을 수 있으므로 종료 조건은 < 다음 달 1일 방식으로 잡는 게 안전합니다.

=SUMIFS(판매원장!$E$2:$E$5000,판매원장!$H$2:$H$5000,$A6,판매원장!$A$2:$A$5000,">="&$B$2,판매원장!$A$2:$A$5000,"<"&EOMONTH($B$2,0)+1,판매원장!$D$2:$D$5000,$B$3)

여기서 자기 파일에 맞게 바꿀 부분은 세 곳입니다. 판매수량 열이 E열이 아니라면 첫 번째 범위를 바꾸고, 표준상품코드 열이 H열이 아니라면 두 번째 조건 범위를 바꾸면 됩니다. 거래처 열이 D열이 아닌 파일도 많으니 이 부분도 꼭 확인하세요.

단가표 중복·누락부터 잡기: COUNTIFS로 매칭건수 확인

단가를 바로 가져오기 전에 G6에 매칭건수를 먼저 계산합니다. 실무에서는 단가표에 같은 구간이 두 번 들어가거나, 구간이 비어 있어 단가가 0으로 나오는 일이 생각보다 자주 있습니다.

=COUNTIFS(행사단가!$A$2:$A$200,$B$2,행사단가!$B$2:$B$200,$A6,행사단가!$C$2:$C$200,"<="&$C6,행사단가!$D$2:$D$200,">="&$C6)

결과가 1이면 정상입니다. 0이면 해당 월, 해당 상품, 해당 수량에 맞는 단가 구간이 없는 것입니다. 2 이상이면 같은 조건에 걸리는 단가표 행이 여러 개라는 뜻이므로, 이 상태에서 단가를 가져오면 정산금액이 틀어질 수 있습니다.

적용단가 계산: SUMIFS를 단가 찾기 용도로 쓰는 실무 방식

단가표가 정확히 한 행만 매칭된다는 전제라면 SUMIFS로 단가를 가져올 수 있습니다. 보통 SUMIFS는 합계를 구할 때만 쓴다고 생각하지만, 조건에 맞는 행이 1개라면 값 조회 용도로도 매우 안정적입니다.

정산표 D6에 아래 수식을 입력하고 아래로 복사합니다.

=IFERROR(IF($G6=1,SUMIFS(행사단가!$E$2:$E$200,행사단가!$A$2:$A$200,$B$2,행사단가!$B$2:$B$200,$A6,행사단가!$C$2:$C$200,"<="&$C6,행사단가!$D$2:$D$200,">="&$C6),0),0)

이 수식은 G6의 매칭건수가 1일 때만 단가를 가져오고, 그렇지 않으면 0을 표시합니다. 단가표 오류를 숨기고 싶지 않다면 0 대신 "단가표 확인"이라고 표시해도 됩니다. 다만 E열 정산금액 계산에서 문자 때문에 오류가 날 수 있으니, 실무에서는 G열 검산 메시지를 별도로 두는 편이 관리하기 좋습니다.

정산표 E6에는 정산금액을 계산합니다. 원 단위 반올림이 필요하다면 ROUND를 함께 씁니다.

=IF($G6=1,ROUND($C6*$D6,0),"단가표 확인")

상품명은 XLOOKUP 또는 VLOOKUP으로 붙이기

상품명은 단가 계산과 분리해서 생각하는 것이 좋습니다. 상품마스터 시트 A열에는 상품코드, B열에는 상품명을 둔다고 가정합니다. 정산표 B6에는 아래 수식을 넣습니다.

=IFERROR(XLOOKUP($A6,상품마스터!$A$2:$A$100,상품마스터!$B$2:$B$100),"코드확인")

XLOOKUP을 쓰지 않는 환경이라면 아래 VLOOKUP 방식으로 바꿔도 됩니다.

=IFERROR(VLOOKUP($A6,상품마스터!$A$2:$B$100,2,0),"코드확인")

여기서 중요한 것은 상품명 조회 오류와 단가표 오류를 섞지 않는 것입니다. 상품명은 정상인데 단가가 0일 수도 있고, 반대로 단가는 맞는데 상품마스터에 이름이 빠져 있을 수도 있습니다.

INDEX, MATCH로 단가를 가져오고 싶다면

SUMIFS 방식이 직관적이지만, 단가를 ‘조회’한다는 느낌으로 관리하고 싶다면 INDEX와 MATCH 조합도 사용할 수 있습니다. 정산표 D6에 아래 수식을 넣으면 조건에 맞는 정산단가를 반환합니다.

=IFERROR(INDEX(행사단가!$E$2:$E$200,MATCH(1,INDEX((행사단가!$A$2:$A$200=$B$2)*(행사단가!$B$2:$B$200=$A6)*(행사단가!$C$2:$C$200<=$C6)*(행사단가!$D$2:$D$200>=$C6),0),0)),0)

다만 이 방식도 단가표에 중복 구간이 있으면 첫 번째로 잡히는 값만 가져옵니다. 그래서 앞에서 만든 G열 COUNTIFS 검산은 그대로 유지하는 편이 안전합니다.

흔한 실수: 수식은 맞는데 결과가 계속 이상할 때

• B2 기준월이 텍스트인 경우: 2026-06처럼 보이지만 실제 날짜가 아니면 SUMIFS 날짜 조건이 빗나갑니다. B2에는 2026-06-01을 입력하고 표시 형식만 yyyy-mm으로 바꾸세요.
• 행사단가 A열의 행사월이 월말 날짜인 경우: 정산표 B2는 2026-06-01인데 단가표는 2026-06-30이면 정확히 일치하지 않습니다. 단가표 행사월은 월초 날짜로 통일하는 것이 좋습니다.
• 상품코드 앞자리 0이 사라진 경우: 0007과 7은 다르게 비교됩니다. TEXT 함수로 자리수를 맞춘 보조열을 만든 뒤 비교하세요.
• 수량구간이 겹치는 경우: 1~50, 50~100처럼 경계값이 겹치면 수량 50에서 매칭건수가 2가 됩니다. 1~49, 50~100처럼 끊어야 합니다.
• 주문일에 시간이 있는 경우: <=EOMONTH(B2,0) 조건은 6월 30일 오후 주문을 놓칠 수 있습니다. 반드시 <EOMONTH(B2,0)+1 방식으로 잡으세요.

반복 정산 파일이라면 VBA로 보조열 만들기 자동화

매월 판매원장을 새로 붙여넣고 H열 표준상품코드, I열 정산월, J열 주문번호 뒤 4자리를 반복해서 만드는 파일이라면 간단한 매크로가 잘 맞습니다. 실행 전에는 반드시 파일을 다른 이름으로 저장해 두세요. 매크로 실행 후에는 일반적인 Ctrl+Z 되돌리기가 되지 않으므로, 잘못 실행했을 때는 저장하지 않고 닫거나 백업 파일로 돌아가는 방식이 안전합니다.

Sub PrepareSalesRaw()
    Dim ws As Worksheet
    Dim lastRow As Long

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

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

    ws.Range("H1").Value = "표준상품코드"
    ws.Range("I1").Value = "정산월"
    ws.Range("J1").Value = "주문번호뒤4자리"

    ws.Range("H2:H" & lastRow).Formula = "=IF(LEFT(C2,3)=""PRD"",MID(C2,4,4),IFERROR(TEXT(C2*1,""0000""),C2))"
    ws.Range("I2:I" & lastRow).Formula = "=DATE(YEAR(A2),MONTH(A2),1)"
    ws.Range("J2:J" & lastRow).Formula = "=RIGHT(B2,4)"

    ws.Range("H2:J" & lastRow).Value = ws.Range("H2:J" & lastRow).Value
    ws.Columns("H:J").AutoFit

    MsgBox "판매원장 보조열 정리가 끝났습니다."
End Sub

이 코드는 판매원장이라는 이름의 시트에서만 작동합니다. A열 주문일 기준으로 마지막 행을 찾고, H:J열에 보조열을 만든 뒤 수식을 값으로 고정합니다. 이미 H:J열에 다른 중요한 데이터가 있다면 덮어쓰게 되니, 실행 전에 열 위치를 꼭 확인하세요.

점검 포인트: 정산금액을 믿기 전에 G열부터 보세요

이 방식의 핵심은 단가를 가져오는 수식보다 매칭건수 검산을 먼저 두는 것입니다. G열이 전부 1인지 확인한 다음 E열 정산금액을 집계하면, 단가표 누락과 중복 구간을 훨씬 빨리 잡을 수 있습니다.

월마감 정산표에서 오류가 나는 파일은 대부분 수식 하나가 틀려서라기보다 날짜 형식, 상품코드 자리수, 수량구간 경계가 조금씩 어긋난 경우가 많습니다. 기준월 B2, 거래처 B3, 상품코드 A열, 단가표 구간 C:D열만 차례로 확인해도 정산표 신뢰도가 확 올라갑니다.