프로젝트별 비용 배부 합계가 안 맞을 때: 품의번호 기준월 추출해서 SUMIFS 검산하는 법

월마감 때 프로젝트별 비용 배부 합계가 안 맞는 경우가 꽤 많습니다. 원장에는 분명히 전표가 있는데 SUMIFS 결과가 0으로 나오거나, 거래처명이 VLOOKUP 안됨 상태로 비어 있고, 부가세 포함 합계는 1~2원씩 계속 틀어지는 식입니다.

특히 ERP에서 내려받은 원장에 품의번호나 전표번호 안에 기준월이 들어 있는 파일은 더 조심해야 합니다. 전표일만 보고 집계하면 결산 조정분이 다른 월로 들어가고, 전표번호에서 월을 잘라 쓰면 텍스트 처리 실수 때문에 또 합계가 틀어질 수 있습니다.

이번 예시는 실무에서 자주 보는 프로젝트별 비용 배부 검산표입니다. 어려운 기능으로 한 번에 끝내기보다, 많이 쓰는 LEFT, MID, DATE, EOMONTH, SUMIFS, COUNTIFS, XLOOKUP, VLOOKUP, INDEX, MATCH, IFERROR, ROUND 조합으로 안전하게 맞춰보겠습니다.

실무에서 자주 터지는 상황: 원장 합계는 있는데 프로젝트 집계표가 0으로 나옴

예를 들어 원장 시트의 데이터가 2행부터 5000행까지 있다고 해보겠습니다. A열은 전표번호, B열은 전표일, C열은 부서코드, D열은 거래처코드, E열은 계정, F열은 공급가액, G열은 부가세, H열은 ERP 합계, I열은 프로젝트코드입니다.

보고서 시트는 이렇게 잡겠습니다. 보고서!B2에는 기준월을 2026-06으로 입력하고, 보고서!B3에는 부서코드 영업1, 보고서!B4에는 프로젝트코드 P-100을 입력합니다. 최종 합계는 보고서!B8, 건수는 보고서!B9, 중복 의심 건수는 보고서!B10에 표시해보겠습니다.

왜 문제가 되는지: 전표일, 기준월, 반올림, 거래처코드가 한꺼번에 흔들림

이런 파일에서 가장 흔한 실수는 전표일 B열을 그대로 월 조건으로 쓰는 것입니다. 위 예시의 2행은 전표일이 2026-07-01이지만, 전표번호 PRJ-2606-SALES-0007 안에는 2606이 들어 있습니다. 회사 기준으로는 2026년 6월 비용인데, 전표일 기준으로 집계하면 7월로 빠져버립니다.

두 번째는 공급가액과 부가세에 소수점이 숨어 있는 경우입니다. 화면에는 1,250,000처럼 보이지만 실제 값이 1,250,000.4이면 전체 합계에서 1원, 2원 차이가 납니다. 월마감 검산에서는 이런 작은 차이가 여러 행에 쌓여서 꽤 골치 아픕니다.

세 번째는 거래처코드 기준표 누락입니다. 원장 D열의 V999가 기준표에 없으면 거래처명이 빈칸이 되거나 #N/A가 납니다. 이 상태로 피벗이나 보고서를 만들면 담당자가 보기에는 “합계가 안 맞는다”가 아니라 “어느 거래처 비용인지 모르겠다”로 보입니다.

원장에 검산용 보조열 만들기: 기준월, 거래처명, 반올림 합계

원장 시트의 J열부터 N열까지 검산용 보조열을 만들겠습니다. 원본 열을 건드리지 않고 오른쪽에 붙이는 방식이라 나중에 삭제하기도 쉽습니다.

먼저 원장!J2에 기준월 수식을 입력하고 5000행까지 복사합니다. 전표번호가 항상 PRJ-2606-SALES-0007처럼 5번째 자리부터 연도 2자리, 7번째 자리부터 월 2자리로 들어간다는 전제입니다.

=IFERROR(DATE(2000+MID($A2,5,2),MID($A2,7,2),1),"")

이 수식은 A2에서 26과 06을 잘라 2026-06-01이라는 실제 날짜로 바꿉니다. 나중에 SUMIFS에서 월 조건을 걸려면 텍스트 "2026-06"보다 실제 날짜가 훨씬 안전합니다.

다음은 원장!K2에 거래처명을 붙입니다. 기준표 시트는 기준표!A2:C200에 있고, A열은 거래처코드, B열은 거래처명, C열은 과세구분이라고 가정합니다.

=IFERROR(XLOOKUP($D2,기준표!$A$2:$A$200,기준표!$B$2:$B$200),IFERROR(VLOOKUP($D2,기준표!$A$2:$C$200,2,FALSE),"미등록"))

