엑셀 생산실적 SUMIFS 0 나옴? 작업지시번호 라인코드·날짜 텍스트 때문에 월별 집계 안 맞을 때

생산실적 파일에서 SUMIFS가 0으로 나오거나, 월별 생산수량이 ERP 집계와 다르게 나오는 경우가 꽤 많습니다. 특히 작업일이 20260703 같은 텍스트 날짜로 내려오고, 라인 정보는 작업지시번호 안에 숨어 있는 파일이라면 단순히 필터 후 합계만으로는 검산이 잘 안 됩니다.

이번 예제는 제조, 물류가공, 포장 실적표에서 자주 보는 구조로 잡아보겠습니다. 핵심은 원본을 억지로 눈으로 고치지 않고, 보조열에서 날짜·라인코드·순생산수량·중량을 정리한 뒤 SUMIFS, COUNTIFS, VLOOKUP, XLOOKUP, INDEX MATCH로 월별 집계를 검산하는 흐름입니다.

생산실적 월별 집계가 안 맞는 실제 파일 구조

시트는 두 개를 사용한다고 가정하겠습니다. 첫 번째 시트 이름은 실적원본, 두 번째 시트 이름은 품목마스터입니다. 집계 결과는 별도 시트 집계에서 확인합니다.

실적원본 시트의 A열부터 F열까지는 ERP에서 내려받은 원본이고, G열부터 L열까지는 우리가 수식으로 정리할 보조열입니다. 데이터는 2행부터 2000행까지 들어온다고 보겠습니다.

예시 데이터는 아래처럼 들어와 있다고 보겠습니다.

품목마스터 시트는 A열부터 D열까지 사용합니다. A열은 품목코드, B열은 품목명, C열은 단위중량kg, D열은 사용여부입니다. 예를 들어 FG-01001의 단위중량이 0.35kg이라면 생산중량은 순생산수량에 0.35를 곱해 계산합니다.

집계 시트에서는 조건 셀을 이렇게 둡니다. B2에는 기준월, C2에는 라인코드, D2에는 품목코드를 입력하고, E2부터 결과를 표시합니다.

SUMIFS가 0으로 나오는 원인은 대부분 조건값의 모양이 다르기 때문입니다

이 상황에서 가장 흔한 실수는 사람 눈에는 같은 2026년 7월인데 엑셀 입장에서는 서로 다른 값이라는 점입니다. 실적원본 A열의 20260703은 날짜가 아니라 8자리 숫자 또는 텍스트일 수 있습니다. 반면 집계 시트 B2는 실제 날짜 2026-07-01일 수 있습니다.

또 하나는 라인코드입니다. 작업지시번호 WO-2607-A03-0007 안에 A03이 들어 있으니 사용자는 A03 라인의 실적이라고 생각하지만, SUMIFS는 B열 전체 문자열에서 자동으로 A03만 골라내지 않습니다. 라인코드를 따로 뽑아낸 열이 필요합니다.

마지막으로 취소 건과 불량수량입니다. 생산수량 D열만 합산하면 취소된 500개가 들어가거나, 불량수량을 빼지 않아 ERP의 순생산수량과 차이가 납니다. 이럴 때는 원본을 직접 수정하기보다 보조열을 만들어 계산 기준을 명확히 해두는 편이 안전합니다.

실적원본 보조열에서 날짜, 라인코드, 순수량을 먼저 정리합니다

먼저 실적원본 G열에는 정상작업일을 만듭니다. A2가 20260703처럼 8자리로 내려온 경우에는 DATE, LEFT, MID, RIGHT로 실제 날짜를 만들고, 이미 날짜인 값은 그대로 쓰는 방식입니다.

=IFERROR(IF(LEN(TEXT(A2,"0"))=8,DATE(LEFT(TEXT(A2,"0"),4),MID(TEXT(A2,"0"),5,2),RIGHT(TEXT(A2,"0"),2)),A2),"")

이 수식은 실적원본!G2에 입력한 뒤 G2000까지 복사합니다. 결과가 2026-07-03처럼 날짜로 보이면 정상입니다. 만약 46206 같은 숫자로 보이면 값은 날짜로 변환된 것이고, 셀 서식만 날짜 형식으로 바꾸면 됩니다.

