엑셀 처리기한 초과 건수가 안 맞을 때: 접수일 텍스트·완료일 빈칸·COUNTIFS 검산법

월말 AS 처리 현황을 만들다 보면 엑셀 처리기한 초과 건수가 담당자별로 맞지 않는 일이 꽤 자주 생깁니다. 화면에서는 분명 7월 접수건이 48건인데, COUNTIFS로 세면 0건이 나오거나 초과 건수가 실제보다 적게 잡히는 식입니다.

특히 접수일이 20260703처럼 숫자처럼 보이는 텍스트로 들어오고, 완료일이 빈칸인 진행 중 건까지 포함해야 하는 파일에서 오류가 많이 납니다. 오늘은 AS 접수대장 기준으로 접수월, 담당자, 처리기한 초과 여부를 함수 조합으로 검산하는 흐름을 정리해 보겠습니다.

어떤 표에서 COUNTIFS 초과 건수가 틀어질까?

예시는 AS접수 시트에 데이터가 있고, 5행부터 실제 접수내역이 입력되는 상황입니다. A열부터 H열까지는 원본에서 내려받은 값이고, I열부터 M열은 계산용 보조열로 사용합니다.

조건 셀은 오른쪽에 둡니다. S2에는 기준월의 첫날인 2026-07-01을 날짜 형식으로 입력하고, T2에는 담당자명 김민수를 입력합니다. 결과는 U2 초과건수, V2 접수건수, W2 완료율, X2 평균처리일수에 표시하겠습니다.

먼저 확인할 것은 날짜가 진짜 날짜인지 여부입니다

COUNTIFS가 0으로 나오는 가장 흔한 원인은 조건식이 아니라 날짜 형식입니다. B열 접수일이 눈으로는 2026-07-03처럼 보여도 실제 값이 텍스트라면 >=2026-07-01 조건에 제대로 걸리지 않습니다.

그래서 원본 날짜를 바로 조건 범위로 쓰지 말고, I열에 접수일정리, J열에 완료일정리를 만들어 날짜값으로 바꿔둡니다. I5 셀에 아래 수식을 넣고 I500까지 복사합니다.

=IF(B5="","",IF(ISNUMBER(B5),B5,IF(LEN(B5)=8,DATE(LEFT(B5,4),MID(B5,5,2),RIGHT(B5,2)),DATE(LEFT(B5,4),MID(B5,6,2),RIGHT(B5,2)))))

이 수식은 B5가 이미 날짜 숫자이면 그대로 쓰고, 20260703 형태면 LEFT, MID, RIGHT로 연월일을 잘라 DATE로 다시 만듭니다. 2026-07-03처럼 하이픈이 있는 텍스트도 처리할 수 있도록 길이에 따라 MID 위치를 다르게 잡았습니다.

완료일도 같은 방식으로 정리합니다. J5 셀에는 아래 수식을 넣고 J500까지 복사합니다. 완료일이 빈칸이면 진행 중 건으로 봐야 하므로 빈칸은 그대로 둡니다.

=IF(G5="","",IF(ISNUMBER(G5),G5,IF(LEN(G5)=8,DATE(LEFT(G5,4),MID(G5,5,2),RIGHT(G5,2)),DATE(LEFT(G5,4),MID(G5,6,2),RIGHT(G5,2)))))

AS유형별 처리기한은 VLOOKUP으로 붙여야 검산이 쉬워집니다

처리기한은 건마다 직접 입력하면 나중에 수정하기 어렵습니다. 오른쪽 P3:Q6에 작은 기준표를 만들어두고, E열 AS유형을 기준으로 기한일수를 가져오면 관리가 훨씬 편합니다.

K5 셀에는 처리기한일수를 가져오는 수식을 넣습니다. 유형명이 기준표에 없으면 나중에 바로 찾을 수 있도록 확인이라는 문구가 나오게 했습니다.

=IFERROR(VLOOKUP(E5,$P$3:$Q$6,2,FALSE),"확인")

