엑셀 SUMIFS 합계가 두 배로 나올 때: 수정 거래명세서 중복 합산을 최신본만 계산하는 법

월말 정산표를 만들다 보면 엑셀 SUMIFS 합계가 원장보다 크게 나오거나, 거래명세서 금액이 두 번 잡히는 증상이 은근히 자주 나옵니다. 특히 거래명세서를 수정 발행하는 회사라면 원본 파일에 최초 발행분, 1차 수정분, 2차 수정분이 모두 남아 있어서 단순 SUMIFS로는 합계가 맞기 어렵습니다.

이번 예제는 “정상 거래만 더했는데 왜 금액이 두 배지?”라는 상황을 기준으로 보겠습니다. 핵심은 어려운 기능을 쓰는 것이 아니라, COUNTIFS로 최신 수정본만 표시하고 SUMIFS는 그 표시값만 합산하게 만드는 방식입니다.

기존 방식과 개선 후 방식 비교: SUMIFS가 틀린 게 아니라 원본 기준이 애매한 경우

실무에서 흔한 기존 방식은 발행월, 거래처코드, 상품코드만 조건으로 걸고 바로 SUMIFS를 거는 것입니다. 그런데 같은 명세서번호가 수정차수만 다르게 여러 줄 남아 있으면, 엑셀은 그것을 전부 정상 데이터로 보고 더합니다.

이 방식의 장점은 원본 구조를 크게 바꾸지 않아도 된다는 점입니다. 보조열 몇 개만 추가하면 기존 SUMIFS 정산표를 그대로 살릴 수 있습니다.

예시 표 구조: 원본 시트 A4:M5000 기준으로 잡기

예제 파일에는 원본 시트가 있고, 데이터는 5행부터 5000행까지 들어온다고 가정하겠습니다. 4행은 제목 행입니다.

샘플 데이터로 보면 아래처럼 같은 명세서번호 S-260701이 수정차수 0과 1로 두 번 들어와 있습니다. 기존 방식으로는 두 줄이 모두 합산되어 금액이 커집니다.

Before: 조건은 맞는데 SUMIFS 합계가 과하게 나오는 수식

정산표에서 P2에는 정산월, P3에는 거래처코드, P4에는 상품코드를 입력한다고 하겠습니다. 예를 들어 P2는 2026-07-01, P3는 C001, P4는 P-100입니다.

기존에 많이 쓰는 수식은 아래 형태입니다. 발행일이 날짜로 잘 정리되어 있다는 전제라면 겉보기에는 문제가 없어 보입니다.

=SUMIFS($G$5:$G$5000,$A$5:$A$5000,>=$P$2,$A$5:$A$5000,"<="&EOMONTH($P$2,0),$D$5:$D$5000,$P$3,$E$5:$E$5000,$P$4,$I$5:$I$5000,"정상")

하지만 이 수식은 같은 명세서번호의 수정 전 금액과 수정 후 금액을 구분하지 못합니다. 위 샘플에서는 S-260701의 100,000원과 120,000원이 둘 다 합산됩니다. 실제로 필요한 금액은 최신 수정차수인 120,000원만 잡히는 것입니다.

또 하나의 문제는 A열 발행일 형식입니다. 어떤 행은 날짜 값이고, 어떤 행은 20260705 같은 숫자 또는 문자로 들어오면 월 조건이 흔들릴 수 있습니다. 그래서 먼저 날짜를 정리하는 보조열을 만드는 편이 안전합니다.

After 핵심 보조열: 정리발행일, 정산월, 최신여부, 정산금액

원본 시트에 J열부터 M열까지 보조열을 추가하겠습니다. J4에는 정리발행일, K4에는 정산월, L4에는 최신여부, M4에는 정산금액이라고 입력합니다.

먼저 J5에 아래 수식을 넣고 J5000까지 복사합니다. A열이 실제 날짜면 그대로 쓰고, 20260705처럼 8자리로 들어온 값은 DATE, LEFT, MID, RIGHT로 날짜를 다시 만듭니다.

=IF(ISNUMBER(A5),A5,IF(LEN(A5)=8,DATE(LEFT(A5,4),MID(A5,5,2),RIGHT(A5,2)),DATE(LEFT(A5,4),MID(A5,6,2),RIGHT(A5,2))))