XLOOKUP을 먼저 쓰고, 혹시 수식 구조를 VLOOKUP 방식으로 관리하던 파일에서도 확인할 수 있게 한 번 더 감쌌습니다. 결과가 미등록으로 나오면 원장 오류라기보다 기준표에 코드가 빠졌을 가능성이 큽니다.

만약 사용 중인 엑셀에서 XLOOKUP이 지원되지 않는다면 아래처럼 INDEX와 MATCH 조합으로 바꿔도 됩니다.

=IFERROR(INDEX(기준표!$B$2:$B$200,MATCH($D2,기준표!$A$2:$A$200,0)),"미등록")

원장!L2에는 반올림 기준의 검산합계를 만듭니다. 공급가액 F열과 부가세 G열에 소수점이 숨어 있을 수 있으니 각각 ROUND 처리 후 더합니다.

=ROUND($F2,0)+ROUND($G2,0)

원장!M2에는 ERP 합계 H열과 검산합계 L열이 같은지 표시합니다.

=IF(ROUND($H2,0)=$L2,"정상","금액확인")

원장!N2에는 중복 의심 여부를 표시합니다. 전표번호, 계정, 공급가액이 모두 같은 행이 2건 이상이면 일단 확인 대상으로 잡습니다.

=IF(COUNTIFS($A:$A,$A2,$E:$E,$E2,$F:$F,$F2)>1,"중복의심","")

보고서에서 SUMIFS로 프로젝트별 월마감 금액 뽑기

이제 보고서 시트에서 기준월의 시작일과 종료일을 만들어두겠습니다. 보고서!B2에는 사용자가 보기 편하게 2026-06이라고 입력합니다. 보고서!C2에는 시작일, 보고서!D2에는 종료일을 계산합니다.

보고서!C2
=DATE(LEFT($B$2,4),RIGHT($B$2,2),1)

보고서!D2
=EOMONTH($C$2,0)

여기서 중요한 점은 SUMIFS 조건에 보고서!B2의 텍스트 2026-06을 직접 넣지 않는 것입니다. 원장!J:J는 실제 날짜이고, 보고서!B2는 텍스트일 수 있으므로 서로 형식이 다르면 합계가 0으로 나올 수 있습니다.

보고서!B8에는 기준월, 부서, 프로젝트 조건을 모두 만족하는 검산합계를 구합니다.

=SUMIFS(원장!$L:$L,원장!$J:$J,">="&$C$2,원장!$J:$J,"<="&$D$2,원장!$C:$C,$B$3,원장!$I:$I,$B$4)

보고서!B9에는 같은 조건의 전표 건수를 구합니다. 합계만 맞고 건수가 다르면 중복이나 누락이 숨어 있을 수 있으니 건수도 같이 보는 편이 좋습니다.

=COUNTIFS(원장!$J:$J,">="&$C$2,원장!$J:$J,"<="&$D$2,원장!$C:$C,$B$3,원장!$I:$I,$B$4)

보고서!B10에는 조건에 해당하면서 중복의심 표시가 있는 행의 개수를 셉니다.

=COUNTIFS(원장!$J:$J,">="&$C$2,원장!$J:$J,"<="&$D$2,원장!$C:$C,$B$3,원장!$I:$I,$B$4,원장!$N:$N,"중복의심")

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

첫째, 기준월 위치가 바뀐 전표번호입니다. PRJ-2606-SALES-0007 형식이면 MID(A2,5,2), MID(A2,7,2)가 맞습니다. 그런데 회사마다 P2606-0007처럼 형식이 다르면 MID 시작 위치를 반드시 바꿔야 합니다.

둘째, 부서코드 뒤 공백입니다. 원장 C열에는 영업1처럼 보이지만 실제로는 영업1 뒤에 공백이 붙어 있을 수 있습니다. 이 경우 보고서!B3의 영업1과 같아 보이지만 SUMIFS는 다르게 봅니다. 원장 옆에 임시로 =LEN(C2)를 넣어 글자 수가 예상보다 긴지 확인해보면 빠르게 잡힙니다.

셋째, 숫자처럼 보이는 텍스트 금액입니다. F열, G열, H열이 왼쪽 정렬되어 있거나 셀 왼쪽 위에 초록색 표시가 있으면 금액이 텍스트일 수 있습니다. 이때 ROUND가 정상 계산되지 않거나 SUMIFS 합계가 예상과 다를 수 있으니, 원본을 숫자 형식으로 정리한 뒤 검산하는 것이 안전합니다.

넷째, 기준표 범위 부족입니다. 기준표가 A2:C200까지만 잡혀 있는데 실제 거래처가 300행까지 있으면 뒤쪽 코드는 전부 미등록으로 나옵니다. 수식에서 기준표 범위를 넉넉하게 잡거나, 기준표 행이 늘어날 때 범위를 같이 수정해야 합니다.

점검 포인트: 월마감 전에 이 순서로 보면 빠릅니다

