엑셀 입금 확인 자동 표시: SUMIF와 IF로 미입금·부분입금 한 번에 구분하기

엑셀에서 입금 확인표를 만들다 보면 “청구액은 있는데 입금됐는지 자동으로 표시하고 싶다”, “부분입금은 어떻게 구분하지?”, “입금내역이 여러 줄이면 VLOOKUP으로 왜 안 맞지?” 같은 상황을 자주 만납니다. 특히 초보자 입장에서는 청구내역 표와 입금내역 표가 따로 있을 때, 어느 셀에 어떤 수식을 넣어야 하는지부터 헷갈리기 쉽습니다.

이번 예제는 작은 정산표 하나를 끝까지 개선하는 방식으로 볼게요. 핵심은 청구번호를 기준으로 입금액을 합산한 뒤, 청구액과 비교해서 상태를 표시하는 것입니다. 어려운 기능 없이 SUMIF, IF, SUMIFS, COUNTIF만 사용합니다.

실무에서 자주 만나는 입금 확인표 구조

아래처럼 한 시트 안에 왼쪽에는 청구내역, 오른쪽에는 입금내역이 있다고 가정하겠습니다. 실제 회사 파일에서도 세금계산서 발행 내역은 왼쪽, 통장 입금 내역은 오른쪽에 붙여 두고 대조하는 경우가 많습니다.

청구내역은 A3:G9 범위에 있습니다. 3행은 제목 행이고, 실제 데이터는 4행부터 9행까지입니다.

예시 데이터는 아래처럼 입력되어 있다고 생각하면 됩니다.

입금내역은 같은 시트의 H3:K10 범위에 있다고 하겠습니다. 여기서 중요한 점은 같은 청구번호가 여러 번 나올 수 있다는 것입니다. 예를 들어 거래처가 350,000원 중 200,000원을 먼저 보내고, 나중에 150,000원을 추가로 보낼 수 있습니다.

왜 VLOOKUP보다 SUMIF가 먼저일까?

초보자분들이 여기서 가장 많이 하는 실수는 입금액을 가져오려고 VLOOKUP을 쓰는 것입니다. VLOOKUP은 기준값을 찾아서 옆 칸의 값을 가져오는 함수라서, 같은 청구번호가 여러 줄 있을 때는 첫 번째로 찾은 값만 가져오는 경우가 많습니다.

이번 예시에서 INV-260701은 입금내역에 두 번 나옵니다. 200,000원과 150,000원을 합쳐야 실제 입금합계가 350,000원인데, 단순 조회만 하면 200,000원만 가져와서 부분입금처럼 보일 수 있습니다.

그래서 이 상황에서는 “찾아서 가져오기”가 아니라 같은 청구번호의 입금액을 모두 더하기가 맞습니다. 이때 쓰는 함수가 SUMIF입니다.

E4 셀에 입금합계 수식 넣기

먼저 청구내역의 E4 셀을 클릭합니다. E열은 입금합계를 표시할 열이고, 4행은 첫 번째 청구건입니다.

E4 셀에 아래 수식을 입력하세요.

=SUMIF($I$4:$I$10,C4,$K$4:$K$10)

수식을 풀어서 읽으면 이렇습니다. I4:I10 범위에서 C4의 청구번호와 같은 값을 찾고, 같은 행에 있는 K4:K10의 입금액을 모두 더하라는 뜻입니다.

여기서 달러 표시($)가 붙은 범위는 복사해도 움직이지 않게 고정한 것입니다. E4 수식을 E9까지 아래로 복사할 예정이므로, 입금내역 범위인 I4:I10과 K4:K10은 고정해야 합니다. 반대로 C4는 아래로 내려가면서 C5, C6처럼 바뀌어야 하므로 고정하지 않습니다.

수식을 입력한 뒤 E4 셀 오른쪽 아래의 작은 네모, 즉 채우기 핸들을 잡고 E9까지 아래로 끌어내리면 됩니다. 또는 E4 셀을 복사한 뒤 E5:E9 범위에 붙여넣어도 됩니다.