다음은 K5에 정산월을 만듭니다. 일자는 버리고 해당 월의 1일로 통일합니다. 이렇게 해두면 SUMIFS에서 시작일과 종료일을 매번 비교하지 않아도 됩니다.

=DATE(LEFT(TEXT(J5,"yyyymm"),4),RIGHT(TEXT(J5,"yyyymm"),2),1)

이제 가장 중요한 L5 최신여부입니다. 같은 명세서번호 안에서 현재 행보다 더 큰 수정차수가 있으면 현재 행은 제외합니다. 더 큰 수정차수가 없고 상태가 정상인 행만 “최신”으로 표시합니다.

=IF(AND(I5="정상",COUNTIFS($B$5:$B$5000,B5,$C$5:$C$5000,">"&C5)=0),"최신","제외")

마지막으로 M5에는 실제 정산에 사용할 금액을 만듭니다. 최신 행이면 공급가와 부가세를 더하고, 제외 행이면 0으로 둡니다. 금액 계산에서 소수점이 섞이는 파일이라면 ROUND로 한 번 감싸는 습관이 좋습니다.

=IF(L5="최신",ROUND(G5+H5,0),0)

이제 같은 명세서번호가 여러 번 있어도 M열에는 최신 정상 행의 금액만 남습니다. 눈으로 중복을 지우지 않아도 되기 때문에 월말에 원본을 다시 받았을 때도 훨씬 안전합니다.

개선 후 SUMIFS: 최신본만 합산하는 정산표 수식

정산표 결과 셀을 P6이라고 하겠습니다. P2에는 정산월, P3에는 거래처코드, P4에는 상품코드가 들어 있습니다. 이제 합산 대상은 M열 정산금액이고, 조건에는 K열 정산월과 L열 최신여부를 추가합니다.

=SUMIFS($M$5:$M$5000,$K$5:$K$5000,$P$2,$D$5:$D$5000,$P$3,$E$5:$E$5000,$P$4,$L$5:$L$5000,"최신")

공급가만 따로 보고 싶다면 M열 대신 G열을 합산하되, 최신여부 조건은 그대로 유지하면 됩니다.

=SUMIFS($G$5:$G$5000,$K$5:$K$5000,$P$2,$D$5:$D$5000,$P$3,$E$5:$E$5000,$P$4,$L$5:$L$5000,"최신")

건수 검산도 같이 해두면 좋습니다. P7에 아래 COUNTIFS를 넣으면 해당 조건에 해당하는 최신 명세서가 몇 건인지 확인할 수 있습니다.

=COUNTIFS($K$5:$K$5000,$P$2,$D$5:$D$5000,$P$3,$E$5:$E$5000,$P$4,$L$5:$L$5000,"최신")

거래처명이 안 맞을 때: XLOOKUP, VLOOKUP, INDEX MATCH로 코드 검증

정산 금액이 맞아도 거래처코드가 잘못 들어오면 보고서에서 엉뚱한 거래처로 보일 수 있습니다. 거래처마스터 시트의 A열에는 거래처코드, B열에는 거래처명이 있고, 범위는 A2:B200이라고 하겠습니다.

N5에 거래처명을 가져오려면 XLOOKUP을 사용할 수 있습니다. 코드가 마스터에 없으면 “코드확인”이라고 표시되게 IFERROR를 붙입니다.

=IFERROR(XLOOKUP(D5,거래처마스터!$A$2:$A$200,거래처마스터!$B$2:$B$200),"코드확인")

XLOOKUP을 쓸 수 없는 환경이라면 VLOOKUP으로도 충분합니다.

=IFERROR(VLOOKUP(D5,거래처마스터!$A$2:$B$200,2,FALSE),"코드확인")

왼쪽 찾기나 열 순서 변경이 잦은 파일에서는 INDEX와 MATCH 조합도 안정적입니다.

=IFERROR(INDEX(거래처마스터!$B$2:$B$200,MATCH(D5,거래처마스터!$A$2:$A$200,0)),"코드확인")

정산 오류를 줄이려면 N열에 “코드확인”이 남아 있는 행이 있는지 먼저 필터링해 보세요. SUMIFS가 틀린 게 아니라 거래처코드 자체가 마스터와 맞지 않는 경우도 많습니다.

