엑셀 입금완료 표시가 안 될 때: SUMIF 0 나옴과 IF 미입금만 뜨는 초보 확인법

엑셀에서 입금완료 표시가 안 되고, SUMIF 결과가 계속 0으로 나오거나 IF 수식이 전부 미입금으로만 뜨는 경우가 있습니다. 분명 입금내역에는 같은 청구번호가 있는데 청구관리표에서는 입금액을 못 가져오는 상황이라 초보자 입장에서는 수식이 틀린 건지, 데이터가 틀린 건지 헷갈리기 쉽습니다.

이번 글은 실제로 청구관리 파일을 정리하다가 자주 만나는 문제를 기준으로 잡았습니다. 청구번호로 입금액을 합산하고, 청구금액과 비교해서 입금완료·부분입금·미입금을 자동 표시하는 흐름입니다. 어려운 기능보다 SUMIF, IF, COUNTIF만으로 원인을 좁혀가겠습니다.

분명 입금내역은 있는데 청구관리표에는 0원으로 보이는 상황

먼저 시트 구성을 정확히 잡아보겠습니다. 한 시트 안에 왼쪽에는 청구관리표, 오른쪽에는 입금내역이 있다고 가정하겠습니다. 청구관리표는 A열부터 F열까지 사용하고, 입금내역은 H열부터 J열까지 사용합니다.

청구관리표의 열 의미는 다음과 같습니다. A열은 청구번호, B열은 거래처, C열은 청구일, D열은 청구금액, E열은 입금합계, F열은 입금상태입니다. 실제 데이터는 2행부터 7행까지 들어 있다고 보겠습니다.

오른쪽 입금내역은 H열부터 J열까지입니다. H열은 입금일, I열은 청구번호, J열은 입금액입니다. 은행 입금내역이나 ERP 다운로드 자료를 붙여넣은 뒤 이 표를 기준으로 왼쪽 청구관리표에 입금합계를 가져오는 구조입니다.

여기서 눈여겨볼 부분은 마지막 행의 INV-1004 입니다. 화면으로 보면 거의 티가 나지 않지만 뒤에 공백이 하나 붙어 있습니다. 이런 작은 차이 때문에 SUMIF는 같은 청구번호라고 판단하지 못하고 0을 반환합니다.

E2에 SUMIF를 넣었는데 왜 0이 나오는지 먼저 확인합니다

청구관리표의 E2 셀에 아래 수식을 입력합니다. 이 수식은 입금내역의 I열에서 A2의 청구번호와 같은 값을 찾고, 같은 행의 J열 입금액을 모두 더합니다. 같은 청구번호로 여러 번 나누어 입금된 경우에도 합계가 계산됩니다.

=SUMIF($I$2:$I$100,A2,$J$2:$J$100)

여기서 $I$2:$I$100은 찾을 청구번호 범위입니다. A2는 현재 행의 청구번호입니다. $J$2:$J$100은 더할 입금액 범위입니다. E2에 입력한 뒤 E7까지 아래로 복사하면 각 청구번호별 입금합계가 들어와야 합니다.

그런데 E5, 즉 INV-1004의 입금합계가 0으로 나온다면 수식이 무조건 틀렸다고 보기 전에 먼저 일치 여부부터 확인해야 합니다. 초보자분들이 가장 많이 놓치는 부분이 바로 “보이는 글자”와 “엑셀이 실제로 비교하는 값”이 다를 수 있다는 점입니다.

COUNTIF로 같은 청구번호를 실제로 찾고 있는지 확인합니다

원인을 좁힐 때는 바로 수식을 고치기보다 확인용 칸을 하나 만드는 편이 좋습니다. 청구관리표의 G열을 임시 확인용으로 사용하겠습니다. G1에는 입금내역 존재여부라고 입력하고, G2 셀에 아래 수식을 넣습니다.

=COUNTIF($I$2:$I$100,A2)

이 수식은 입금내역 I2:I100 범위 안에 A2의 청구번호가 몇 번 있는지 세어줍니다. 결과가 1 이상이면 같은 청구번호를 찾았다는 뜻이고, 0이면 엑셀이 보기에는 같은 값이 없다는 뜻입니다.

예를 들어 INV-1004가 눈으로는 입금내역에 있는데 G5 결과가 0이라면, 대부분 청구번호 앞뒤 공백, 숨은 문자, 하이픈 모양 차이, 복사 과정에서 들어간 불필요한 글자 때문입니다. 이런 경우에는 입금액 수식보다 청구번호 정리가 먼저입니다.

입금내역 청구번호 뒤 공백 때문에 안 맞는 경우가 많습니다

입금내역 쪽에 공백이 섞여 있다면 바로 원본을 지워가며 고치기보다, 옆에 정리용 열을 하나 만들어 비교하는 것이 안전합니다. K열과 L열을 사용해 보겠습니다. K1에는 정리청구번호, L1에는 정리입금액이라고 입력합니다.

K2 셀에는 아래 수식을 입력합니다. TRIM은 글자 앞뒤의 불필요한 공백을 정리하는 함수입니다. 특히 다른 시스템에서 내려받은 자료를 붙여넣었을 때 유용합니다.

=TRIM(I2)

L2 셀에는 아래 수식을 입력합니다. 입금액이 숫자처럼 보이지만 실제로는 텍스트로 들어온 경우가 있기 때문에, *1을 해 숫자로 바꿔주는 방식입니다.

=J2*1

K2와 L2 수식을 각각 100행까지 아래로 복사합니다. 이제 청구관리표의 E2 수식을 정리된 범위 기준으로 바꿉니다. 기존에는 I열과 J열을 봤지만, 이제는 K열과 L열을 보게 하는 것입니다.

