월말 미수금 보고서 잔액이 안 맞을 때: SUMIFS로 청구·입금·상계 누계 검산하기

월말 미수금 보고서를 만들다 보면 가장 자주 만나는 증상이 있습니다. 거래처별 잔액을 SUMIFS로 집계했는데 어떤 거래처는 0으로 나오고, 어떤 거래처는 회계 원장 잔액과 몇 만 원씩 차이가 납니다.

특히 청구, 입금, 상계가 한 원장에 섞여 있는 파일은 단순히 금액 열 하나만 더하면 안 됩니다. 청구는 미수금을 늘리고, 입금과 상계는 미수금을 줄이는 방향으로 계산해야 하며, 기준월의 시작일과 말일 조건도 정확히 잡아야 합니다.

이번 예시는 월말 정산에서 바로 쓰는 형태로 잡아보겠습니다. 낯선 기능으로 한 번에 끝내기보다, 실무에서 가장 많이 쓰는 SUMIFS, COUNTIFS, XLOOKUP, VLOOKUP, INDEX, MATCH, IF, IFERROR, LEFT, TEXT, DATE, EOMONTH, ROUND 조합으로 안전하게 맞추는 방식입니다.

실무 상황: 청구·입금·상계가 섞인 원장에서 거래처별 미수금 보고서 만들기

원본 시트 이름은 원장이고, 데이터는 5행부터 5000행까지 있다고 가정하겠습니다. A열은 전표일, B열은 거래처코드, C열은 전표구분, D열은 세금계산서번호, E열은 공급가액, F열은 부가세, G열은 입금액, H열은 메모입니다.

거래처명과 담당자는 거래처마스터 시트에서 가져옵니다. 거래처마스터 시트는 A열 거래처코드, B열 거래처명, C열 담당자이며 2행부터 300행까지 사용한다고 보겠습니다.

보고서 시트에서는 B2에 기준월을 2026-06처럼 입력합니다. B3에는 월초일, B4에는 월말일을 계산하고, A7:K열에 거래처별 미수금 결과를 표시합니다.

기준월을 직접 날짜로 바꾸지 않으면 SUMIFS가 0으로 나올 수 있습니다

보고서!B2에 2026-06을 입력해두고, SUMIFS 조건에 바로 쓰면 파일 상태에 따라 날짜 조건이 어긋날 수 있습니다. 그래서 월초와 월말을 별도 셀로 만들어두는 편이 안전합니다.

보고서!B3에는 기준월의 첫날을 계산합니다.

=DATE(LEFT($B$2,4),RIGHT($B$2,2),1)

보고서!B4에는 기준월의 마지막 날을 계산합니다.

=EOMONTH($B$3,0)

예를 들어 B2가 2026-06이면 B3은 2026-06-01, B4는 2026-06-30이 됩니다. 이후 모든 SUMIFS는 B3과 B4를 기준으로 잡아야 날짜 밀림이나 월 경계 오류가 줄어듭니다.

거래처명은 XLOOKUP으로 붙이고, 안 되는 버전은 VLOOKUP으로 대체합니다

보고서 시트의 A7부터 A200까지 거래처코드를 입력해둔다고 하겠습니다. B열에는 거래처명을 표시합니다. Microsoft 365 또는 Excel 2021 이상에서 XLOOKUP을 쓸 수 있다면 보고서!B7에 아래 수식을 넣습니다.

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

XLOOKUP을 사용할 수 없는 환경이라면 VLOOKUP으로 바꾸면 됩니다.

=IFERROR(VLOOKUP($A7,거래처마스터!$A$2:$C$300,2,FALSE),"거래처코드 확인")

담당자는 INDEX와 MATCH로 가져오겠습니다. 보고서!C7에 넣고 아래로 복사합니다.

=IFERROR(INDEX(거래처마스터!$C$2:$C$300,MATCH($A7,거래처마스터!$A$2:$A$300,0)),"담당자 확인")

이때 거래처명이 “거래처코드 확인”으로 나온다면 미수금 수식부터 의심하지 말고, A열 코드와 마스터 코드가 실제로 같은지 먼저 확인해야 합니다. 앞뒤 공백, 코드 자리수, 지점 코드 포함 여부 때문에 여기서부터 틀어지는 경우가 많습니다.

기초미수, 당월청구, 당월입금을 분리해야 잔액 차이를 찾기 쉽습니다

보고서 열 구조는 다음처럼 잡겠습니다. A열 거래처코드, B열 거래처명, C열 담당자, D열 기초미수, E열 당월청구, F열 당월입금상계, G열 계산미수, H열 원장누계미수, I열 차이, J열 전표건수, K열 상태입니다.

