엑셀 월정액 청구금액이 안 맞을 때: 계약 시작일·종료일 걸친 일할계산 SUMIFS 검산법

월정액 서비스, 렌탈료, 유지보수료 정산을 하다 보면 엑셀 월별 청구금액이 안 맞음, SUMIFS 합계 누락, 계약 종료일 포함 여부 오류 같은 문제가 자주 나옵니다. 특히 계약 시작일이 월 중간이고 종료일도 월 중간이면 단순히 월단가를 합산하는 방식으로는 금액이 맞을 수 없습니다.

오늘은 계약목록 시트에서 기준월에 실제로 청구해야 할 기간만 잘라내고, 그 일수만큼 월단가를 일할계산한 뒤, 월마감 시트에서 거래처별·상품별로 SUMIFS 집계하는 방식으로 정리해 보겠습니다. 어려운 기능으로 한 번에 끝내기보다, 실무 파일에서 가장 많이 쓰는 IF, DATE, EOMONTH, ROUND, SUMIFS, COUNTIFS, XLOOKUP, VLOOKUP 조합으로 차근차근 맞추는 흐름입니다.

어떤 상황에서 월정액 청구 합계가 틀어질까?

예를 들어 유지보수 계약을 관리하는 파일이 있고, 시트는 계약목록과 월마감으로 나뉘어 있다고 가정해 보겠습니다. 계약목록 시트에는 5행부터 실제 데이터가 입력되어 있고, 월마감 시트에서는 기준월과 거래처코드, 상품코드를 입력하면 청구금액이 계산되어야 합니다.

월마감 시트는 아래처럼 구성합니다. 여기서 B2에는 기준월을 2026-07 형식의 텍스트로 입력한다고 하겠습니다. 날짜 서식이 섞인 파일에서도 안정적으로 계산하기 위해 기준월은 직접 DATE 함수로 변환해서 쓰겠습니다.

먼저 원인부터 확인: 계약기간이 기준월과 겹치는지 봐야 한다

월정액 청구에서 가장 흔한 실수는 계약시작일이 기준월 안에 들어온 계약을 통째로 한 달 청구하거나, 반대로 계약종료일이 기준월보다 앞에 있는데도 청구하는 경우입니다. 핵심은 계약기간과 기준월 기간이 하루라도 겹치는지입니다.

기준월이 2026-07이라면 월 시작일은 2026-07-01, 월 종료일은 2026-07-31입니다. 계약시작일이 2026-07-10이고 종료일이 빈칸이면 7월에는 7월 10일부터 7월 31일까지 22일만 청구해야 합니다.

계약목록에 계산용 열을 추가하면 SUMIFS가 훨씬 안전해진다

바로 월마감 시트에서 긴 SUMIFS 하나로 끝내려고 하면 수식이 복잡해지고, 오류가 났을 때 어디가 틀렸는지 찾기 어렵습니다. 실무에서는 계약목록 시트 오른쪽에 계산용 열을 몇 개 추가한 뒤, 그 결과를 SUMIFS로 모으는 방식이 훨씬 안정적입니다.

계약목록 시트의 J열부터 P열까지 아래 계산용 열을 추가합니다.

J5에는 기준월 시작일을 계산하는 수식을 입력합니다. 월마감!B2가 2026-07처럼 입력되어 있을 때, LEFT와 MID로 연도와 월을 꺼내 DATE로 실제 날짜를 만듭니다.

=DATE(LEFT(월마감!$B$2,4),MID(월마감!$B$2,6,2),1)

K5에는 기준월의 마지막 날짜를 구합니다.

=EOMONTH(J5,0)

L5에는 실제 청구 시작일을 계산합니다. 계약시작일이 기준월 시작일보다 늦으면 계약시작일부터, 그렇지 않으면 월초부터 청구합니다.

=IF(E5>J5,E5,J5)

M5에는 실제 청구 종료일을 계산합니다. 계약종료일이 빈칸이면 기준월말까지 청구하고, 종료일이 기준월말보다 빠르면 종료일까지 청구합니다.

=IF(F5="",K5,IF(F5<K5,F5,K5))

N5에는 청구일수를 계산합니다. 종료일이 시작일보다 빠른 계약은 기준월과 겹치지 않는 계약이므로 0일로 처리합니다.

=IF(M5<L5,0,M5-L5+1)

여기서 +1을 빼먹는 실수가 정말 많습니다. 7월 1일부터 7월 31일까지는 31일인데, 단순히 31일-1일로 계산하면 30이 됩니다. 시작일과 종료일을 모두 포함해서 청구하는 방식이라면 반드시 +1을 넣어야 합니다.

