엑셀 입금확인 미입금 표시 헷갈릴 때: 주문번호로 COUNTIF·SUMIF 대조하는 초보 공식

엑셀 입금확인 파일을 만들다 보면 주문번호는 분명히 있는데 미입금 표시가 계속 나오거나, COUNTIF 결과가 0으로 나오는 경우가 많습니다. 매출 주문서와 통장 입금내역을 따로 받아서 비교할 때 특히 자주 생기는 문제입니다.

처음에는 눈으로 하나씩 찾으면 될 것 같지만, 주문이 50건만 넘어가도 금방 헷갈립니다. 오늘은 초보자도 바로 따라 할 수 있게 주문번호로 입금여부 표시하기, 입금액 합계 대조하기, 금액이 안 맞는 행 찾기까지 기본 함수 COUNTIF, SUMIF, IF만 중심으로 정리해 보겠습니다.

매출 주문서와 입금내역이 따로 있을 때 생기는 흔한 상황

실무에서는 주문서 파일과 입금내역 파일이 따로 오는 경우가 많습니다. 예를 들어 쇼핑몰, 거래처 주문, 교육비 신청, 소액 정산 업무처럼 주문번호나 신청번호를 기준으로 입금 여부를 확인해야 하는 상황입니다.

이번 예시는 같은 시트 안에 두 개의 표가 있다고 가정하겠습니다. 왼쪽은 주문서이고, 오른쪽은 입금내역입니다. 실제 파일에서는 시트가 나뉘어 있어도 원리는 같습니다.

주문서의 제목 행은 3행, 실제 데이터는 A4:H9 범위에 입력되어 있다고 하겠습니다. 오른쪽 입금내역은 J3:L9에 있고, J열은 입금일, K열은 주문번호, L열은 입금액입니다.

입금내역은 아래처럼 들어왔다고 생각해 보겠습니다.

주문번호가 입금내역에 있으면 입금완료, 없으면 미입금

먼저 가장 많이 필요한 결과는 F열의 입금여부입니다. 주문서 A열의 주문번호가 입금내역 K열에 있으면 “입금완료”, 없으면 “미입금”이라고 표시하면 됩니다.

F4 셀을 클릭한 뒤 아래 수식을 입력하세요. 그리고 F9까지 아래로 복사합니다.

=IF(COUNTIF($K$4:$K$9,A4)>0,"입금완료","미입금")

여기서 COUNTIF는 “범위 안에서 조건과 같은 값이 몇 개 있는지 세는 함수”입니다. $K$4:$K$9는 입금내역의 주문번호 범위이고, A4는 지금 확인할 주문번호입니다.

COUNTIF 결과가 1 이상이면 입금내역에 주문번호가 있다는 뜻입니다. 그래서 IF 함수로 1개 이상이면 입금완료, 아니면 미입금이라고 나누는 구조입니다.

초보자가 헷갈리는 부분은 달러 표시입니다. $K$4:$K$9처럼 달러를 붙이면 수식을 아래로 복사해도 입금내역 범위가 움직이지 않습니다. 반대로 A4는 A5, A6처럼 내려가야 하므로 달러를 붙이지 않습니다.

입금완료인데 금액이 다를 수 있으니 입금액도 같이 가져오기

입금여부만 확인하면 놓치는 문제가 있습니다. 입금은 되었지만 금액이 다르게 들어온 경우입니다. 예시에서 SO-1005 주문은 주문금액이 32,000원인데 입금액은 30,000원입니다.

이런 행을 잡으려면 G열에 입금액을 표시해 두는 것이 좋습니다. G4 셀에 아래 수식을 입력하고 G9까지 복사합니다.

=SUMIF($K$4:$K$9,A4,$L$4:$L$9)

SUMIF는 “조건에 맞는 행의 금액을 더하는 함수”입니다. 입금내역 K열에서 A4 주문번호와 같은 행을 찾고, 그 행의 L열 입금액을 더합니다.

왜 VLOOKUP이 아니라 SUMIF를 썼을까요? 입금내역에 같은 주문번호가 두 번 들어오는 경우가 있기 때문입니다. 예를 들어 계약금과 잔금처럼 나눠 입금되면 같은 주문번호가 2행으로 나뉠 수 있습니다. 이때 SUMIF는 해당 주문번호의 입금액을 모두 합쳐 주므로 정산표에서 더 안전합니다.

주문금액과 입금액 차이를 H열에서 바로 확인하기

이제 H열에서 주문금액과 입금액의 차이를 계산해 보겠습니다. H4 셀에 아래 수식을 입력하고 H9까지 복사합니다.

=E4-G4

결과가 0이면 주문금액과 입금액이 같습니다. 양수이면 아직 덜 입금된 금액이 있다는 뜻이고, 음수이면 주문금액보다 더 입금된 상황입니다.

예시에서는 SO-1003은 입금내역이 없으므로 G열이 0으로 나오고, H열은 39,000이 됩니다. SO-1005는 2,000이 나오므로 입금은 되었지만 2,000원이 부족한 상태입니다.

보고서용 상태 문구를 조금 더 친절하게 만들기

상사나 다른 부서에 공유할 정산표라면 단순히 입금완료, 미입금만으로는 부족할 수 있습니다. “금액불일치”까지 한 번에 표시하면 확인하기 훨씬 편합니다.

F4 셀의 수식을 아래처럼 바꿔도 됩니다. 이 수식은 입금액이 0이면 미입금, 주문금액과 입금액이 같으면 입금완료, 그 외에는 금액불일치로 표시합니다.