보고서 합계가 이상할 때는 SUMIFS 수식부터 뜯어고치기보다 원장 보조열을 먼저 확인하는 편이 빠릅니다. 특히 J열 기준월이 비어 있는 행, K열 미등록, M열 금액확인, N열 중복의심만 필터로 걸어봐도 원인의 대부분이 드러납니다.

• 원장!J:J 기준월이 실제 날짜로 들어갔는지 확인
• 보고서!C2, D2가 날짜인지 확인
• 원장!K:K에 미등록 거래처가 있는지 확인
• 원장!M:M에 금액확인이 있는지 확인
• 원장!N:N에 중복의심이 있는지 확인

여기서 하나라도 걸리면 보고서 합계가 틀리는 것은 자연스러운 결과입니다. 반대로 이 네 가지가 깨끗한데도 합계가 다르면, 부서코드나 프로젝트코드 조건 셀의 공백, 오타, 코드 체계 변경을 의심하면 됩니다.

반복 파일이면 VBA로 검산열 자동 생성하기

매달 ERP 원장을 새로 내려받아 같은 보조열을 붙인다면 VBA로 처리하는 편이 낫습니다. 아래 코드는 현재 활성 시트를 기준으로 J:N열에 검산용 제목과 수식을 넣고, 실행 전 원장 시트를 복사해 백업 시트를 하나 만들어둡니다.

적용 전 주의할 점은 세 가지입니다. 첫째, 원본 데이터가 A:I열 구조와 같아야 합니다. 둘째, 거래처 기준표 시트 이름이 반드시 기준표여야 합니다. 셋째, 매크로 실행 후에는 일반적인 실행 취소가 어려우므로 코드에 포함된 백업 시트를 확인한 뒤 작업하세요.

Sub 원장_기준월_검산열_만들기()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim backupName As String

    Set ws = ActiveSheet

    If MsgBox("현재 시트에 J:N 검산열을 만들까요?", vbYesNo + vbQuestion) <> vbYes Then Exit Sub

    Application.ScreenUpdating = False

    backupName = ws.Name & "_백업_" & Format(Now, "mmdd_hhnnss")
    ws.Copy After:=ws
    ActiveSheet.Name = backupName
    ws.Activate

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

    If lastRow < 2 Then
        MsgBox "데이터가 없습니다. A열 전표번호를 확인하세요."
        Application.ScreenUpdating = True
        Exit Sub
    End If

    ws.Range("J1").Value = "기준월"
    ws.Range("K1").Value = "거래처명"
    ws.Range("L1").Value = "검산합계"
    ws.Range("M1").Value = "합계확인"
    ws.Range("N1").Value = "중복확인"

    ws.Range("J2:J" & lastRow).Formula = "=IFERROR(DATE(2000+MID($A2,5,2),MID($A2,7,2),1),"""")"
    ws.Range("K2:K" & lastRow).Formula = "=IFERROR(XLOOKUP($D2,기준표!$A$2:$A$200,기준표!$B$2:$B$200),IFERROR(VLOOKUP($D2,기준표!$A$2:$C$200,2,FALSE),""미등록""))"
    ws.Range("L2:L" & lastRow).Formula = "=ROUND($F2,0)+ROUND($G2,0)"
    ws.Range("M2:M" & lastRow).Formula = "=IF(ROUND($H2,0)=$L2,""정상"",""금액확인"")"
    ws.Range("N2:N" & lastRow).Formula = "=IF(COUNTIFS($A:$A,$A2,$E:$E,$E2,$F:$F,$F2)>1,""중복의심"",""" )"

    ws.Range("J:J").NumberFormat = "yyyy-mm"
    ws.Columns("J:N").AutoFit

    Application.ScreenUpdating = True

    MsgBox "검산열 생성 완료! 되돌리려면 백업 시트 [" & backupName & "]를 사용하세요."
End Sub

되돌리는 방법은 간단합니다. 매크로 실행 직후 만들어진 백업 시트를 원본으로 사용하거나, 현재 시트의 J:N열만 삭제하면 됩니다. 다만 이미 수식을 값으로 붙여넣었거나 다른 작업을 이어서 했다면 백업 시트에서 다시 시작하는 것이 가장 안전합니다.

실무 체크: 합계가 아니라 원인을 남겨야 다음 달이 편합니다

프로젝트별 비용 배부 검산은 단순히 합계 숫자 하나를 맞추는 작업이 아닙니다. 기준월 추출, 거래처명 확인, 반올림 기준, 중복 여부를 같이 남겨야 다음 달에 같은 문제가 반복되지 않습니다.

보고서에는 최종 합계만 보이게 하더라도, 원장 오른쪽에는 J:N 검산열을 남겨두는 것을 추천합니다. 월마감 회의에서 “왜 차이 났나요?”라는 질문이 나왔을 때, 필터 몇 번으로 기준월 오류인지, 미등록 거래처인지, 중복 전표인지 바로 설명할 수 있습니다.