O5에는 기준월 전체 일수를 계산합니다.

=K5-J5+1

P5에는 최종 청구금액을 계산합니다. 원 단위 반올림이 필요하므로 ROUND를 사용했습니다.

=IF(N5=0,0,ROUND(G5*H5*N5/O5,0))

이제 J5:P5 수식을 2000행까지 복사합니다. 데이터가 500행까지만 있어도 범위는 넉넉히 2000행까지 잡아두면 다음 달에 계약이 추가되어도 수식 복사 누락을 줄일 수 있습니다.

월마감 시트에서 SUMIFS로 거래처·상품별 청구금액 모으기

계약목록에서 행별 청구금액이 계산되었다면, 월마감 시트에서는 SUMIFS로 필요한 조건만 모으면 됩니다. 월마감!B6에 아래 수식을 입력합니다.

=SUMIFS(계약목록!$P$5:$P$2000,계약목록!$B$5:$B$2000,$B$3,계약목록!$D$5:$D$2000,$B$4,계약목록!$N$5:$N$2000,">0",계약목록!$I$5:$I$2000,"<>테스트")

이 수식은 계약목록의 P열 청구금액 중에서 거래처코드가 월마감!B3과 같고, 상품코드가 월마감!B4와 같고, 청구일수가 0보다 크며, 상태가 테스트가 아닌 건만 합산합니다.

만약 상품코드와 상관없이 거래처 전체 청구금액을 보고 싶다면 상품코드 조건 한 쌍을 빼면 됩니다.

=SUMIFS(계약목록!$P$5:$P$2000,계약목록!$B$5:$B$2000,$B$3,계약목록!$N$5:$N$2000,">0",계약목록!$I$5:$I$2000,"<>테스트")

금액이 맞는지 COUNTIFS로 먼저 건수를 검산하자

정산표에서 금액만 보면 어디서 틀렸는지 찾기 어렵습니다. 월마감!B7에는 청구대상 건수를 같이 표시해 두는 것이 좋습니다. 금액이 이상할 때 먼저 건수가 예상과 맞는지 확인하면 원인 범위가 확 줄어듭니다.

=COUNTIFS(계약목록!$B$5:$B$2000,$B$3,계약목록!$D$5:$D$2000,$B$4,계약목록!$N$5:$N$2000,">0",계약목록!$I$5:$I$2000,"<>테스트")

예를 들어 C001 거래처의 SVC-A 상품이 7월에 3건 청구되어야 하는데 COUNTIFS 결과가 2라면, 금액 수식보다 계약시작일·종료일·상품코드부터 확인하는 것이 빠릅니다.

거래처명이 안 따라오면 XLOOKUP, 안 되면 VLOOKUP으로 확인

월마감!B3에 거래처코드만 입력하면 B5에 거래처명이 자동으로 표시되게 해두면 입력 오류를 바로 잡을 수 있습니다. 거래처마스터 시트에 A열은 거래처코드, B열은 거래처명이고 데이터가 A2:B300에 있다고 하겠습니다.

Microsoft 365나 Excel 2021 이상에서 XLOOKUP을 사용할 수 있다면 월마감!B5에 아래 수식을 입력합니다.

=IFERROR(XLOOKUP($B$3,거래처마스터!$A$2:$A$300,거래처마스터!$B$2:$B$300),"코드 없음")

XLOOKUP이 없는 버전이라면 VLOOKUP으로도 충분합니다.

=IFERROR(VLOOKUP($B$3,거래처마스터!$A$2:$B$300,2,FALSE),"코드 없음")

거래처코드가 왼쪽에 없거나, 조회 범위를 자유롭게 잡아야 한다면 INDEX와 MATCH 조합을 써도 됩니다.

=IFERROR(INDEX(거래처마스터!$B$2:$B$300,MATCH($B$3,거래처마스터!$A$2:$A$300,0)),"코드 없음")

거래처명이 코드 없음으로 나오면 SUMIFS 결과도 0이 나올 가능성이 큽니다. 이때는 실제로 계약목록 B열의 코드와 월마감 B3 코드가 같은지, 앞뒤 공백이나 앞자리 0 누락이 없는지 확인해야 합니다.

실무 체크리스트: SUMIFS보다 먼저 볼 것들

월정액 청구 파일은 날짜, 코드, 상태값이 조금만 틀어져도 합계가 달라집니다. 아래 표를 기준으로 하나씩 확인해 보세요.

날짜가 20260710처럼 들어온 파일은 어떻게 처리할까?