=IF(G4=0,"미입금",IF(E4=G4,"입금완료","금액불일치"))

이 수식을 쓰려면 먼저 G열에 SUMIF로 입금액을 계산해 두어야 합니다. 함수가 길어 보이지만 순서는 간단합니다. 먼저 입금액이 0인지 보고, 0이 아니면 주문금액과 같은지 한 번 더 확인하는 구조입니다.

COUNTIF가 0으로 나올 때 먼저 볼 곳

입금내역에 주문번호가 있는데도 COUNTIF가 0으로 나오면 대부분 값 모양이 미묘하게 다릅니다. 눈으로는 같아 보여도 엑셀은 다르게 판단할 수 있습니다.

가장 흔한 원인은 주문번호 앞뒤에 공백이 들어간 경우입니다. 예를 들어 A4에는 SO-1001이 있고, K4에는 SO-1001 처럼 뒤에 빈칸이 있으면 서로 다른 값으로 봅니다. 이럴 때는 해당 셀을 클릭한 뒤 수식 입력줄에서 커서를 끝으로 보내 공백이 있는지 확인해 보세요.

두 번째는 하이픈 모양이 다른 경우입니다. 복사해 온 자료에서 일반 하이픈(-)이 아니라 긴 대시처럼 보이는 문자가 들어가면 COUNTIF가 찾지 못합니다. 주문번호는 가능하면 한 파일에서 복사해 붙여넣는 방식으로 맞추는 것이 안전합니다.

세 번째는 범위를 잘못 잡은 경우입니다. 수식을 F4에 입력했는데 COUNTIF 범위가 $K$5:$K$9처럼 첫 입금내역 행을 빼먹으면 첫 행의 주문번호는 찾지 못합니다. 제목 행이 3행이면 실제 데이터는 4행부터라는 점을 꼭 확인하세요.

주문번호가 숫자처럼 생겼다면 TEXT로 자리수를 맞추기

주문번호가 SO-1001처럼 문자와 숫자가 섞여 있으면 비교적 안전합니다. 하지만 주문번호가 000123처럼 숫자만 있고 앞자리에 0이 붙는 형식이면 문제가 자주 생깁니다.

예를 들어 주문서 A4에는 000123으로 보이는데, 입금내역 K4에는 123으로 들어오면 COUNTIF가 0이 됩니다. 이때는 양쪽 주문번호 형식을 같은 자리수의 문자로 맞춰야 합니다.

주문서의 주문번호를 6자리 문자로 맞춘 보조열을 만들고 싶다면, 예를 들어 I4 셀에 아래처럼 입력할 수 있습니다.

=TEXT(A4,"000000")

입금내역 쪽도 같은 방식으로 M4 셀에 아래 수식을 넣습니다.

=TEXT(K4,"000000")

그다음 COUNTIF 범위를 K열이 아니라 M열 보조열로 바꾸면 됩니다. 숫자 주문번호는 앞자리 0이 사라지는 일이 많기 때문에, 대조용 파일에서는 처음부터 텍스트 형식으로 관리하는 습관이 좋습니다.

거래처별 미입금 총액까지 보고 싶을 때

입금 확인표를 만들다 보면 “어느 거래처가 얼마를 아직 안 냈는지”도 같이 물어봅니다. 이때는 H열의 차이를 이용해서 거래처별 미입금액을 합산하면 됩니다.

예를 들어 K12 셀에 확인할 거래처명으로 가람상사를 입력하고, L12 셀에 미입금 총액을 표시한다고 하겠습니다. 주문서의 거래처는 C4:C9, 차이는 H4:H9에 있습니다.

=SUMIF($C$4:$C$9,K12,$H$4:$H$9)

이 수식은 C열에서 K12의 거래처명을 찾고, 해당 행의 H열 차이 금액을 더합니다. 다만 H열에 음수, 즉 과입금 금액이 섞여 있으면 합계가 줄어들 수 있습니다. 초보 단계에서는 먼저 H열에서 0이 아닌 행을 필터로 확인한 뒤 거래처별 집계를 보는 편이 안전합니다.

실제 파일에서 적용하기 전 확인하면 좋은 순서

먼저 주문서와 입금내역의 기준값을 정해야 합니다. 거래처명만으로 대조하면 같은 거래처가 하루에 여러 번 주문했을 때 헷갈립니다. 가능하면 주문번호, 신청번호, 청구번호처럼 한 건을 정확히 구분하는 값을 기준으로 쓰세요.

다음으로 두 표의 시작 행을 확인합니다. 예시에서는 주문서가 A4부터, 입금내역이 J4부터 시작했습니다. 내 파일에서 데이터가 2행부터 시작한다면 수식의 범위도 그에 맞게 바꿔야 합니다.

마지막으로 결과 셀을 나눠서 만드세요. F열에는 상태, G열에는 입금액, H열에는 차이를 따로 두면 어디에서 문제가 생겼는지 훨씬 빨리 찾을 수 있습니다. 처음부터 한 셀에 모든 판단을 넣으면 수식이 길어지고, 초보자는 오류 원인을 찾기 어렵습니다.

입금확인표는 어려운 기능보다 기준값을 정확히 맞추는 것이 핵심입니다. 주문번호 범위만 제대로 고정하고, 입금액은 SUMIF로 가져온 뒤, 차이 금액을 확인하는 흐름만 잡아도 대부분의 미입금 정산 업무는 훨씬 깔끔하게 정리됩니다.