엑셀 근태수당 합계 안 맞을 때: 사번 앞자리 0·날짜 텍스트·SUMIFS 0 나옴 한 번에 잡기

월말마다 근태수당을 계산하는 파일에서 SUMIFS 합계가 0으로 나오거나, 특정 직원의 야근시간만 빠지는 경우가 있습니다. 원장을 보면 분명히 근무일도 있고 사번도 맞아 보이는데, 보고서에서는 시간이 비거나 수당이 다르게 계산되는 증상입니다.

특히 인사 시스템에서 내려받은 근태 원장과 직원 마스터를 붙여 쓰는 파일에서 자주 생깁니다. 사번 앞자리 0이 사라지고, 근무일이 날짜가 아니라 20260603 같은 숫자처럼 들어오고, 인정시간도 문자로 섞여 있으면 SUMIFS, VLOOKUP, XLOOKUP이 모두 어긋날 수 있습니다.

근태수당 보고서에서 실제로 많이 보이는 표 구조

예시 파일은 근태원장 시트와 수당계산 시트가 있다고 가정하겠습니다. 근태원장 시트의 A4:H4에는 제목이 있고, 실제 데이터는 A5:H5000 범위에 쌓입니다.

수당계산 시트에서는 B2에 기준월을 입력합니다. 예를 들어 B2에는 2026-06-01 날짜가 들어 있고, B3에는 조회할 부서명, A6:H6부터 직원별 결과를 표시합니다.

원인은 대부분 ‘보이는 값’과 ‘실제 값’이 다른 데 있습니다

근태수당 파일에서 가장 흔한 원인은 세 가지입니다. 첫째, 사번이 한쪽은 001234이고 다른 쪽은 1234입니다. 화면에서는 비슷해 보여도 엑셀은 다른 값으로 봅니다.

둘째, 근무일이 진짜 날짜가 아닙니다. 20260603은 사람이 보기에는 2026년 6월 3일이지만, 엑셀 날짜 기준으로는 그냥 숫자일 수 있습니다. 이 상태에서 >=B2, <=EOMONTH(B2,0) 조건을 걸면 월별 합계가 0으로 나올 수 있습니다.

셋째, 인정시간이 문자입니다. G열에 2.5처럼 보이지만 왼쪽 정렬되어 있거나, 뒤에 공백이 붙어 있으면 합계에서 빠질 수 있습니다. 수당 계산에서는 0.5시간 차이도 금액으로 바로 튀기 때문에 반드시 정리 열을 만들어 확인하는 편이 안전합니다.

근태원장 옆에 정리 열을 만들면 SUMIFS가 안정적으로 돌아갑니다

원본 데이터를 직접 고치기보다, 근태원장 시트의 I열부터 L열까지 보조 열을 만들어 두는 방식을 추천합니다. 나중에 원장을 다시 붙여넣어도 수식만 아래로 복사하면 같은 방식으로 정리됩니다.

근태원장 시트 I4:L4에 아래 제목을 입력합니다.

I5에는 근무일을 날짜로 바꾸는 수식을 넣습니다. A열 값이 20260603처럼 8자리로 들어온 경우를 기준으로 합니다.

=IFERROR(DATE(LEFT(A5,4),MID(A5,5,2),RIGHT(A5,2)),A5)

이 수식은 A5의 앞 4자리를 연도, 가운데 2자리를 월, 마지막 2자리를 일로 잘라 DATE 함수에 넣습니다. 이미 날짜로 들어온 값이 있다면 IFERROR 뒤의 A5가 반환되도록 해 두었습니다.

J5에는 사번을 6자리 문자로 맞추는 수식을 넣습니다. 사번 길이가 회사마다 다르면 000000 부분만 자리수에 맞게 바꾸면 됩니다.

=TEXT(B5,"000000")

K5에는 월 비교용 값을 만듭니다. 보고서에서 날짜 범위로 조건을 걸어도 되지만, 검산용으로 월 열을 하나 만들어두면 필터 확인이 쉬워집니다.

=TEXT(I5,"yyyy-mm")

L5에는 인정시간을 숫자로 바꿉니다. G열이 이미 숫자면 그대로 숫자가 되고, 문자로 들어온 2.5도 계산 가능한 값으로 바뀝니다.

=IFERROR(G5*1,0)

이제 I5:L5 수식을 데이터 끝 행인 5000행까지 복사합니다. 실제 파일에서 데이터가 2만 행이라면 범위도 20000행까지 맞춰 주세요.

직원 마스터에서 이름, 부서, 시급을 먼저 정확히 가져옵니다