외부 시스템에서 내려받은 계약시작일과 종료일이 2026-07-10이 아니라 20260710처럼 들어오는 경우가 있습니다. 이 값이 숫자처럼 보이더라도 엑셀 날짜가 아니면 EOMONTH나 날짜 비교에서 엉뚱한 결과가 나올 수 있습니다.

이런 파일은 계약목록 시트에서 원본 E열과 F열을 바로 쓰지 말고, 오른쪽에 날짜정리 열을 따로 만들어 쓰는 편이 안전합니다. 예를 들어 Q열을 시작일정리, R열을 종료일정리로 둡니다.

Q5에는 E5의 8자리 값을 실제 날짜로 바꾸는 수식을 넣습니다.

=DATE(LEFT(E5,4),MID(E5,5,2),RIGHT(E5,2))

R5에는 종료일이 빈칸이면 빈칸으로 두고, 값이 있으면 날짜로 변환합니다.

=IF(F5="","",DATE(LEFT(F5,4),MID(F5,5,2),RIGHT(F5,2)))

그다음 L열과 M열 수식에서 E5, F5 대신 Q5, R5를 사용하면 됩니다.

=IF(Q5>J5,Q5,J5)

=IF(R5="",K5,IF(R5<K5,R5,K5))

이미 E열과 F열이 정상 날짜라면 굳이 이 과정을 거칠 필요는 없습니다. 다만 원본 파일이 매달 시스템에서 내려오는 형태라면 날짜정리 열을 만들어 두는 것이 나중에 검산하기 훨씬 편합니다.

앞자리 0이 있는 거래처코드는 TEXT로 맞춰두자

거래처코드가 00123처럼 앞자리 0을 포함하는 회사도 많습니다. 계약목록에서는 00123인데 월마감에는 123으로 입력되면 사람 눈에는 비슷해 보여도 엑셀은 다른 값으로 봅니다.

거래처코드가 항상 5자리 숫자 코드라면 월마감!B3 옆 C3에 정리 코드를 만들어 사용할 수 있습니다.

=TEXT(B3,"00000")

계약목록 B열도 숫자로 들어왔다면 같은 방식으로 보조열을 만들어 맞춘 뒤 SUMIFS 조건범위에 사용합니다. 코드 체계가 C001처럼 문자와 숫자가 섞여 있다면 TEXT로 억지 변환하지 말고, 마스터 기준 코드와 동일하게 입력되었는지 확인하는 쪽이 안전합니다.

금액 차이가 1원씩 날 때는 ROUND 위치를 확인하자

일할계산 정산에서 1원 차이가 나는 경우는 ROUND 위치 때문에 생기는 일이 많습니다. 행별로 반올림한 금액을 합산할지, 전체 금액을 합산한 뒤 마지막에 반올림할지는 회사 정산 기준에 따라 다릅니다.

이번 예제의 P열 수식은 계약 한 줄마다 반올림하는 방식입니다.

=IF(N5=0,0,ROUND(G5*H5*N5/O5,0))

계약별 청구서가 따로 나가거나 계약번호 단위로 금액을 확정한다면 이 방식이 적합합니다. 반대로 거래처 전체 금액을 먼저 합산한 뒤 마지막에 반올림해야 하는 회사라면 P열에서는 반올림하지 않고, 월마감 B6에서 ROUND를 씌우는 방식으로 바꿔야 합니다.

=ROUND(SUMIFS(계약목록!$P$5:$P$2000,계약목록!$B$5:$B$2000,$B$3,계약목록!$D$5:$D$2000,$B$4,계약목록!$N$5:$N$2000,">0"),0)

다만 이 방식을 쓰려면 P열의 청구금액 수식에서 ROUND를 제거해야 합니다. 행별 반올림과 최종 반올림을 동시에 적용하면 오히려 차이가 커질 수 있습니다.

적용 전에 확인할 것

이 방식의 핵심은 계약목록에서 먼저 기준월과 겹치는 청구일수를 계산하고, SUMIFS는 이미 계산된 청구금액만 집계하게 만드는 것입니다. 수식이 조금 길어 보이지만, 오류가 났을 때 J열부터 P열까지 차례로 보면 어느 단계에서 틀어졌는지 바로 찾을 수 있습니다.

실제 파일에 적용할 때는 월마감!B2의 기준월 형식, 계약목록 E열과 F열의 날짜 형식, 거래처코드 앞자리 0, SUMIFS 범위 크기부터 확인하세요. 특히 N열 청구일수가 예상과 맞는지만 확인해도 월정액 청구금액 오류의 대부분은 빠르게 잡을 수 있습니다.