엑셀 발주 미입고 수량이 안 맞을 때: SUMIFS 0 나옴·납기일 텍스트·부분입고 검산법

발주 관리 파일에서 가장 자주 틀어지는 부분이 ‘미입고 수량’입니다. 발주번호별로 입고 수량을 SUMIFS로 끌어왔는데 0이 나오거나, 이미 일부 입고된 건인데 납기지연으로 잡히지 않거나, 월말 보고서의 미입고 금액이 구매팀 집계와 다르게 나오는 경우가 많습니다.

특히 발주번호가 숫자처럼 보이지만 실제로는 텍스트이거나, 납기일이 날짜가 아니라 2026.07.15 같은 문자로 들어온 파일에서는 COUNTIFS와 SUMIFS가 조용히 엉뚱한 결과를 냅니다. 오늘은 실무 발주현황 파일 기준으로 미입고 수량, 납기지연 상태, 미입고 금액까지 한 번에 검산하는 흐름으로 정리해 보겠습니다.

발주현황은 맞는데 입고내역을 붙이면 SUMIFS가 0으로 나오는 상황

예시 파일은 시트가 3개라고 가정하겠습니다. 발주현황 시트에는 발주한 원본 데이터가 있고, 입고내역 시트에는 창고에서 실제 입고 처리한 내역이 있습니다. 품목마스터 시트에는 품목별 표준단가가 있습니다.

발주현황 시트의 구조는 아래와 같습니다. 데이터는 5행부터 5000행까지 있다고 보겠습니다.

입고내역 시트는 5행부터 아래처럼 쌓입니다.

이때 발주현황 시트에서 J열에는 누적입고수량, K열에는 미입고수량, L열에는 입고상태, M열에는 지연일수, N열에는 정리된 납기일, O열에는 표준단가, P열에는 미입고금액을 계산해 보겠습니다. 기준일은 발주현황!R2 셀에 입력합니다. 예를 들어 R2에 2026-07-21을 넣어 두는 방식입니다.

원인은 대부분 발주번호 불일치, 납기일 텍스트, 부분입고 누락입니다

SUMIFS가 0으로 나올 때 수식만 계속 바꾸는 경우가 많은데, 실제 원인은 데이터 모양이 다른 경우가 훨씬 많습니다. 발주현황의 발주번호는 PO-260701인데 입고내역에는 뒤에 공백이 붙어 있거나, 품목코드는 같아 보이지만 한쪽은 앞뒤 공백이 들어가 있을 수 있습니다.

또 하나는 납기일입니다. 셀에는 2026.07.15처럼 보이지만 엑셀이 날짜로 인식하지 못하면 COUNTIFS에서 기준일보다 작다는 조건이 제대로 먹지 않습니다. 특히 ERP에서 내려받은 파일, 웹 시스템에서 복사한 파일은 날짜가 문자로 들어오는 일이 많습니다.

마지막으로 부분입고입니다. 한 발주번호가 여러 번 나눠 입고되면 VLOOKUP으로 첫 줄만 가져와서는 절대 맞지 않습니다. 이때는 발주번호와 품목코드 두 조건으로 입고수량을 합산해야 하므로 SUMIFS가 가장 안정적입니다.

납기일을 먼저 진짜 날짜로 바꿔야 지연 판단이 흔들리지 않습니다

발주현황 시트의 N열을 정리납기일로 사용하겠습니다. N5 셀에 아래 수식을 입력한 뒤 N5000행까지 내려 복사합니다. H열 납기일이 이미 날짜이면 그대로 쓰고, 텍스트이면 LEFT, MID, RIGHT, DATE 조합으로 날짜값을 다시 만듭니다.

=IFERROR(IF(ISNUMBER(H5),H5,DATE(LEFT(H5,4),MID(H5,6,2),RIGHT(H5,2))),"")

이 수식에서 LEFT(H5,4)는 연도 2026을 가져오고, MID(H5,6,2)는 월 07을 가져오며, RIGHT(H5,2)는 일 15를 가져옵니다. DATE 함수로 묶으면 엑셀이 계산 가능한 실제 날짜가 됩니다.