직원 마스터는 수당계산 시트의 J4:N200에 있다고 하겠습니다. J열은 사번, K열은 이름, L열은 부서, M열은 직급, N열은 시급입니다. 여기서도 사번은 6자리 문자로 통일되어 있어야 합니다.

수당계산 시트 A6에는 조회할 사번을 입력합니다. A6에 사용자가 1234라고 입력해도 자동으로 6자리로 맞추고 싶다면, 별도 입력 열을 두는 것이 더 좋지만 여기서는 A6에 001234가 들어온 것으로 설명하겠습니다.

B6 이름은 VLOOKUP으로 가져올 수 있습니다.

=IFERROR(VLOOKUP(TEXT($A6,"000000"),$J$4:$N$200,2,FALSE),"")

C6 부서는 같은 방식으로 3번째 열을 가져옵니다.

=IFERROR(VLOOKUP(TEXT($A6,"000000"),$J$4:$N$200,3,FALSE),"")

F6 시급은 5번째 열입니다.

=IFERROR(VLOOKUP(TEXT($A6,"000000"),$J$4:$N$200,5,FALSE),0)

Microsoft 365나 Excel 2021 이상을 쓰는 환경이라면 XLOOKUP으로도 같은 작업을 할 수 있습니다. VLOOKUP은 조회 기준보다 왼쪽에 있는 값을 가져오기 어렵지만, XLOOKUP은 범위를 따로 지정할 수 있어 마스터 열 위치가 바뀌어도 관리가 편합니다.

=IFERROR(XLOOKUP(TEXT($A6,"000000"),$J$4:$J$200,$K$4:$K$200),"")

XLOOKUP을 지원하지 않는 버전에서는 INDEX와 MATCH 조합을 쓰면 됩니다. 예를 들어 이름을 가져오는 수식은 아래와 같습니다.

=IFERROR(INDEX($K$4:$K$200,MATCH(TEXT($A6,"000000"),$J$4:$J$200,0)),"")

야근시간과 휴일시간은 정리 열 기준으로 SUMIFS를 걸어야 합니다

이제 핵심인 시간 합계입니다. 수당계산 시트 D6에는 A6 사번의 기준월 야근시간을 표시합니다. 근태원장 시트에서 정리근무일은 I열, 정리사번은 J열, 근태코드는 D열, 정리인정시간은 L열입니다.

기준월 시작일은 수당계산!B2이고, 월말은 EOMONTH로 계산합니다. 그래서 D6 수식은 다음처럼 작성합니다.

=SUMIFS(근태원장!$L$5:$L$5000,
근태원장!$J$5:$J$5000,TEXT($A6,"000000"),
근태원장!$D$5:$D$5000,"OT",
근태원장!$I$5:$I$5000,">="&$B$2,
근태원장!$I$5:$I$5000,"<="&EOMONTH($B$2,0),
근태원장!$H$5:$H$5000,"승인")

E6에는 휴일근무시간을 계산합니다. 근태코드 조건만 HD로 바꾸면 됩니다.

=SUMIFS(근태원장!$L$5:$L$5000,
근태원장!$J$5:$J$5000,TEXT($A6,"000000"),
근태원장!$D$5:$D$5000,"HD",
근태원장!$I$5:$I$5000,">="&$B$2,
근태원장!$I$5:$I$5000,"<="&EOMONTH($B$2,0),
근태원장!$H$5:$H$5000,"승인")

여기서 중요한 포인트는 합계 범위와 조건 범위의 행 개수가 반드시 같아야 한다는 점입니다. 예를 들어 합계 범위가 $L$5:$L$5000인데 조건 범위가 $J$5:$J$4999이면 오류가 나거나 의도와 다르게 계산됩니다.

지급액 계산은 ROUND로 반올림 기준을 명확히 둡니다

회사 규정상 야근은 시급의 1.5배, 휴일근무는 시급의 2배로 계산한다고 가정하겠습니다. 수당계산 시트 G6에는 지급액을 계산합니다.

=ROUND((D6*$F6*1.5)+(E6*$F6*2),0)

원 단위 반올림이 아니라 10원 단위, 100원 단위 절사가 필요하면 ROUND 자리수를 조정해야 합니다. 예를 들어 10원 단위 반올림은 ROUND(금액,-1), 100원 단위 반올림은 ROUND(금액,-2)입니다.

계산식에는 회사 수당 규정이 그대로 들어가야 합니다. 야근 1.5배, 휴일 2배가 아닌 사업장도 있으니, 배율은 별도 셀에 빼두고 참조하는 방식도 좋습니다.