실적원본 H열에는 작업지시번호에서 라인코드를 추출합니다. 예시 형식이 항상 WO-2607-A03-0007이라면 A03은 9번째 글자부터 3글자입니다.

=IFERROR(MID(B2,9,3),"")

작업지시번호 자릿수가 회사마다 조금 다를 수 있으니, 자기 파일에서 라인코드가 몇 번째부터 시작하는지 먼저 확인하세요. 예를 들어 PRD-202607-A03-0007처럼 앞부분이 길다면 MID의 시작 위치를 12 또는 13으로 바꿔야 합니다.

실적원본 I열에는 실적월을 만듭니다. 월별 집계를 할 때는 매번 2026-07-01부터 2026-07-31까지 조건을 걸 수도 있지만, 보조열에 월 첫날을 만들어두면 SUMIFS 조건이 훨씬 깔끔해집니다.

=IF(G2="","",EOMONTH(G2,-1)+1)

이 결과는 2026-07-01처럼 표시되도록 셀 서식을 날짜로 지정합니다. 집계 시트 B2도 같은 방식으로 해당 월의 첫날이어야 합니다. B2에 그냥 2026-07이라는 글자를 입력하면 SUMIFS가 0으로 나올 수 있습니다.

실적원본 J열에는 순생산수량을 계산합니다. 완료가 아닌 취소 건은 0으로 만들고, 완료 건은 생산수량에서 불량수량을 뺍니다. D열과 E열이 텍스트 숫자로 들어오는 경우까지 고려해 IFERROR를 같이 씁니다.

=IF(F2="취소",0,IFERROR(D2*1,0)-IFERROR(E2*1,0))

실적원본 K열에는 품목명을 가져옵니다. 많이 쓰는 방식인 VLOOKUP으로 먼저 작성하면 아래와 같습니다.

=IFERROR(VLOOKUP(C2,품목마스터!$A$2:$D$500,2,FALSE),"마스터없음")

Microsoft 365 또는 Excel 2021 이후 버전에서 XLOOKUP을 사용할 수 있다면 아래처럼 써도 됩니다. 다만 회사 PC마다 버전이 다를 수 있으니, 공동으로 쓰는 파일이면 VLOOKUP 방식이 더 무난할 때도 있습니다.

=IFERROR(XLOOKUP(C2,품목마스터!$A$2:$A$500,품목마스터!$B$2:$B$500),"마스터없음")

XLOOKUP이 없는 버전에서는 INDEX와 MATCH 조합으로도 같은 결과를 만들 수 있습니다. VLOOKUP처럼 찾는 열이 반드시 맨 왼쪽에 있어야 한다는 부담이 적어서, 마스터 열 배치가 자주 바뀌는 파일에 좋습니다.

=IFERROR(INDEX(품목마스터!$B$2:$B$500,MATCH(C2,품목마스터!$A$2:$A$500,0)),"마스터없음")

실적원본 L열에는 생산중량을 계산합니다. 순생산수량 J열에 품목마스터의 단위중량을 곱하고, 소수 둘째 자리까지 ROUND로 정리합니다.

=IFERROR(ROUND(J2*VLOOKUP(C2,품목마스터!$A$2:$D$500,3,FALSE),2),0)

집계 시트에서 SUMIFS와 COUNTIFS로 월·라인·품목 조건을 동시에 겁니다

이제 집계 시트로 넘어갑니다. 집계!B2에는 기준월을 입력합니다. 이 셀은 반드시 실제 날짜여야 합니다. 예를 들어 2026년 7월을 집계한다면 B2에 아래 수식을 넣고 표시 형식만 yyyy-mm으로 바꿔도 좋습니다.

=DATE(2026,7,1)

집계!C2에는 라인코드 A03, 집계!D2에는 품목코드 FG-01001을 입력합니다. 그리고 집계!E2에는 순생산수량 합계를 구합니다.

