엑셀 프로젝트별 비용 SUMIFS 0 나옴? 전표일 텍스트·프로젝트코드 누락·계정코드 불일치 잡는 법

엑셀에서 프로젝트별 비용을 SUMIFS로 집계했는데 결과가 0으로 나오거나, 손익표 금액이 회계 전표 합계와 다르게 나오는 일이 꽤 많습니다. 특히 전표일이 텍스트 날짜로 들어오고, 프로젝트코드가 일부 행에만 입력되어 있으며, 계정코드가 숫자와 문자로 섞여 있으면 수식 자체는 맞아도 결과가 틀어집니다.

오늘 예제는 실무에서 자주 보는 프로젝트 손익 대사 파일 기준으로 설명하겠습니다. 어려운 기능으로 한 번에 끝내기보다, 전표 원본을 안전하게 정리한 뒤 SUMIFS, COUNTIFS, VLOOKUP, XLOOKUP, INDEX, MATCH, IF, IFERROR, LEFT, RIGHT, MID, TEXT, DATE, EOMONTH, ROUND를 엮어서 검산 가능한 구조로 만드는 방식입니다.

실무에서 자주 터지는 증상: 전표는 있는데 프로젝트별 비용 합계가 0

예를 들어 전표원본 시트에 아래처럼 데이터가 있다고 가정하겠습니다. 원본 데이터는 5행부터 1500행까지 있고, A열부터 H열까지 내려옵니다.

그리고 손익요약 영역은 같은 시트 또는 별도 시트에 아래처럼 둡니다. 여기서는 설명을 쉽게 하기 위해 전표원본 시트의 R4:W12 영역을 사용하겠습니다.

많이 하는 실수는 바로 원본 A열 전표일을 그대로 SUMIFS 조건에 넣는 것입니다. 화면에는 2026.07.03처럼 보이지만 실제 값이 텍스트이면, 기준월 S4의 날짜 값과 비교되지 않아 합계가 0으로 나옵니다.

왜 문제가 되는지: 날짜, 코드, 금액 중 하나만 달라도 SUMIFS는 조용히 0을 반환

SUMIFS는 친절하게 오류를 보여주기보다 조건에 맞는 행이 없다고 판단하고 0을 반환하는 경우가 많습니다. 그래서 사용자는 수식이 맞는지, 데이터가 틀린지 바로 구분하기 어렵습니다.

이번 파일에서 가장 위험한 지점은 세 가지입니다. 첫째, A열 전표일이 실제 날짜가 아니라 텍스트입니다. 둘째, D열 프로젝트코드가 비어 있는데 B열 전표번호 안에는 코드가 들어 있습니다. 셋째, E열 계정코드가 어떤 행은 숫자 5210이고 어떤 행은 문자 5210입니다.

이런 상태에서 바로 아래처럼 수식을 쓰면 겉보기에는 멀쩡하지만 결과가 빗나갈 수 있습니다.

=SUMIFS($F$5:$F$1500,$D$5:$D$1500,$R$4,$A$5:$A$1500,">="&$S$4,$A$5:$A$1500,"<="&EOMONTH($S$4,0),$E$5:$E$1500,5210)

이 수식이 틀렸다는 뜻은 아닙니다. 다만 원본 데이터가 깨끗하게 정리되어 있다는 전제가 있어야 합니다. 실무 파일은 그 전제가 자주 무너집니다.

안전한 처리법: 원본 옆에 정리용 보조열을 먼저 만든다

원본을 직접 고치는 것보다 I열부터 M열까지 정리용 보조열을 만드는 편이 안전합니다. 원본 A:H열은 그대로 두고, 수식으로 집계에 쓸 값을 따로 만들어야 나중에 검산과 수정이 쉽습니다.

먼저 I5셀에 아래 수식을 입력하고 I1500행까지 복사합니다. A열이 실제 날짜이면 그대로 숫자 날짜로 쓰고, 텍스트이면 DATE, LEFT, MID, RIGHT 조합으로 날짜를 다시 만듭니다.

=IFERROR(A5*1,IFERROR(DATE(LEFT(A5,4),MID(A5,6,2),RIGHT(A5,2)),"확인"))

이 수식에서 A5는 원본 전표일입니다. 사용 중인 파일에서 전표일이 B열이라면 A5 부분을 B5로 바꾸면 됩니다. 결과가 "확인"으로 나오면 날짜 형식이 2026.07.03 또는 2026-07-03과 다른 형태라는 뜻이므로 해당 행을 따로 봐야 합니다.

J5셀에는 월 확인용 수식을 넣습니다. 이 열은 꼭 집계에 쓰지 않더라도 필터로 월이 제대로 변환됐는지 확인할 때 유용합니다.

=IFERROR(TEXT(I5,"yyyy-mm"),"확인")

K5셀에는 프로젝트코드를 정리합니다. D열 프로젝트코드가 있으면 그 값을 쓰고, 비어 있으면 B열 전표번호 PJ-2607-A102-003에서 9번째 글자부터 4글자를 가져옵니다.