F4 셀에 완납·부분입금·미입금 표시하기

입금합계가 계산되면 이제 청구액과 비교해서 상태를 표시할 수 있습니다. 상태는 F4 셀부터 입력합니다.

업무상 보기 편하게 기준을 이렇게 잡아보겠습니다.

• 입금합계가 청구액 이상이면 완납
• 입금합계가 0이면 미입금
• 그 외에는 부분입금

F4 셀에 아래 수식을 입력합니다.

=IF(E4>=D4,"완납",IF(E4=0,"미입금","부분입금"))

IF 함수는 “만약 ~라면 A, 아니면 B”라고 판단하는 함수입니다. 위 수식은 먼저 E4의 입금합계가 D4의 청구액보다 크거나 같은지 확인합니다. 맞으면 완납이라고 표시합니다.

그 조건이 아니라면 다시 한 번 E4가 0인지 확인합니다. 0이면 미입금, 0도 아니고 청구액보다 작으면 부분입금이라고 표시합니다.

F4 수식도 F9까지 아래로 복사합니다. 이때 D4, E4는 행마다 D5, E5처럼 자동으로 바뀌어야 하므로 달러 표시를 붙이지 않는 것이 맞습니다.

G4 셀에 남은금액 계산하기

상태만 표시해도 좋지만, 실제 정산 업무에서는 “그래서 얼마가 남았는데?”가 바로 보여야 합니다. 그래서 G열에 남은금액을 계산해 두면 보고서가 훨씬 편해집니다.

G4 셀에 아래 수식을 입력합니다.

=IF(D4-E4<0,0,D4-E4)

기본 계산은 청구액에서 입금합계를 빼는 것입니다. 다만 간혹 착오로 더 많이 입금된 경우가 있을 수 있으므로, 결과가 마이너스가 되면 0으로 표시하도록 했습니다.

예를 들어 청구액이 260,000원이고 입금합계가 180,000원이면 남은금액은 80,000원입니다. 반대로 청구액보다 더 많이 들어온 경우에는 남은금액을 -10,000원처럼 표시하지 않고 0으로 둡니다. 과입금은 별도 확인이 필요하지만, 미수금 집계에서는 보통 남은금액을 0으로 보는 편이 안전합니다.

거래처별 남은금액도 바로 확인하기

이제 표 아래나 오른쪽에 간단한 확인 칸을 만들어 보겠습니다. 예를 들어 M3 셀에는 확인할 거래처명을 입력하고, N3 셀에는 해당 거래처의 남은금액 합계를 표시하겠습니다.

M3 셀에 라온문구라고 입력해 보세요. 그리고 N3 셀에 아래 수식을 넣습니다.

=SUMIFS($G$4:$G$9,$B$4:$B$9,M3)

SUMIFS는 조건에 맞는 금액만 더하는 함수입니다. 여기서는 G4:G9의 남은금액 중에서, B4:B9의 거래처명이 M3과 같은 행만 더합니다.

초보자분들이 SUMIF와 SUMIFS를 헷갈릴 수 있는데, 이번 예제에서는 이렇게 기억하면 쉽습니다. 조건이 하나여도 SUMIFS를 써도 됩니다. 나중에 월, 담당자, 상태 같은 조건을 더 붙이기 쉬워서 실무에서는 SUMIFS를 자주 씁니다.

미입금 건수 세기: COUNTIF로 상태 개수 확인

금액뿐 아니라 “미입금이 몇 건인지”도 자주 묻습니다. 이때는 COUNTIF를 쓰면 됩니다. COUNTIF는 조건에 맞는 셀 개수를 세는 함수입니다.

예를 들어 M4 셀에 미입금 건수라고 적고, N4 셀에 아래 수식을 넣어 보세요.

=COUNTIF($F$4:$F$9,"미입금")

