엑셀 월매출이 ERP와 다를 때: 귀속월 텍스트·취소전표·거래처코드까지 SUMIFS로 복구하기

월매출 집계표를 만들었는데 ERP 기준 7월 매출은 76,500,000원, 엑셀 집계는 72,300,000원으로 나오는 상황이 있었습니다. 처음에는 SUMIFS 수식이 틀린 줄 알았는데, 실제 원인은 귀속월 표기가 제각각이고 취소전표가 양수로 섞여 있는 문제였습니다.

특히 매출원장을 내려받으면 날짜처럼 보이는 값이 실제 날짜가 아닌 텍스트인 경우가 많습니다. 화면에는 2026-07처럼 보이지만 셀 안에는 202607, 2026.7, 2026-07-01이 섞여 있으면 조건별 합계가 조용히 빠집니다. 오류 메시지는 없는데 합계만 안 맞는, 실무에서 가장 피곤한 유형입니다.

증상은 단순했다: SUMIFS는 정상인데 7월 매출만 계속 작게 나왔다

예시 파일은 매출원장 시트와 거래처마스터 시트, 그리고 월매출검산 시트로 나누어져 있다고 가정하겠습니다. 매출원장 시트의 데이터는 3행부터 5000행까지 들어 있고, 각 열은 아래처럼 구성되어 있습니다.

월매출검산 시트에서는 L2에 기준월, M2에 거래처코드를 입력하고 O2에 해당 거래처의 월 공급가액을 표시하려고 했습니다. L2에는 반드시 실제 날짜 2026-07-01을 입력한 뒤 표시 형식만 yyyy-mm으로 바꾸는 방식이 안전합니다.

원장을 뜯어보니 귀속월이 날짜가 아니라 문자처럼 섞여 있었다

가장 먼저 확인할 부분은 매출원장 시트의 F열입니다. F열 귀속월이 모두 같은 형식이면 SUMIFS 날짜 조건이 깔끔하게 먹히지만, 아래처럼 섞여 있으면 일부 행이 조건에서 빠집니다.

이럴 때는 원본 F열을 억지로 고치기보다, 옆에 검산용 열을 추가하는 쪽이 안전합니다. 매출원장 시트의 I열에는 귀속월시작일, J열에는 계산용공급가액을 만들겠습니다.

I3 셀에는 아래 수식을 넣고 I5000까지 내려 복사합니다. 이 수식은 F열 값이 실제 날짜이면 그 달의 1일로 바꾸고, 202607 같은 숫자형 표기나 2026-07 같은 문자형 표기도 최대한 같은 날짜로 맞춥니다.

=IFERROR(IF(F3>30000,DATE(YEAR(F3),MONTH(F3),1),DATE(LEFT(TEXT(F3,"000000"),4),RIGHT(TEXT(F3,"000000"),2),1)),IFERROR(DATE(LEFT(F3,4),MID(F3,6,2),1),"확인"))

수식 결과가 날짜로 보이지 않고 45474 같은 숫자로 보이면 셀 서식만 날짜로 바꾸면 됩니다. 중요한 것은 화면 모양이 아니라, I열 값이 실제 날짜로 계산되는지입니다. 만약 I열에 확인이라고 나오면 해당 행의 귀속월 원본이 너무 지저분하다는 뜻이므로 F열 값을 직접 확인해야 합니다.

취소전표가 양수로 들어와 있어서 합계가 더 틀어졌다

두 번째 원인은 H열 전표상태였습니다. ERP에서는 취소전표를 차감해서 보여주는데, 엑셀 원장에는 공급가액 G열이 항상 양수로 내려오는 경우가 있습니다. 이 상태에서 G열을 그대로 SUMIFS로 더하면 취소 건까지 매출로 잡힙니다.

그래서 J3 셀에는 계산용공급가액을 따로 만들었습니다. H열이 취소이면 음수로 바꾸고, 정상 전표이면 그대로 가져옵니다.

=IF(H3="취소",-G3,G3)