=IF(D5<>"",D5,MID(B5,9,4))

여기서 중요한 점은 전표번호 규칙입니다. 예제처럼 항상 PJ-2607-A102-003 구조라면 A102는 9번째 글자부터 4글자입니다. 만약 회사 전표번호가 PJ202607-A102-003처럼 다르다면 MID의 시작 위치를 반드시 조정해야 합니다.

L5셀에는 공급가액을 숫자로 정리합니다. ERP에서 내려받은 금액이 텍스트 숫자인 경우 SUMIFS가 정상 합산되지 않거나, 다른 수식에서 예상치 못한 결과가 나올 수 있습니다.

=IFERROR(ROUND(F5*1,0),0)

ROUND를 넣는 이유는 소수점이 숨어 있는 수입 비용, 배부 금액, 환산 금액을 정리하기 위해서입니다. 원 단위 집계라면 0, 소수 둘째 자리까지 관리한다면 2로 바꾸면 됩니다.

계정코드표로 외주비·광고비 분류하기: VLOOKUP과 INDEX MATCH 같이 준비

이제 계정코드 5210이 외주비인지, 5310이 광고비인지 구분해야 합니다. O5:P10 영역에 계정코드표를 만들어 두겠습니다.

M5셀에는 아래 수식을 입력합니다. E열 계정코드가 숫자든 문자든 최대한 찾아보고, 그래도 없으면 "계정확인"이라고 표시하게 했습니다.

=IFERROR(VLOOKUP(TEXT(E5,"0"),$O$5:$P$20,2,FALSE),IFERROR(VLOOKUP(E5&"",$O$5:$P$20,2,FALSE),"계정확인"))

만약 회사 계정코드가 05210처럼 앞자리 0을 포함하는 5자리 코드라면 TEXT(E5,"0") 대신 TEXT(E5,"00000")을 써야 합니다. 앞자리 0이 중요한 코드 체계에서는 원본을 열 때부터 텍스트로 유지하는 것이 가장 안전합니다.

프로젝트명도 같이 표시하고 싶다면 U5:V50에 프로젝트 마스터를 만들어 둡니다. U열은 프로젝트코드, V열은 프로젝트명입니다. 예를 들어 T4셀에 프로젝트명을 표시하려면 아래처럼 XLOOKUP을 쓰면 됩니다.

=IFERROR(XLOOKUP($R$4,$U$5:$U$50,$V$5:$V$50),"프로젝트 없음")

XLOOKUP을 사용할 수 없는 버전이라면 INDEX와 MATCH 조합으로 같은 결과를 만들 수 있습니다.

=IFERROR(INDEX($V$5:$V$50,MATCH($R$4,$U$5:$U$50,0)),"프로젝트 없음")

정리된 보조열 기준으로 SUMIFS 집계하기

이제 실제 집계 수식을 넣어 보겠습니다. R4에는 조회할 프로젝트코드 A102, S4에는 기준월 시작일 2026-07-01이 들어 있다고 가정합니다. S4는 반드시 실제 날짜여야 하며, 셀 서식만 yyyy-mm으로 바꿔 보이게 하는 것을 추천합니다.

R7셀에 외주비 합계를 구하려면 아래 수식을 입력합니다. 원본 F열이 아니라 정리된 L열 공급가액을 합산하고, 날짜 조건은 정리일자 I열로 판단합니다.

=SUMIFS($L$5:$L$1500,$K$5:$K$1500,$R$4,$I$5:$I$1500,">="&$S$4,$I$5:$I$1500,"<="&EOMONTH($S$4,0),$M$5:$M$1500,"외주비")

R8셀에 광고비를 구하려면 마지막 조건만 바꾸면 됩니다.

=SUMIFS($L$5:$L$1500,$K$5:$K$1500,$R$4,$I$5:$I$1500,">="&$S$4,$I$5:$I$1500,"<="&EOMONTH($S$4,0),$M$5:$M$1500,"광고비")

여기서 EOMONTH($S$4,0)은 기준월의 말일을 뜻합니다. S4가 2026-07-01이면 조건 범위는 2026-07-01부터 2026-07-31까지가 됩니다.

점검 포인트: 합계보다 먼저 건수와 오류 표시를 본다

SUMIFS 결과가 이상할 때는 금액부터 보지 말고 건수부터 확인하는 것이 빠릅니다. R10셀에 아래 COUNTIFS 수식을 넣으면 해당 프로젝트, 해당 월에 잡힌 전표가 몇 건인지 볼 수 있습니다.

=COUNTIFS($K$5:$K$1500,$R$4,$I$5:$I$1500,">="&$S$4,$I$5:$I$1500,"<="&EOMONTH($S$4,0))

R10이 0이면 외주비 수식이 문제가 아니라 프로젝트코드 또는 날짜 조건에서 이미 걸러진 것입니다. 이때는 K열 정리프로젝트와 I열 정리일자를 필터로 확인해야 합니다.