VLOOKUP 대신 XLOOKUP을 쓰는 환경이라면 아래처럼 작성해도 됩니다. 다만 팀원들과 파일을 주고받는 경우에는 호환성을 생각해서 VLOOKUP 버전이 더 무난할 때가 많습니다.

=IFERROR(XLOOKUP(E5,$P$3:$P$6,$Q$3:$Q$6),"확인")

또는 INDEX와 MATCH 조합을 선호한다면 아래 수식도 같은 결과를 냅니다. 실무에서는 VLOOKUP이 왼쪽 기준열만 찾는다는 제약이 있어서, 기준표 구조가 자주 바뀌는 파일에서는 INDEX MATCH가 편할 때가 있습니다.

=IFERROR(INDEX($Q$3:$Q$6,MATCH(E5,$P$3:$P$6,0)),"확인")

완료일이 빈칸인 진행 중 건은 어떻게 초과 판단할까?

여기서 실무 파일이 많이 갈립니다. 완료된 건은 완료일에서 접수일을 빼면 되지만, 아직 진행 중인 건은 기준월 말일까지 며칠이 지났는지 봐야 합니다. 그래야 7월 말 보고서 기준으로 미완료 초과 건을 잡을 수 있습니다.

L5 셀에는 처리일수를 계산합니다. J5 완료일이 있으면 완료일 기준, 없으면 S2 기준월의 말일인 EOMONTH(S2,0)을 기준으로 계산합니다.

=IF(I5="","",IF(J5="",EOMONTH($S$2,0)-I5,J5-I5))

이 방식의 장점은 진행 중 건도 초과 여부에 포함할 수 있다는 점입니다. 예를 들어 7월 2일 접수된 일반 건이 7월 말까지 완료되지 않았다면 처리일수는 29일로 계산되고, 기한 5일을 넘었으므로 초과로 잡힙니다.

M5 셀에는 초과여부를 표시합니다. K열이 확인이면 AS유형이 기준표에 없는 상태이므로 초과 판단을 하지 않고 먼저 기한표를 보게 만듭니다.

=IF(I5="","",IF(K5="확인","기한확인",IF(L5>K5,"초과","정상")))

담당자별 기준월 초과건수는 COUNTIFS로 세면 됩니다

이제 날짜 정리, 기한일수, 처리일수, 초과여부가 준비됐으니 결과 셀을 계산합니다. 기준월은 S2, 담당자는 T2에 입력되어 있다고 가정합니다.

U2에는 해당 담당자의 기준월 접수건 중 초과 건수를 계산합니다. 날짜 조건은 반드시 원본 B열이 아니라 정리된 I열을 기준으로 잡습니다.

=COUNTIFS($F$5:$F$500,$T$2,$I$5:$I$500,">="&$S$2,$I$5:$I$500,"<="&EOMONTH($S$2,0),$M$5:$M$500,"초과")

V2에는 같은 조건의 전체 접수건수를 계산합니다. 이 값이 화면에서 필터로 본 건수와 맞는지 먼저 비교해야 합니다.

=COUNTIFS($F$5:$F$500,$T$2,$I$5:$I$500,">="&$S$2,$I$5:$I$500,"<="&EOMONTH($S$2,0))

W2 완료율은 완료 상태 건수를 전체 접수건수로 나눕니다. 접수건수가 0일 때 #DIV/0! 오류가 나오지 않도록 IFERROR를 감쌌고, ROUND로 소수점 셋째 자리까지 정리했습니다.

=IFERROR(ROUND(COUNTIFS($F$5:$F$500,$T$2,$I$5:$I$500,">="&$S$2,$I$5:$I$500,"<="&EOMONTH($S$2,0),$H$5:$H$500,"완료")/$V$2,3),0)

X2에는 완료건만 대상으로 평균 처리일수를 계산합니다. AVERAGEIFS를 써도 되지만, 여기서는 SUMIFS와 COUNTIFS 조합으로 검산하기 쉽게 작성했습니다.