=SUMIFS(실적원본!$J$2:$J$2000,실적원본!$I$2:$I$2000,$B$2,실적원본!$H$2:$H$2000,$C$2,실적원본!$C$2:$C$2000,$D$2,실적원본!$F$2:$F$2000,"완료")

위 수식은 J열 순생산수량을 합산하되, I열 실적월이 B2와 같고, H열 라인코드가 C2와 같고, C열 품목코드가 D2와 같고, F열 상태가 완료인 행만 더합니다. 예시 데이터 기준으로는 2행과 3행만 포함되고, 5행 취소 건과 6행 8월 건은 제외됩니다.

집계!F2에는 생산중량 합계를 구합니다. 이미 실적원본 L열에서 단위중량을 곱해두었으므로 SUMIFS의 합계 범위만 L열로 바꾸면 됩니다.

=SUMIFS(실적원본!$L$2:$L$2000,실적원본!$I$2:$I$2000,$B$2,실적원본!$H$2:$H$2000,$C$2,실적원본!$C$2:$C$2000,$D$2,실적원본!$F$2:$F$2000,"완료")

집계!G2에는 완료 건수를 세어봅니다. 금액이나 수량이 틀릴 때는 합계만 보지 말고 건수도 같이 봐야 원인을 빨리 찾을 수 있습니다.

=COUNTIFS(실적원본!$I$2:$I$2000,$B$2,실적원본!$H$2:$H$2000,$C$2,실적원본!$C$2:$C$2000,$D$2,실적원본!$F$2:$F$2000,"완료")

집계!H2에는 품목명을 표시해두면 검토자가 보기 편합니다. VLOOKUP 기준으로는 아래 수식을 사용합니다.

=IFERROR(VLOOKUP($D$2,품목마스터!$A$2:$D$500,2,FALSE),"마스터없음")

0이 나오거나 ERP와 다를 때는 이 순서로 검증합니다

SUMIFS 결과가 0이면 수식을 뜯어고치기 전에 조건별로 하나씩 세어보는 편이 빠릅니다. 먼저 기준월이 제대로 잡혔는지 확인합니다. 집계!J2에 아래 수식을 넣어 해당 월의 원본 건수가 있는지 봅니다.

=COUNTIFS(실적원본!$I$2:$I$2000,$B$2)

J2가 0이면 집계 수식 문제가 아니라 날짜 변환 문제일 가능성이 큽니다. 실적원본 I열이 2026-07-01 날짜인지, 집계 B2도 같은 날짜인지 확인하세요. 겉으로는 둘 다 2026-07처럼 보여도 하나는 텍스트이고 하나는 날짜일 수 있습니다.

다음은 라인코드입니다. 집계!J3에 아래 수식을 넣어 A03 라인 건수가 있는지 확인합니다.

=COUNTIFS(실적원본!$H$2:$H$2000,$C$2)

여기서 0이 나오면 MID 위치가 틀렸을 가능성이 큽니다. 실적원본 H열에 A03이 아니라 03- 또는 -A0처럼 잘못 추출되어 있으면 SUMIFS는 당연히 찾지 못합니다.

품목코드도 확인해야 합니다. 집계!J4에는 기준 품목코드가 원본에 몇 건 있는지 세어봅니다.

=COUNTIFS(실적원본!$C$2:$C$2000,$D$2)

품목코드 앞뒤에 공백이 숨어 있거나, 하이픈 모양이 다르면 같은 코드처럼 보여도 다른 값입니다. 이 경우에는 원본 C열과 집계 D2를 복사해서 빈 셀에 붙여넣은 뒤 글자 수나 코드 모양을 비교해보면 원인을 찾기 쉽습니다.

마스터 누락도 자주 발생합니다. 집계!J5에 아래 수식을 넣으면 품목마스터에서 찾지 못한 품목이 몇 건인지 바로 확인할 수 있습니다.

=COUNTIFS(실적원본!$K$2:$K$2000,"마스터없음")

이 값이 0보다 크면 생산중량이 0으로 계산된 행이 있을 수 있습니다. ERP의 수량 합계는 맞는데 중량 합계만 틀린다면, 대부분 단위중량 누락이나 품목코드 불일치가 원인입니다.