부가세 포함 금액까지 같이 검산해야 한다면 K열에 계산용합계금액을 만들고 아래 수식을 사용할 수 있습니다. 공급가액에 10%를 곱한 뒤 원 단위로 반올림하는 방식입니다.

=ROUND(J3*1.1,0)

정산 자료에서 1원 차이가 반복될 때는 ROUND 위치도 중요합니다. 전표별로 반올림한 뒤 합산하는 방식인지, 월 합계에 1.1을 곱한 뒤 반올림하는 방식인지 ERP 기준과 맞춰야 합니다. 세금계산서 단위 검산이라면 보통 행별 계산값을 합산하는 방식이 더 안전했습니다.

거래처명은 조회용으로만 쓰고, 합계 조건은 거래처코드로 걸었다

월매출검산 시트의 M2에는 거래처코드를 입력합니다. 거래처명은 사람이 확인하기 좋도록 N2에 자동으로 불러오되, SUMIFS 조건은 거래처명 D열이 아니라 거래처코드 C열로 거는 편이 안전합니다. 거래처명에는 띄어쓰기, 지점명, 괄호 표기가 섞이는 경우가 많기 때문입니다.

거래처마스터 시트는 A2:C200 범위에 있다고 가정하겠습니다. A열은 거래처코드, B열은 거래처명, C열은 담당팀입니다. Microsoft 365나 Excel 2021 이상을 사용한다면 N2에 XLOOKUP을 사용할 수 있습니다.

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

XLOOKUP을 쓰기 어려운 버전이라면 VLOOKUP으로도 충분합니다. 거래처코드가 마스터 표의 첫 번째 열에 있기만 하면 아래 수식으로 같은 결과를 낼 수 있습니다.

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

마스터 표에서 조회 열 위치가 자주 바뀌는 파일이라면 INDEX와 MATCH 조합도 괜찮습니다. 열을 끼워 넣어도 조회 기준과 반환 범위를 따로 잡을 수 있어서 유지보수가 편합니다.

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

최종 SUMIFS는 날짜 범위를 시작일과 말일로 잡아야 빠지는 행이 없다

이제 월매출검산 시트 O2에 최종 공급가액 합계를 구합니다. 기준월은 L2, 거래처코드는 M2, 원장 범위는 매출원장 시트의 3행부터 5000행까지입니다.

=SUMIFS(매출원장!$J$3:$J$5000,매출원장!$I$3:$I$5000,">="&$L$2,매출원장!$I$3:$I$5000,"<="&EOMONTH($L$2,0),매출원장!$C$3:$C$5000,$M$2)

여기서 핵심은 조건을 2026-07이라는 글자로 비교하지 않는 것입니다. L2의 2026-07-01 이상, EOMONTH(L2,0) 이하라는 날짜 범위로 비교해야 7월 전체가 정확히 잡힙니다.

건수도 같이 확인하면 문제를 훨씬 빨리 좁힐 수 있습니다. P2에는 해당 월, 해당 거래처의 전체 전표 건수를 구합니다.

=COUNTIFS(매출원장!$I$3:$I$5000,">="&$L$2,매출원장!$I$3:$I$5000,"<="&EOMONTH($L$2,0),매출원장!$C$3:$C$5000,$M$2)

Q2에는 같은 조건에서 취소전표 건수만 따로 셉니다. 취소 건수가 0인데 ERP에는 취소가 있다면 H열 전표상태 표기가 다른지 먼저 봐야 합니다. 예를 들어 취소, 승인취소, 반제취소처럼 단어가 다르면 조건이 맞지 않습니다.

=COUNTIFS(매출원장!$I$3:$I$5000,">="&$L$2,매출원장!$I$3:$I$5000,"<="&EOMONTH($L$2,0),매출원장!$C$3:$C$5000,$M$2,매출원장!$H$3:$H$5000,"취소")

SUMIFS가 0으로 나오면 수식보다 조건 셀부터 봐야 한다

이 문제에서 흔한 실수는 O2 수식만 계속 고치는 것입니다. 하지만 SUMIFS가 0으로 나오거나 일부만 잡힐 때는 조건 셀과 비교 열의 자료형이 맞는지 먼저 봐야 합니다.