먼저 D7에는 기준월 이전까지의 기초미수를 계산합니다. 청구는 공급가액과 부가세를 더하고, 입금과 상계는 입금액에서 차감합니다.

=ROUND(SUMIFS(원장!$E$5:$E$5000,원장!$B$5:$B$5000,$A7,원장!$A$5:$A$5000,"<"&$B$3,원장!$C$5:$C$5000,"청구")+SUMIFS(원장!$F$5:$F$5000,원장!$B$5:$B$5000,$A7,원장!$A$5:$A$5000,"<"&$B$3,원장!$C$5:$C$5000,"청구")-SUMIFS(원장!$G$5:$G$5000,원장!$B$5:$B$5000,$A7,원장!$A$5:$A$5000,"<"&$B$3,원장!$C$5:$C$5000,"입금")-SUMIFS(원장!$G$5:$G$5000,원장!$B$5:$B$5000,$A7,원장!$A$5:$A$5000,"<"&$B$3,원장!$C$5:$C$5000,"상계"),0)

E7에는 당월 청구액을 계산합니다. 기준월 월초 이상, 월말 이하 조건을 동시에 걸어야 합니다.

=ROUND(SUMIFS(원장!$E$5:$E$5000,원장!$B$5:$B$5000,$A7,원장!$A$5:$A$5000,">="&$B$3,원장!$A$5:$A$5000,"<="&$B$4,원장!$C$5:$C$5000,"청구")+SUMIFS(원장!$F$5:$F$5000,원장!$B$5:$B$5000,$A7,원장!$A$5:$A$5000,">="&$B$3,원장!$A$5:$A$5000,"<="&$B$4,원장!$C$5:$C$5000,"청구"),0)

F7에는 당월 입금과 상계를 합산합니다. 회사에 따라 상계를 별도 금액 열로 관리하는 경우도 있는데, 이 예시에서는 G열 입금액에 양수로 입력되어 있다고 보겠습니다.

=ROUND(SUMIFS(원장!$G$5:$G$5000,원장!$B$5:$B$5000,$A7,원장!$A$5:$A$5000,">="&$B$3,원장!$A$5:$A$5000,"<="&$B$4,원장!$C$5:$C$5000,"입금")+SUMIFS(원장!$G$5:$G$5000,원장!$B$5:$B$5000,$A7,원장!$A$5:$A$5000,">="&$B$3,원장!$A$5:$A$5000,"<="&$B$4,원장!$C$5:$C$5000,"상계"),0)

G7에는 보고서 계산 기준 미수금을 표시합니다.

=ROUND(D7+E7-F7,0)

원장누계미수를 한 번 더 계산해두면 수식 오류를 빨리 잡습니다

실무에서는 G열 계산미수만 두면 어디서 틀렸는지 찾기 어렵습니다. 그래서 H열에 월말일까지 원장 전체를 누계로 다시 계산한 값을 둡니다. G열과 H열이 같아야 정상입니다.

=ROUND(SUMIFS(원장!$E$5:$E$5000,원장!$B$5:$B$5000,$A7,원장!$A$5:$A$5000,"<="&$B$4,원장!$C$5:$C$5000,"청구")+SUMIFS(원장!$F$5:$F$5000,원장!$B$5:$B$5000,$A7,원장!$A$5:$A$5000,"<="&$B$4,원장!$C$5:$C$5000,"청구")-SUMIFS(원장!$G$5:$G$5000,원장!$B$5:$B$5000,$A7,원장!$A$5:$A$5000,"<="&$B$4,원장!$C$5:$C$5000,"입금")-SUMIFS(원장!$G$5:$G$5000,원장!$B$5:$B$5000,$A7,원장!$A$5:$A$5000,"<="&$B$4,원장!$C$5:$C$5000,"상계"),0)

I7에는 차이를 표시합니다.

=ROUND(G7-H7,0)

J7에는 해당 거래처가 월말일까지 원장에 몇 건 있는지 COUNTIFS로 확인합니다.

=COUNTIFS(원장!$B$5:$B$5000,$A7,원장!$A$5:$A$5000,"<="&$B$4)

K7에는 상태 문구를 붙입니다. 차이가 있으면 먼저 검산 대상으로 표시하고, 미수금이 음수이면 과입금 가능성을 표시합니다.

=IF(I7<>0,"검산필요",IF(G7<0,"과입금 확인",IF(G7=0,"정산완료","미수있음")))

거래처코드에 지점번호가 붙어 있으면 LEFT 보조열로 기준을 맞춥니다