계정코드표에서 못 찾은 항목은 별도로 세어야 합니다. R11셀에 아래 수식을 넣으면 M열에 "계정확인"으로 남은 행 수를 확인할 수 있습니다.

=COUNTIFS($M$5:$M$1500,"계정확인")

날짜 변환 실패 건도 확인합니다. R12셀에 아래 수식을 넣어 I열이 "확인"인 행이 있는지 봅니다.

=COUNTIFS($I$5:$I$1500,"확인")

흔한 실수: 수식은 맞는데 기준 셀이 틀린 경우

특히 기준월 셀 S4에 2026-07이라고 직접 입력해 놓고 날짜처럼 쓰는 경우가 많습니다. 이 값은 실제 날짜가 아닐 수 있으므로, 가능하면 S4에는 2026-07-01을 입력한 뒤 표시 형식만 yyyy-mm으로 바꾸세요.

여러 전표 시트에 보조열을 한 번에 넣는 VBA 자동화 팁

매월 전표_1월, 전표_2월, 전표_3월처럼 시트가 여러 개라면 I:M 보조열 수식을 매번 복사하는 것도 일이 됩니다. 이럴 때는 VBA로 보조열 제목과 수식을 한 번에 넣는 방식이 잘 맞습니다.

아래 코드는 시트 이름이 전표로 시작하는 모든 시트에 대해 4행에 보조열 제목을 넣고, A열 마지막 행까지 I:M열 수식을 채웁니다. 원본 구조가 A열 전표일, B열 전표번호, D열 프로젝트코드, E열 계정코드, F열 공급가액일 때만 그대로 사용하세요.

Sub 전표보조열_일괄입력()
    Dim ws As Worksheet
    Dim lastRow As Long

    For Each ws In ThisWorkbook.Worksheets
        If Left(ws.Name, 2) = "전표" Then
            lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
            If lastRow >= 5 Then
                ws.Range("I4").Value = "정리일자"
                ws.Range("J4").Value = "정리월"
                ws.Range("K4").Value = "정리프로젝트"
                ws.Range("L4").Value = "정리공급가액"
                ws.Range("M4").Value = "계정분류"

                ws.Range("I5:I" & lastRow).FormulaR1C1 = "=IFERROR(RC[-8]*1,IFERROR(DATE(LEFT(RC[-8],4),MID(RC[-8],6,2),RIGHT(RC[-8],2)),""확인""))"
                ws.Range("J5:J" & lastRow).FormulaR1C1 = "=IFERROR(TEXT(RC[-1],""yyyy-mm""),""확인"")"
                ws.Range("K5:K" & lastRow).FormulaR1C1 = "=IF(RC[-7]<>"""",RC[-7],MID(RC[-9],9,4))"
                ws.Range("L5:L" & lastRow).FormulaR1C1 = "=IFERROR(ROUND(RC[-6]*1,0),0)"
                ws.Range("M5:M" & lastRow).FormulaR1C1 = "=IFERROR(VLOOKUP(TEXT(RC[-8],""0""),R5C15:R20C16,2,FALSE),IFERROR(VLOOKUP(RC[-8]&"""",R5C15:R20C16,2,FALSE),""계정확인""))"
            End If
        End If
    Next ws

    MsgBox "전표 시트 보조열 입력이 완료되었습니다."
End Sub

실행 전에는 반드시 파일을 다른 이름으로 저장해 두세요. 매크로 실행 후에는 일반 실행 취소가 되지 않습니다. 계정코드표 O5:P20이 각 전표 시트에 있어야 M열 VLOOKUP이 정상 작동한다는 점도 확인해야 합니다.

되돌리고 싶을 때는 저장하지 않고 파일을 닫는 방법이 가장 안전합니다. 이미 저장했다면 아래 매크로로 전표 시트의 I:M 보조열을 지울 수 있습니다.

Sub 전표보조열_삭제()
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        If Left(ws.Name, 2) = "전표" Then
            ws.Range("I:M").ClearContents
        End If
    Next ws

    MsgBox "전표 시트 보조열 내용이 삭제되었습니다."
End Sub

실무 체크: SUMIFS가 틀린 게 아니라 데이터 준비가 덜 된 경우가 많다

프로젝트별 비용 집계가 안 맞을 때는 SUMIFS 수식만 계속 고치기보다, 날짜·프로젝트코드·계정코드·금액을 보조열에서 먼저 표준화해 보세요. I열 정리일자, K열 정리프로젝트, M열 계정분류만 안정적으로 만들어도 대부분의 0 반환 문제와 누락 금액을 잡을 수 있습니다.

최종 확인은 간단합니다. R10의 전표 건수가 0이 아닌지, R11의 계정확인 건수가 없는지, R12의 날짜 확인 건수가 없는지 먼저 봅니다. 이 세 가지가 정리된 상태에서 SUMIFS 합계를 보면 손익표 대사 시간이 훨씬 짧아집니다.