검증은 이렇게 하면 빠릅니다: 명세서번호 하나를 잡고 Before/After 비교

수식을 넣은 뒤에는 전체 금액만 보지 말고, 문제가 있었던 명세서번호 하나를 잡아 확인하는 것이 좋습니다. 예를 들어 B열에서 S-260701을 필터링합니다.

특히 수정차수가 문자로 들어온 파일은 조심해야 합니다. 0, 1, 2가 아니라 ‘1차’, ‘2차’처럼 입력되어 있으면 COUNTIFS의 “더 큰 값” 비교가 의도와 다르게 작동할 수 있습니다. 가능하면 C열 수정차수는 숫자로 통일해 주세요.

반복 파일이면 VBA로 보조열을 한 번에 만들기

매월 같은 양식의 거래명세서 원본을 받는다면 보조열 수식을 매번 복사하는 것도 번거롭습니다. 이럴 때는 아래 VBA로 J:M열 제목과 수식을 한 번에 넣을 수 있습니다.

적용 범위는 현재 활성화된 시트입니다. A열 발행일을 기준으로 마지막 행을 찾고, 5행부터 마지막 행까지 수식을 입력합니다. 실행 전에는 반드시 원본 시트를 복사해 두세요. 매크로 실행 후에는 일반적인 Ctrl+Z 되돌리기가 되지 않습니다.

Sub 정산_최신수정본_보조열생성()
    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow < 5 Then
        MsgBox "5행 이후 데이터가 없습니다."
        Exit Sub
    End If

    ws.Range("J4:M4").Value = Array("정리발행일", "정산월", "최신여부", "정산금액")

    ws.Range("J5:J" & lastRow).FormulaR1C1 = _
        "=IF(ISNUMBER(RC[-9]),RC[-9],IF(LEN(RC[-9])=8,DATE(LEFT(RC[-9],4),MID(RC[-9],5,2),RIGHT(RC[-9],2)),DATE(LEFT(RC[-9],4),MID(RC[-9],6,2),RIGHT(RC[-9],2))))"

    ws.Range("K5:K" & lastRow).FormulaR1C1 = _
        "=DATE(LEFT(TEXT(RC[-1],""yyyymm""),4),RIGHT(TEXT(RC[-1],""yyyymm""),2),1)"

    ws.Range("L5:L" & lastRow).FormulaR1C1 = _
        "=IF(AND(RC[-3]=""정상"",COUNTIFS(R5C2:R" & lastRow & "C2,RC[-10],R5C3:R" & lastRow & "C3,"">""&RC[-9])=0),""최신"",""제외"")"

    ws.Range("M5:M" & lastRow).FormulaR1C1 = _
        "=IF(RC[-1]=""최신"",ROUND(RC[-6]+RC[-5],0),0)"

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

    MsgBox "최신 수정본 기준 보조열 생성이 완료되었습니다."
End Sub

되돌리는 방법은 간단합니다. 실행 전에 복사해 둔 원본 시트로 돌아가거나, J:M열에 추가된 보조열을 삭제하면 됩니다. 다만 매크로가 기존 J:M열 내용을 덮어쓰므로, 그 열에 다른 계산식이 있는 파일에서는 열 위치를 바꿔서 테스트해야 합니다.

정산표가 안 맞을 때 마지막으로 보는 체크 포인트

SUMIFS 합계가 계속 이상하다면 수식만 의심하지 말고 원본 기준을 먼저 확인해야 합니다. 같은 명세서번호가 여러 번 남는 업무에서는 “어떤 행을 최종본으로 볼 것인가”가 정해져야 합계가 맞습니다.

이번 방식은 J:M 보조열로 원본을 정리한 뒤, SUMIFS에는 이미 정리된 값만 넘기는 구조입니다. 그래서 나중에 거래처 조건을 추가하거나 상품코드를 바꿔도 원리는 그대로 유지됩니다.

월말 정산에서 금액이 과하게 나온다면 먼저 B열 명세서번호와 C열 수정차수를 확인해 보세요. 중복 수정본이 원인이라면, 최신여부 보조열 하나만으로도 보고서 신뢰도가 꽤 올라갑니다.