부분입금까지 포함해서 아직 끝나지 않은 건수를 보고 싶다면 아래처럼 두 개를 더하면 됩니다.

=COUNTIF($F$4:$F$9,"미입금")+COUNTIF($F$4:$F$9,"부분입금")

COUNTIF는 숫자 합계가 아니라 개수를 세는 함수입니다. 그래서 “미입금 금액”을 구할 때 COUNTIF를 쓰면 안 되고, “미입금 건수”를 구할 때 사용해야 합니다. 금액은 SUMIF나 SUMIFS, 개수는 COUNTIF나 COUNTIFS로 구분해서 생각하면 헷갈림이 줄어듭니다.

결과가 이상할 때 먼저 확인할 것들

수식은 맞게 넣은 것 같은데 입금합계가 0으로 나오거나, 완납이어야 하는데 부분입금으로 보일 때가 있습니다. 이럴 때는 수식을 새로 만들기 전에 아래 순서로 확인하는 것이 빠릅니다.

청구번호가 정말 같은지 확인

SUMIF는 청구번호가 정확히 같아야 더합니다. 눈으로는 같아 보여도 한쪽에는 뒤에 빈칸이 붙어 있거나, 하이픈 모양이 다르거나, 앞뒤에 보이지 않는 공백이 있으면 다른 값으로 인식될 수 있습니다.

가장 쉬운 확인 방법은 청구내역의 C4 값을 복사해서, 입금내역 I열에서 찾기 단축키 Ctrl + F로 검색해 보는 것입니다. 검색이 안 되면 값이 실제로 다른 것입니다.

입금액이 숫자인지 확인

K열 입금액이 숫자처럼 보이지만 텍스트로 들어와 있으면 합계가 제대로 안 잡힐 수 있습니다. 보통 셀 왼쪽 위에 초록색 삼각형이 보이거나, 셀 안의 숫자가 왼쪽 정렬되어 있으면 의심해 볼 수 있습니다.

이럴 때는 K열을 선택한 뒤 오류 표시가 뜨면 숫자로 변환을 눌러 처리할 수 있습니다. 또는 빈 셀에 숫자 1을 입력해 복사한 다음, K열 금액 범위를 선택하고 선택하여 붙여넣기 > 곱하기를 사용하는 방법도 있습니다.

SUMIF 범위 크기가 같은지 확인

SUMIF에서 조건범위와 합계범위는 줄 수가 맞아야 합니다. 예를 들어 조건범위는 I4:I10인데 합계범위를 K4:K9로 잡으면 계산이 어긋날 수 있습니다.

이번 예제에서는 반드시 아래처럼 같은 행 범위를 잡아야 합니다.

=SUMIF($I$4:$I$10,C4,$K$4:$K$10)

I4:I10은 7칸, K4:K10도 7칸입니다. 범위를 직접 드래그할 때 한 줄이 덜 잡히는 실수가 생각보다 많으니, 수식 입력줄에서 범위를 한 번 더 확인해 보세요.

조금 더 실무답게 바꾸는 응용

이번 예제는 청구번호 하나를 기준으로 입금액을 합산하는 구조였습니다. 여기서 한 단계만 더 나가면 월별 미수금, 거래처별 미입금 건수, 담당자별 회수율 같은 보고서도 만들 수 있습니다.

예를 들어 A열 청구일을 기준으로 7월 청구분만 보고 싶다면 날짜 조건을 추가한 SUMIFS로 확장할 수 있습니다. 다만 처음부터 조건을 많이 붙이면 수식이 어려워지니, 먼저 오늘처럼 청구번호 기준 입금합계 → 상태 표시 → 남은금액 계산 흐름을 손에 익히는 것이 좋습니다.

입금 확인표는 거창한 기능보다 기준값을 정확히 잡는 것이 핵심입니다. 청구번호가 맞고, 금액이 숫자이며, 범위가 정확하다면 SUMIF와 IF만으로도 초보자가 바로 쓸 수 있는 정산표를 만들 수 있습니다.