만약 원본 납기일이 20260715 형태라면 월과 일을 가져오는 위치가 달라집니다. 그 경우에는 N5 수식을 아래처럼 바꾸면 됩니다.

=IFERROR(IF(ISNUMBER(H5),H5,DATE(LEFT(H5,4),MID(H5,5,2),RIGHT(H5,2))),"")

수식을 넣은 뒤 N열 표시 형식을 날짜로 바꿔 보세요. 제대로 변환되었다면 2026-07-15처럼 표시되고, 셀 맞춤이 기본값 기준 오른쪽으로 붙는 경우가 많습니다. 빈칸이 나오면 H열의 원본 날짜에 점, 슬래시, 공백 등이 섞여 있는지 먼저 확인해야 합니다.

부분입고는 VLOOKUP이 아니라 SUMIFS로 누적해야 합니다

이제 J열에 누적입고수량을 계산하겠습니다. 발주현황의 A열 발주번호와 E열 품목코드를 기준으로, 입고내역 시트의 D열 입고수량을 합산합니다. J5 셀에 아래 수식을 입력합니다.

=IFERROR(SUMIFS(입고내역!$D$5:$D$5000,입고내역!$B$5:$B$5000,$A5,입고내역!$C$5:$C$5000,$E5),0)

여기서 합계 범위는 입고내역!$D$5:$D$5000입니다. 조건 범위는 발주번호가 있는 입고내역!$B$5:$B$5000, 품목코드가 있는 입고내역!$C$5:$C$5000입니다.

이 수식이 중요한 이유는 한 발주가 100개, 150개처럼 여러 번 입고되어도 모두 더해 준다는 점입니다. VLOOKUP으로 입고수량을 가져오면 첫 번째 100개만 가져오고 두 번째 150개를 놓치기 쉽습니다.

K열 미입고수량은 발주수량에서 누적입고수량을 빼면 됩니다. 다만 입고수량이 발주수량보다 많이 들어온 예외가 있을 수 있으니, 음수도 그대로 보이게 해서 확인하는 편이 좋습니다.

=IFERROR(G5-J5,"")

만약 초과입고를 0으로 처리하고 싶다면 아래처럼 IF를 한 번 더 감싸면 됩니다. 다만 실무 검산 단계에서는 초과입고를 숨기지 않는 쪽을 추천합니다.

=IFERROR(IF(G5-J5<0,0,G5-J5),"")

납기지연, 부분입고, 입고완료 상태를 IF로 나누기

L열에는 상태를 표시하겠습니다. 기준일은 발주현황 시트 R2에 입력되어 있다고 했습니다. L5 셀에 아래 수식을 넣습니다.

=IF($K5=0,"입고완료",IF($N5="","납기확인",IF($N5<$R$2,"납기지연","진행중")))

이 수식은 먼저 미입고수량이 0인지 확인합니다. 미입고수량이 0이면 납기일이 지났더라도 현재 기준으로는 입고완료입니다. 그다음 정리납기일이 비어 있으면 납기확인으로 표시합니다.

아직 미입고수량이 남아 있고, 정리납기일이 기준일보다 작으면 납기지연입니다. 그 외에는 진행중으로 처리합니다. 이런 순서가 중요합니다. 납기일 조건을 먼저 보면 이미 입고완료된 과거 발주까지 지연으로 표시될 수 있습니다.

M열에는 지연일수를 계산합니다. 납기지연일 때만 기준일에서 납기일을 빼고, 나머지는 빈칸으로 둡니다.

=IF($L5="납기지연",$R$2-$N5,"")

지연일수가 이상하게 45200 같은 큰 숫자로 보인다면 M열 셀 서식이 날짜로 되어 있을 가능성이 큽니다. M열은 날짜가 아니라 일수이므로 셀 서식을 일반 또는 숫자로 바꿔 주세요.

품목마스터에서 단가를 가져와 미입고금액까지 계산하기

미입고수량만 맞아도 업무는 많이 편해지지만, 월말 보고에서는 보통 미입고금액까지 필요합니다. 품목마스터 시트는 A열 품목코드, B열 품목명, C열 단위, D열 표준단가, E열 사용여부로 구성되어 있고 데이터는 5행부터 1000행까지 있다고 가정하겠습니다.