=IFERROR(ROUND(SUMIFS($L$5:$L$500,$F$5:$F$500,$T$2,$I$5:$I$500,">="&$S$2,$I$5:$I$500,"<="&EOMONTH($S$2,0),$H$5:$H$500,"완료")/COUNTIFS($F$5:$F$500,$T$2,$I$5:$I$500,">="&$S$2,$I$5:$I$500,"<="&EOMONTH($S$2,0),$H$5:$H$500,"완료"),1),0)

결과가 안 맞을 때 보는 체크리스트

수식은 맞는데 숫자가 계속 다르다면 아래 순서로 확인하는 것이 빠릅니다. 무작정 수식을 고치기보다, 어느 열에서 틀어졌는지 좁혀가야 합니다.

매월 같은 양식이면 수식 채우기 매크로도 편합니다

AS접수 파일을 매월 내려받아 I:M열 수식을 다시 넣는 작업이 반복된다면, 아래 매크로로 선택한 시트의 5행부터 마지막 접수번호 행까지 수식을 채울 수 있습니다. 실행 전에는 반드시 파일을 다른 이름으로 저장하거나 시트를 복사해 두는 것이 좋습니다.

Sub AS_처리기한_수식채우기()
    Dim ws As Worksheet
    Dim lastRow As Long

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

    If lastRow < 5 Then
        MsgBox "5행 이후 데이터가 없습니다."
        Exit Sub
    End If

    ws.Range("I5:I" & lastRow).Formula = "=IF(B5="""","""",IF(ISNUMBER(B5),B5,IF(LEN(B5)=8,DATE(LEFT(B5,4),MID(B5,5,2),RIGHT(B5,2)),DATE(LEFT(B5,4),MID(B5,6,2),RIGHT(B5,2)))))"
    ws.Range("J5:J" & lastRow).Formula = "=IF(G5="""","""",IF(ISNUMBER(G5),G5,IF(LEN(G5)=8,DATE(LEFT(G5,4),MID(G5,5,2),RIGHT(G5,2)),DATE(LEFT(G5,4),MID(G5,6,2),RIGHT(G5,2)))))"
    ws.Range("K5:K" & lastRow).Formula = "=IFERROR(VLOOKUP(E5,$P$3:$Q$6,2,FALSE),""확인"")"
    ws.Range("L5:L" & lastRow).Formula = "=IF(I5="""","""",IF(J5="""",EOMONTH($S$2,0)-I5,J5-I5))"
    ws.Range("M5:M" & lastRow).Formula = "=IF(I5="""","""",IF(K5=""확인"",""기한확인"",IF(L5>K5,""초과"",""정상"")))"

    MsgBox "처리기한 검산 수식을 채웠습니다."
End Sub

이 매크로는 현재 활성화된 시트에 적용됩니다. 다른 시트에 잘못 실행했다면 저장하지 말고 파일을 닫았다가 다시 열거나, 미리 복사해 둔 백업 시트로 되돌리면 됩니다. 이미 저장했다면 I:M열을 지우고 원본 시트에서 다시 복사하는 방식으로 복구하세요.

적용 전에 확인할 것

이 방식의 핵심은 원본 날짜를 그대로 믿지 않는 것입니다. 접수일과 완료일을 날짜값으로 정리한 뒤, 기준월은 S2의 첫날부터 EOMONTH(S2,0)까지로 잡아야 COUNTIFS 결과가 안정적으로 나옵니다.

또 하나는 완료일 빈칸 처리입니다. 진행 중 건을 제외하면 보고서상 초과 건수가 낮게 보일 수 있습니다. 월말 기준 처리기한 초과를 보고해야 한다면 완료일이 없는 건도 기준월 말일 기준으로 처리일수를 계산해 보세요.

마지막으로 VLOOKUP 결과가 확인으로 남아 있는 행은 반드시 먼저 정리해야 합니다. AS유형 기준표가 비어 있으면 아무리 COUNTIFS 수식이 정확해도 초과 여부 자체가 흔들립니다.