L2가 실제 날짜인지 확인하려면 빈 셀에 아래처럼 입력해 보세요. 결과가 숫자로 나오면 날짜 계산이 가능한 상태입니다. 오류가 나거나 그대로 글자처럼 붙으면 L2가 텍스트일 가능성이 큽니다.

=EOMONTH(L2,0)

또 하나는 거래처코드 앞뒤 공백입니다. M2에 C1024를 입력했는데 원장 C열에는 C1024 뒤에 공백이 붙어 있으면 같은 코드처럼 보여도 조건이 맞지 않습니다. 이 경우에는 C열 전체를 선택한 뒤 찾기 및 바꾸기에서 공백을 정리하거나, 원장 추출 단계에서 거래처코드를 코드값 그대로 내려받는 것이 좋습니다.

매달 같은 정리를 한다면 보조열 수식은 버튼으로 깔아도 된다

매출원장을 매달 새로 붙여넣고 I열, J열 수식을 다시 채우는 작업이 반복된다면 간단한 매크로로 줄일 수 있습니다. 아래 코드는 매출원장 시트의 마지막 행을 찾아 I열에는 귀속월시작일 수식, J열에는 계산용공급가액 수식을 넣습니다.

실행 전에는 파일을 복사해 두는 것을 권장합니다. 매크로 실행 후에는 일반적인 Ctrl+Z 되돌리기가 안정적으로 되지 않을 수 있으므로, 원본 백업을 남겨두는 편이 안전합니다. 적용 범위는 매출원장 시트의 A열 마지막 데이터 행 기준입니다.

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 < 3 Then
        MsgBox "3행부터 데이터가 있는지 확인하세요."
        Exit Sub
    End If

    ws.Range("I2").Value = "귀속월시작일"
    ws.Range("J2").Value = "계산용공급가액"

    ws.Range("I3:I" & lastRow).Formula = "=IFERROR(IF(F3>30000,DATE(YEAR(F3),MONTH(F3),1),DATE(LEFT(TEXT(F3,""000000""),4),RIGHT(TEXT(F3,""000000""),2),1)),IFERROR(DATE(LEFT(F3,4),MID(F3,6,2),1),""확인""))"
    ws.Range("J3:J" & lastRow).Formula = "=IF(H3=""취소"",-G3,G3)"

    ws.Range("I3:I" & lastRow).NumberFormat = "yyyy-mm-dd"
    ws.Range("G3:J" & lastRow).NumberFormat = "#,##0"

    MsgBox "검산 보조열 입력이 완료되었습니다. I열의 '확인' 행을 먼저 점검하세요."
End Sub

되돌리고 싶다면 저장하지 않고 파일을 닫거나, 백업 파일을 다시 여는 방식이 가장 확실합니다. 이미 저장했다면 I:J열의 보조열을 삭제하고 백업 원장에서 다시 시작하는 편이 안전합니다.

다음 달에도 같은 사고를 막는 체크포인트

월매출 검산에서 가장 먼저 볼 곳은 SUMIFS 수식이 아니라 원장 데이터의 기준 열입니다. 귀속월은 실제 날짜로 통일하고, 취소전표는 계산용 금액에서 음수 처리하며, 거래처명 대신 거래처코드로 조건을 걸면 대부분의 차이는 빠르게 좁혀집니다.

마지막으로 O2 합계만 보지 말고 P2 전표건수, Q2 취소건수를 같이 확인해 보세요. 금액 차이가 생겼을 때 건수가 맞는지부터 보면 날짜 문제인지, 취소전표 문제인지, 거래처코드 문제인지 훨씬 빨리 갈라낼 수 있습니다.

이번처럼 오류 메시지는 없는데 월매출만 안 맞는 파일은 보조열을 만들어 원인을 눈에 보이게 만드는 것이 제일 빠릅니다. 원장을 직접 고치기 전에 I열과 J열로 검산 기준을 세워두면, 다음 달 파일이 들어와도 같은 방식으로 바로 복구할 수 있습니다.