O5 셀에는 품목코드 기준으로 표준단가를 가져옵니다. Microsoft 365나 Excel 2021 이상을 사용한다면 XLOOKUP을 쓰면 읽기가 쉽습니다.

=IFERROR(XLOOKUP(E5,품목마스터!$A$5:$A$1000,품목마스터!$D$5:$D$1000),0)

XLOOKUP을 사용할 수 없는 환경이라면 VLOOKUP으로 바꿔도 됩니다. 품목코드가 품목마스터 범위의 첫 번째 열에 있어야 한다는 점만 기억하면 됩니다.

=IFERROR(VLOOKUP(E5,품목마스터!$A$5:$D$1000,4,FALSE),0)

품목코드가 마스터의 첫 열이 아니라 중간에 있을 때는 INDEX와 MATCH 조합이 유용합니다. 예를 들어 품목마스터에서 품목코드가 B열, 표준단가가 E열이라면 아래처럼 쓸 수 있습니다.

=IFERROR(INDEX(품목마스터!$E$5:$E$1000,MATCH(E5,품목마스터!$B$5:$B$1000,0)),0)

P5 셀에는 미입고금액을 계산합니다. 수량과 단가를 곱한 뒤 ROUND로 원 단위 반올림을 맞춥니다. 회사 기준에 따라 반올림, 버림, 절사가 다를 수 있으니 보고서 기준을 먼저 확인하세요.

=IFERROR(ROUND(K5*O5,0),0)

거래처별 납기지연 건수와 미입고금액을 검산하는 방법

이제 결과가 맞는지 요약 영역에서 검증해 보겠습니다. 발주현황 시트의 T2에는 확인할 거래처코드를 입력합니다. 예를 들어 T2에 V0012를 넣습니다. U2에는 납기지연 건수, V2에는 미입고수량 합계, W2에는 미입고금액 합계를 표시하겠습니다.

U2에는 COUNTIFS로 거래처코드와 상태를 동시에 조건으로 걸어 줍니다.

=COUNTIFS($C$5:$C$5000,$T$2,$L$5:$L$5000,"납기지연")

V2에는 해당 거래처의 납기지연 건에 대한 미입고수량만 합산합니다.

=SUMIFS($K$5:$K$5000,$C$5:$C$5000,$T$2,$L$5:$L$5000,"납기지연")

W2에는 미입고금액을 합산합니다.

=SUMIFS($P$5:$P$5000,$C$5:$C$5000,$T$2,$L$5:$L$5000,"납기지연")

여기서 흔한 실수는 거래처명을 조건으로 쓰는 것입니다. 거래처명은 띄어쓰기, 주식회사 표기, 약칭 때문에 흔들릴 수 있습니다. 가능하면 C열 거래처코드처럼 고유한 값을 기준으로 집계하는 편이 안전합니다.

SUMIFS가 계속 0일 때 확인해야 할 체크 포인트

수식이 맞는데도 누적입고수량이 0이면 아래 순서로 확인해 보세요. 먼저 발주현황 A5의 발주번호를 복사해서 입고내역 B열에서 찾기 합니다. 눈으로 같은 값처럼 보여도 검색이 안 되면 공백이나 특수문자가 섞였을 가능성이 큽니다.

다음은 품목코드입니다. 발주번호만 맞고 품목코드가 다르면 SUMIFS는 당연히 0을 반환합니다. 입고내역에서 품목코드가 변경 코드로 들어오는 회사도 있으므로, 이 경우에는 품목마스터에 구코드와 신코드 매핑표를 따로 두고 XLOOKUP이나 VLOOKUP으로 표준 코드를 맞춘 뒤 집계해야 합니다.

입고수량 D열이 숫자인지도 확인해야 합니다. D열 왼쪽 위에 초록색 표시가 있거나, 합계가 안 되면 텍스트 숫자일 수 있습니다. 빈 셀에 1을 입력해 복사한 뒤 입고수량 범위를 선택하고 선택하여 붙여넣기에서 곱하기를 적용하면 숫자로 바뀌는 경우가 많습니다.