월마다 원본을 붙여넣는 파일이라면 VBA로 보조열 채우기를 버튼화할 수 있습니다

매월 ERP 원본을 붙여넣고 G열부터 L열까지 수식을 다시 복사하는 작업이 반복된다면 VBA가 잘 맞습니다. 아래 코드는 실적원본 시트의 마지막 행을 찾은 뒤, G열부터 L열까지 제목과 수식을 자동으로 채웁니다.

실행 전에는 파일을 반드시 다른 이름으로 저장해두세요. 매크로 실행 후에는 일반적인 Ctrl+Z 되돌리기가 되지 않습니다. 되돌려야 한다면 저장하지 않고 닫거나, 미리 저장해둔 백업 파일로 돌아가는 방식이 안전합니다.

Sub FillProductionHelperFormulas()
    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("실적원본")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow < 2 Then
        MsgBox "실적원본 시트에 데이터가 없습니다."
        Exit Sub
    End If

    With ws
        .Range("G1").Value = "정상작업일"
        .Range("H1").Value = "라인코드"
        .Range("I1").Value = "실적월"
        .Range("J1").Value = "순생산수량"
        .Range("K1").Value = "품목명"
        .Range("L1").Value = "생산중량"

        .Range("G2:G" & lastRow).Formula = "=IFERROR(IF(LEN(TEXT(A2,""0""))=8,DATE(LEFT(TEXT(A2,""0""),4),MID(TEXT(A2,""0""),5,2),RIGHT(TEXT(A2,""0""),2)),A2),"""")"
        .Range("H2:H" & lastRow).Formula = "=IFERROR(MID(B2,9,3),"""")"
        .Range("I2:I" & lastRow).Formula = "=IF(G2="""","""",EOMONTH(G2,-1)+1)"
        .Range("J2:J" & lastRow).Formula = "=IF(F2=""취소"",0,IFERROR(D2*1,0)-IFERROR(E2*1,0))"
        .Range("K2:K" & lastRow).Formula = "=IFERROR(VLOOKUP(C2,품목마스터!$A$2:$D$500,2,FALSE),""마스터없음"")"
        .Range("L2:L" & lastRow).Formula = "=IFERROR(ROUND(J2*VLOOKUP(C2,품목마스터!$A$2:$D$500,3,FALSE),2),0)"

        .Range("G:G").NumberFormat = "yyyy-mm-dd"
        .Range("I:I").NumberFormat = "yyyy-mm"
    End With

    MsgBox "생산실적 보조열 수식 입력이 완료되었습니다."
End Sub

이 코드는 시트 이름이 정확히 실적원본, 품목마스터일 때 동작합니다. 시트명이 다르면 코드의 시트 이름을 자기 파일에 맞게 바꿔야 합니다. 작업지시번호에서 라인코드를 뽑는 위치가 다르면 VBA 안의 MID(B2,9,3)도 함께 수정해야 합니다.

실무에서 적용할 때 볼 것

월별 생산실적 집계가 안 맞을 때는 SUMIFS 수식 하나만 의심하면 시간이 오래 걸립니다. 날짜가 진짜 날짜인지, 라인코드가 정확히 추출됐는지, 취소 건과 불량수량이 반영됐는지, 품목마스터 단위중량이 빠지지 않았는지 순서대로 확인해야 합니다.

특히 기준월 셀은 반드시 월 첫날 날짜로 맞추는 습관을 들이면 좋습니다. 집계 시트 B2를 2026-07이라는 텍스트로 두는 대신 DATE(2026,7,1)로 만들고 표시 형식만 yyyy-mm으로 바꾸면 SUMIFS 0 나옴 문제를 많이 줄일 수 있습니다.

원본을 직접 고치기보다 G열부터 L열처럼 보조열을 만들어두면 검산도 쉬워집니다. 나중에 ERP 담당자에게 차이를 설명할 때도 “A03 라인, FG-01001 품목, 2026년 7월 완료 건 2건, 순생산수량 2,077개”처럼 근거를 행 단위로 바로 확인할 수 있습니다.