SUMIFS 결과가 0일 때는 COUNTIFS로 먼저 건수부터 확인합니다

합계가 0이면 바로 수식을 고치기보다, 해당 조건에 맞는 행이 실제로 잡히는지 COUNTIFS로 확인하는 것이 빠릅니다. 수당계산 시트 H6에 검증용 건수를 넣어 보겠습니다.

=COUNTIFS(근태원장!$J$5:$J$5000,TEXT($A6,"000000"),
근태원장!$D$5:$D$5000,"OT",
근태원장!$I$5:$I$5000,">="&$B$2,
근태원장!$I$5:$I$5000,"<="&EOMONTH($B$2,0),
근태원장!$H$5:$H$5000,"승인")

H6 결과가 0이면 합계 문제가 아니라 조건 매칭 문제입니다. 이때는 사번, 날짜, 근태코드, 승인 문구 중 하나가 맞지 않는 것입니다. 반대로 H6이 3인데 D6 야근시간이 0이면 L열 정리인정시간이 숫자로 변환되지 않았을 가능성이 큽니다.

보고서 금액이 안 맞을 때 확인하는 순서

먼저 근태원장 J열 정리사번을 필터로 열어 A6 사번과 같은 값이 있는지 확인합니다. 001234를 찾고 있는데 원장에는 1234 또는 001234 처럼 공백이 붙어 있으면 SUMIFS가 놓칩니다.

다음으로 I열 정리근무일을 확인합니다. 셀 표시 형식을 날짜로 바꿨을 때 2026-06-03처럼 정상 표시되어야 합니다. 날짜가 이상한 숫자로 보이거나 1900년대로 바뀌면 LEFT, MID, RIGHT로 자르는 위치가 원본 형식과 맞지 않는 것입니다.

그다음 D열 근태코드와 H열 승인 상태를 봅니다. 코드가 OT가 아니라 연장, O/T처럼 들어오는 파일도 있습니다. 승인도 승인과 승인완료가 섞여 있으면 조건을 맞게 바꿔야 합니다.

마지막으로 L열 정리인정시간 합계를 직접 확인합니다. 필터로 해당 사번과 기준월, OT만 걸고 L열 아래 상태 표시줄의 합계를 보면 D6 값과 일치해야 합니다.

부서별 총액까지 한 번에 검산하는 응용 수식

수당계산 시트에서 B3에 부서명을 넣고, 해당 부서 직원들의 지급액 합계를 보고 싶다면 직원별 계산표에 부서 열이 필요합니다. 앞에서 C6에 부서를 가져왔으므로, G6:G200에는 직원별 지급액이 있다고 보겠습니다.

B4에 부서별 지급액 합계를 표시하려면 아래 수식을 사용할 수 있습니다.

=SUMIFS($G$6:$G$200,$C$6:$C$200,$B$3)

부서별 인원수도 같이 검산하면 좋습니다. B5에 해당 부서 직원 수를 세려면 COUNTIFS를 사용합니다.

=COUNTIFS($C$6:$C$200,$B$3,$A$6:$A$200,"<>")

이렇게 부서 총액과 인원수를 같이 보면, 특정 직원이 마스터에서 누락되어 지급액이 빠진 상황을 빨리 찾을 수 있습니다. 특히 부서명이 영업1팀, 영업 1팀처럼 공백 하나 차이로 갈리는 경우가 많으니 부서 마스터도 함께 정리해 두는 편이 좋습니다.

같은 구조를 다른 정산 파일에도 적용할 때 볼 것

이번 방식의 핵심은 원본을 믿고 바로 SUMIFS를 거는 것이 아니라, 날짜·코드·금액을 계산 가능한 형태로 먼저 정리하는 것입니다. 근태수당뿐 아니라 출장비, 식대, 택배비, 외주비 정산에서도 같은 문제가 반복됩니다.

사번이나 거래처코드처럼 앞자리 0이 중요한 값은 TEXT로 자리수를 통일하고, 날짜처럼 보이는 숫자는 DATE와 LEFT, MID, RIGHT로 실제 날짜로 바꿔야 합니다. 금액이나 시간은 IFERROR를 붙여 숫자로 변환해 두면 집계가 훨씬 안정적입니다.

SUMIFS가 0으로 나올 때는 합계 수식만 오래 들여다보지 말고 COUNTIFS로 조건에 잡히는 행 수부터 확인해 보세요. 건수가 잡히는지, 잡히는데 합계만 0인지 나누어 보면 원인이 훨씬 빨리 보입니다.