마지막으로 범위 길이를 확인합니다. 발주현황은 5000행까지인데 입고내역 수식 범위가 1000행까지만 잡혀 있으면 최근 입고분이 빠집니다. 실무 파일에서는 월말에 행이 늘어나므로 SUMIFS 범위를 넉넉하게 잡거나, 매월 복사 시 범위가 줄어들지 않았는지 확인해야 합니다.

발주번호 앞뒤 공백이 많다면 보조열로 정리해서 집계하기

ERP에서 내려받은 발주번호에 공백이 자주 섞인다면 원본 열을 직접 고치기보다 보조열을 하나 만드는 방식이 안전합니다. 예를 들어 발주현황 Q열을 정리발주번호로 쓰고, 입고내역 E열도 정리발주번호로 사용합니다.

발주현황 Q5에는 아래 수식을 넣습니다.

=IFERROR(TRIM(A5),"")

입고내역 E5에도 아래 수식을 넣습니다.

=IFERROR(TRIM(B5),"")

그다음 J5 누적입고수량 수식을 정리발주번호 기준으로 바꾸면 됩니다.

=IFERROR(SUMIFS(입고내역!$D$5:$D$5000,입고내역!$E$5:$E$5000,$Q5,입고내역!$C$5:$C$5000,$E5),0)

TRIM은 앞뒤 공백과 중간의 불필요한 반복 공백을 줄이는 데 도움이 됩니다. 다만 눈에 잘 보이지 않는 특수 공백까지 모두 해결되는 것은 아니므로, 그래도 안 맞으면 발주번호를 새 열에 직접 복사해 값 붙여넣기한 뒤 불필요한 문자를 찾아 바꾸는 작업이 필요할 수 있습니다.

여러 거래처를 한 번에 보고 싶을 때 응용하기

거래처별로 납기지연 현황을 보고해야 한다면 요약표를 따로 만들면 편합니다. 예를 들어 발주현황 시트의 T5:T10에 거래처코드를 입력하고, U열에는 지연건수, V열에는 미입고수량, W열에는 미입고금액을 계산합니다.

U5에는 아래 수식을 넣고 아래로 복사합니다.

=COUNTIFS($C$5:$C$5000,$T5,$L$5:$L$5000,"납기지연")

V5에는 미입고수량 합계를 계산합니다.

=SUMIFS($K$5:$K$5000,$C$5:$C$5000,$T5,$L$5:$L$5000,"납기지연")

W5에는 미입고금액 합계를 계산합니다.

=SUMIFS($P$5:$P$5000,$C$5:$C$5000,$T5,$L$5:$L$5000,"납기지연")

이 요약표를 만들면 필터로 거래처를 하나씩 바꿔 보지 않아도 됩니다. 월말 회의 전에 거래처코드만 바꿔 넣거나, 자주 보는 거래처 목록을 고정해 두면 지연 현황을 빠르게 확인할 수 있습니다.

실무에서 적용할 때 볼 것

발주 미입고 파일은 단순히 발주수량에서 입고수량을 빼는 문제가 아닙니다. 발주번호와 품목코드가 같은 기준으로 정리되어 있어야 하고, 납기일은 반드시 계산 가능한 날짜여야 하며, 부분입고는 SUMIFS로 누적해야 합니다.

추천하는 작업 순서는 정리납기일을 먼저 만들고, 누적입고수량과 미입고수량을 계산한 뒤, 상태값을 IF로 나누는 방식입니다. 그다음 COUNTIFS와 SUMIFS로 거래처별 요약을 만들어 원본 필터 결과와 비교하면 오류를 빨리 잡을 수 있습니다.

수식이 길어 보여도 구조는 단순합니다. 날짜는 DATE로 정리하고, 수량은 SUMIFS로 합산하고, 상태는 IF로 판단하고, 금액은 ROUND로 마감 기준을 맞추는 흐름입니다. 이 틀만 잡아 두면 발주, 입고, 외주, 자재, 상품 매입 파일에도 거의 같은 방식으로 응용할 수 있습니다.