SUMIFS가 0으로 나오는 원인 중 하나는 코드 체계가 미묘하게 다른 경우입니다. 보고서에는 C001이라고 되어 있는데 원장에는 C001-01, C001-02처럼 지점번호가 붙어 있으면 완전일치 조건으로는 잡히지 않습니다.

이럴 때는 원장 시트 I열에 본사코드 보조열을 만듭니다. 원장!I5에 아래 수식을 넣고 아래로 복사합니다.

=LEFT(B5,4)

그 다음 보고서 수식의 조건 범위인 원장!$B$5:$B$5000을 원장!$I$5:$I$5000으로 바꾸면 됩니다. 반대로 뒤 4자리 지점코드가 필요하다면 RIGHT를 써서 별도 보조열을 만들 수 있습니다.

=RIGHT(B5,2)

세금계산서번호가 2606-A118처럼 들어와 있고, 발행월을 점검하고 싶다면 MID로 월 정보를 뽑아 검사용 열을 만들 수도 있습니다.

="20"&MID(D5,1,2)&"-"&MID(D5,3,2)

이 값이 전표일의 월과 다르면 날짜 입력 오류인지, 세금계산서번호 입력 오류인지 확인해야 합니다. 원장!J5에 아래처럼 TEXT를 이용한 전표월을 만들어 나란히 비교하면 검토가 훨씬 빠릅니다.

=TEXT(A5,"yyyy-mm")

잔액이 안 맞을 때는 이 순서로 확인하면 빠릅니다

첫째, 보고서!B3과 B4가 실제 날짜인지 확인합니다. 셀 표시만 날짜처럼 보이고 실제로는 텍스트인 경우 SUMIFS 날짜 조건이 제대로 먹지 않습니다.

둘째, 원장 C열 전표구분이 정확히 청구, 입금, 상계로 입력되어 있는지 봅니다. “입금 ”처럼 뒤에 공백이 있거나 “상계처리”처럼 다른 단어가 섞이면 SUMIFS 조건에서 빠집니다.

셋째, 입금액의 부호를 확인합니다. 이 예시 수식은 입금액과 상계액이 양수로 입력되어 있고, 수식에서 차감하는 구조입니다. 원장 자체에 입금액이 음수로 들어오는 회사라면 차감 수식을 더하기 방식으로 바꿔야 합니다.

넷째, 총계로 한 번 더 맞춥니다. 보고서 D열 전체 합계, E열 전체 합계, F열 전체 합계를 원장 필터 합계와 비교해보면 어느 구간에서 차이가 생겼는지 바로 좁혀집니다.

거래처가 많을 때는 수식 복사를 매크로로 처리하면 실수가 줄어듭니다

A7 아래로 거래처코드만 바뀌고 B:K열 수식 구조가 동일하다면, 수식을 손으로 끌어내리다가 범위가 틀어지는 사고가 자주 납니다. 이럴 때는 보고서 시트의 7행 수식을 기준으로 마지막 거래처코드 행까지 자동 복사하는 간단한 VBA가 잘 맞습니다.

실행 전에는 파일을 다른 이름으로 저장해두세요. 매크로 실행 후에는 일반 작업처럼 Ctrl+Z로 되돌리기 어려우므로, 백업 파일을 하나 만들어두는 편이 안전합니다. 아래 코드는 시트 이름이 정확히 보고서이고, A열에 거래처코드가 입력되어 있으며, B7:K7에 완성된 수식이 들어 있다는 전제에서 동작합니다.

Sub 미수금보고서_수식복사()
    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("보고서")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow < 7 Then
        MsgBox "A7 아래 거래처코드를 먼저 입력하세요."
        Exit Sub
    End If

    ws.Range("B7:K7").AutoFill Destination:=ws.Range("B7:K" & lastRow)
    ws.Range("B7:K" & lastRow).Calculate

    MsgBox "수식 복사가 끝났습니다. I열 차이를 확인하세요."
End Sub

되돌려야 한다면 저장하지 말고 파일을 닫은 뒤 백업 파일을 다시 여는 방식이 가장 깔끔합니다. 이미 저장했다면 7행의 원본 수식을 다시 점검한 뒤 매크로를 재실행하세요.

월말 미수금 보고서는 수식 하나로 끝내기보다, 기준월 날짜 만들기, 거래처코드 매칭, 기초·당월·누계 분리, 차이 검산 열까지 같이 설계해야 안정적입니다. 처음 만들 때는 수식이 길어 보여도, 한 번 틀을 잡아두면 다음 달에는 B2 기준월만 바꾸고 I열 차이만 확인하면 됩니다.