=SUMIF($K$2:$K$100,A2,$L$2:$L$100)

이렇게 바꾸면 입금내역의 청구번호 뒤에 공백이 붙어 있어도 INV-1004를 정상적으로 찾을 수 있습니다. E2에 입력한 뒤 E7까지 복사하면 입금합계가 다시 계산됩니다.

F2의 IF 수식은 입금합계가 잡힌 뒤에 넣어야 합니다

입금합계가 제대로 계산되면 이제 F열에 상태를 표시합니다. F2 셀에 아래 수식을 입력합니다. 이 수식은 E열 입금합계와 D열 청구금액을 비교해서 상태를 나눕니다.

=IF(E2>=D2,"입금완료",IF(E2=0,"미입금","부분입금"))

읽는 순서는 어렵지 않습니다. 먼저 E2 입금합계가 D2 청구금액보다 크거나 같으면 입금완료입니다. 그렇지 않은데 E2가 0이면 미입금입니다. 그 외에는 일부만 들어온 것이므로 부분입금입니다.

여기서 초보자분들이 자주 헷갈리는 부분이 있습니다. IF는 입금내역을 찾아주는 함수가 아닙니다. 이미 계산된 E열의 입금합계를 보고 문구만 판단합니다. 따라서 E열이 0으로 잘못 계산되어 있으면 F열도 당연히 미입금으로 나옵니다.

입금액이 숫자인지 아닌지도 같이 봐야 합니다

SUMIF가 청구번호는 찾았는데도 합계가 이상하다면 입금액이 숫자인지 확인해야 합니다. 숫자는 보통 셀 오른쪽에 붙고, 텍스트는 왼쪽에 붙는 경우가 많습니다. 물론 정렬만으로 100% 판단할 수는 없지만 초보자에게는 빠른 힌트가 됩니다.

J열 입금액 셀 왼쪽 위에 초록색 삼각형이 보이고, 셀을 클릭했을 때 오류 표시 메뉴에 숫자로 변환이 보인다면 텍스트 숫자일 가능성이 큽니다. 이때는 앞에서 만든 L열처럼 =J2*1로 숫자화한 열을 만들어 계산하는 방법이 안전합니다.

메뉴로 한 번에 바꾸고 싶다면 J2:J100 범위를 선택한 뒤, 상단 메뉴에서 데이터 탭을 누르고 텍스트 나누기를 선택한 다음 별도 설정 없이 마침을 눌러보세요. 숫자처럼 생긴 텍스트가 실제 숫자로 바뀌는 경우가 많습니다. 다만 원본이 바뀌므로 작업 전 파일을 복사해두는 편이 좋습니다.

범위를 복사할 때 달러 표시를 빼먹으면 다른 행에서 틀어집니다

E2 수식에서 $I$2:$I$100, $J$2:$J$100처럼 달러 표시가 붙은 부분은 고정 범위입니다. 수식을 아래로 복사해도 입금내역 범위가 움직이지 않게 하려는 목적입니다.

만약 아래처럼 달러 표시 없이 입력하면 E3, E4로 복사할 때 범위가 같이 내려가 버립니다. 그러면 위쪽 입금내역 일부를 놓치게 되어 합계가 틀어질 수 있습니다.

=SUMIF(I2:I100,A2,J2:J100)

초보자일수록 수식 자체보다 복사 후 범위가 움직였는지를 꼭 봐야 합니다. E3 셀을 클릭하고 수식 입력줄을 확인했을 때 찾을 범위가 I3:I101처럼 바뀌어 있다면 고정이 안 된 것입니다. 이럴 때는 E2 수식을 다시 고쳐 넣고 아래로 복사하세요.

실무에서는 청구금액보다 많이 입금된 경우도 따로 표시하면 좋습니다

간혹 수수료 포함, 선입금, 중복 입금 때문에 입금합계가 청구금액보다 큰 경우가 있습니다. 단순히 입금완료로만 표시하면 초과 입금을 놓칠 수 있습니다. 이럴 때는 F2 수식을 조금 더 나눠서 쓰면 됩니다.

=IF(E2>D2,"초과입금",IF(E2=D2,"입금완료",IF(E2=0,"미입금","부분입금")))

이 수식은 E2가 D2보다 크면 초과입금, 같으면 입금완료, 0이면 미입금, 나머지는 부분입금으로 표시합니다. 정산 업무에서는 이 방식이 더 안전합니다.

다만 처음 배우는 단계라면 먼저 기본 수식으로 결과가 맞는지 확인한 뒤, 초과입금 조건을 추가하는 순서가 좋습니다. 조건이 많아질수록 괄호 위치를 헷갈리기 쉽기 때문입니다.

결과가 이상할 때는 이 순서로 다시 보면 빨리 잡힙니다

입금완료 표시가 안 될 때는 F열의 IF부터 고치려고 하지 말고, E열 입금합계가 맞는지 먼저 봐야 합니다. 상태 문구는 합계를 따라가기 때문입니다.

확인 순서는 간단합니다. 첫째, E2의 SUMIF 범위가 맞는지 봅니다. 둘째, G열에 COUNTIF를 넣어 청구번호를 실제로 찾고 있는지 확인합니다. 셋째, 입금내역 청구번호에 공백이 있으면 K열에 TRIM으로 정리합니다. 넷째, 입금액이 텍스트 숫자이면 L열에서 *1로 숫자화합니다.

마지막으로 E열 수식 범위에 달러 표시가 들어갔는지 확인하세요. 수식은 맞는데 복사하면서 범위가 밀려서 틀어지는 일이 생각보다 많습니다. 이 체크만 습관으로 만들어도 입금관리표에서 SUMIF 0 나옴, IF 미입금만 뜸 같은 문제를 훨씬 빨리 해결할 수 있습니다.