엑셀 COUNTIF 0 나옴? 완료 개수가 안 세질 때 공백부터 확인하세요

엑셀에서 COUNTIF 0 나옴, 완료 개수 안 세짐, 조건은 맞는데 결과가 이상함 같은 문제를 처음 만나면 수식이 틀렸다고 생각하기 쉽습니다. 그런데 실무 파일에서는 수식보다 먼저 봐야 할 것이 있습니다. 바로 셀 안에 숨어 있는 공백입니다.

특히 쇼핑몰 주문 내역, 상담 처리 현황, 택배 출고 리스트처럼 다른 시스템에서 내려받은 엑셀 파일은 눈으로는 똑같이 완료로 보이는데 실제 값은 완료 뒤에 공백이 붙은 값인 경우가 많습니다. 이때 COUNTIF는 사람이 보는 글자가 아니라 셀 안의 실제 값을 비교하기 때문에 결과가 0으로 나오거나 예상보다 적게 나옵니다.

문제 상황: 완료가 분명히 있는데 COUNTIF 결과가 0으로 나오는 경우

아래와 같은 처리 현황표가 있다고 해보겠습니다. 시트 이름은 처리현황이고, 원본 데이터는 A4:E12 범위에 있습니다. A열은 접수일, B열은 주문번호, C열은 고객명, D열은 처리상태, E열은 금액입니다.

우리는 G2 셀에 세고 싶은 상태값인 완료를 입력하고, H2 셀에 완료 건수를 표시하려고 합니다.

겉으로 보면 D열에 완료가 여러 개 있습니다. 그래서 H2 셀에 아래 수식을 입력합니다.

=COUNTIF(D5:D12,G2)

COUNTIF는 범위에서 조건에 맞는 셀 개수를 세는 함수입니다. 위 수식은 D5부터 D12까지 처리상태 범위에서 G2에 입력된 값, 즉 완료와 같은 셀이 몇 개인지 세라는 뜻입니다.

그런데 결과가 4가 아니라 0으로 나오거나, 분명히 완료가 4개인데 1개나 2개만 세어지는 일이 생깁니다. 이럴 때 가장 흔한 원인이 D열 값 또는 G2 조건 셀에 눈에 안 보이는 공백이 들어간 것입니다.

원인: 엑셀은 ‘완료’와 ‘완료 ’를 다른 값으로 봅니다

초보자분들이 가장 많이 헷갈리는 부분이 여기입니다. 화면에서는 둘 다 완료로 보일 수 있지만, 엑셀 입장에서는 아래 두 값이 서로 다릅니다.

COUNTIF는 글자의 의미를 이해해서 세는 함수가 아닙니다. 셀 안의 값이 조건과 정확히 같은지를 확인합니다. 그래서 G2에는 완료가 들어 있고 D5에는 완료 뒤에 공백이 붙어 있다면 서로 다르다고 판단합니다.

이 문제는 특히 상태값, 거래처명, 담당자명, 상품코드에서 자주 나옵니다. 사람이 직접 입력한 표보다, 프로그램에서 내려받은 파일이나 복사해서 붙여넣은 표에서 더 자주 생깁니다.

해결 흐름: 원본 옆에 정리용 열을 만들고 그 열로 COUNTIF 하기

초보자에게 가장 안전한 방법은 원본 D열을 바로 고치기보다, 옆에 정리상태 열을 하나 만드는 것입니다. 원본을 남겨두면 나중에 어디서 문제가 생겼는지 비교하기 쉽고, 실수로 원본 데이터를 망가뜨릴 가능성도 줄어듭니다.

먼저 F4 셀에 열 제목으로 정리상태라고 입력합니다. 그리고 F5 셀에 아래 수식을 입력합니다.

=TRIM(D5)

TRIM 함수는 셀 값의 앞뒤 불필요한 공백을 정리해 주는 함수입니다. D5 셀에 완료 뒤 공백이 붙어 있어도 F5에는 정리된 완료가 표시됩니다.

이제 F5 셀의 오른쪽 아래 모서리에 마우스를 올리면 작은 십자가 모양이 보입니다. 그 상태에서 아래로 드래그해 F12 셀까지 수식을 복사합니다. 그러면 F5:F12 범위에 정리된 처리상태가 만들어집니다.

그다음 H2 셀의 기존 수식을 아래처럼 바꿉니다.

=COUNTIF(F5:F12,TRIM(G2))

여기서 중요한 점은 COUNTIF의 범위를 원본 D열이 아니라 정리된 F열로 바꾼다는 것입니다. 그리고 조건 셀인 G2에도 혹시 모를 공백이 들어갈 수 있으므로 TRIM(G2)로 한 번 더 정리해 줍니다.

이 수식을 사용하면 D열에 앞뒤 공백이 섞여 있어도 F열에서 정리된 값끼리 비교하므로 완료 건수가 제대로 계산됩니다.

와일드카드로 쉽게 해결하려다 더 틀릴 수 있습니다

검색하다 보면 아래와 같은 수식을 볼 수 있습니다.

=COUNTIF(D5:D12,"*"&G2&"*")

별표(*)는 앞뒤에 다른 글자가 있어도 포함해서 찾겠다는 뜻입니다. 겉으로 보면 편해 보입니다. D열 값이 완료 뒤에 공백이 붙어 있어도 완료라는 글자가 포함되어 있으니 세어질 가능성이 큽니다.

하지만 처리상태에서는 이 방식이 위험합니다. 왜냐하면 미완료에도 완료라는 글자가 들어 있기 때문입니다. G2가 완료일 때 위 수식을 쓰면 미완료까지 같이 세어질 수 있습니다.

그래서 상태값처럼 정확히 구분해야 하는 데이터는 별표로 대충 포함 검색을 하기보다, TRIM으로 공백을 정리한 뒤 COUNTIF로 정확히 세는 방식이 더 안전합니다.

검증 방법: 글자 수와 필터로 눈에 안 보이는 차이를 확인하기

수식이 맞는지 확인할 때는 결과만 보지 말고, 왜 틀렸는지도 같이 확인하는 습관이 좋습니다. 이번 문제에서는 글자 수를 확인하면 원인을 빨리 찾을 수 있습니다.

G4 셀에 글자수라고 입력하고, G5 셀에 아래 수식을 입력해 보세요.

=LEN(D5)

LEN 함수는 셀 안의 글자 수를 세는 함수입니다. 완료는 한글 두 글자이므로 정상이라면 결과가 2가 나와야 합니다. 그런데 3이 나오면 완료 뒤나 앞에 공백이 하나 붙어 있다는 뜻입니다.

G5 수식을 G12까지 복사한 뒤, D열 처리상태가 완료처럼 보이는 행의 글자 수를 확인해 보세요. 어떤 행은 2, 어떤 행은 3으로 나온다면 바로 그 부분 때문에 COUNTIF 결과가 틀어진 것입니다.

필터로도 확인할 수 있습니다. 표 안 아무 셀이나 클릭한 뒤, 메뉴에서 데이터 탭 > 필터를 누릅니다. D열 처리상태 필터 화살표를 열어 보면 완료가 하나로 보이지 않고 비슷한 항목이 여러 개로 나뉘어 보일 때가 있습니다. 이 경우도 공백이나 보이지 않는 문자가 섞였을 가능성이 큽니다.

원본을 직접 고치고 싶다면 선택 범위를 먼저 제한하세요

정리용 열을 계속 두기 싫고 원본 D열 자체를 고치고 싶다면, 반드시 D5:D12 범위만 선택한 상태에서 작업하는 것이 좋습니다. 전체 시트에서 공백을 바꾸면 고객명, 주소, 상품명처럼 중간 공백이 필요한 값까지 망가질 수 있습니다.

간단한 공백 제거는 메뉴에서도 할 수 있습니다. 먼저 D5:D12를 선택합니다. 그다음 홈 탭 > 찾기 및 선택 > 바꾸기를 클릭합니다. 단축키는 Ctrl + H입니다.

찾을 내용 칸에 스페이스바를 한 번 눌러 공백을 넣고, 바꿀 내용 칸은 비워 둔 뒤 모두 바꾸기를 누릅니다. 다만 이 방법은 선택한 범위 안의 모든 공백을 없애므로, 처리상태처럼 값이 짧고 공백이 필요 없는 열에서만 사용하는 것이 안전합니다.

처리상태가 결제 완료, 출고 대기처럼 중간 공백이 의미 있는 값이라면 이 방법을 쓰면 안 됩니다. 그때는 원본을 직접 바꾸기보다 TRIM을 사용한 정리용 열 방식이 더 안전합니다.

응용: 담당자별 완료 건수는 COUNTIFS로 세면 됩니다

실무에서는 전체 완료 건수만 세는 것보다 담당자별 완료 건수를 확인하는 경우가 많습니다. 예를 들어 I2 셀에 담당자명을 입력하고, J2 셀에 해당 담당자의 완료 건수를 표시한다고 해보겠습니다.

현재 예시 표에서는 C열이 고객명이라 담당자 열이 없지만, 실제 파일에서 C열이 담당자명이라고 가정하면 아래처럼 COUNTIFS를 사용할 수 있습니다. COUNTIFS는 여러 조건을 동시에 만족하는 행의 개수를 세는 함수입니다.

=COUNTIFS(C5:C12,I2,F5:F12,TRIM(G2))

이 수식은 C5:C12에서 I2 담당자명과 같은 행을 찾고, 동시에 F5:F12 정리상태가 G2의 완료와 같은 행만 셉니다. 조건이 두 개 이상이면 COUNTIF가 아니라 COUNTIFS를 사용한다고 기억하면 됩니다.

금액까지 합산하고 싶다면 SUMIFS를 사용합니다. 예를 들어 완료된 주문의 금액 합계를 K2 셀에 구하고 싶다면 아래처럼 입력합니다.

=SUMIFS(E5:E12,F5:F12,TRIM(G2))

SUMIFS는 조건에 맞는 행의 금액을 합계 내는 함수입니다. 여기서는 E5:E12 금액 범위에서, F5:F12 정리상태가 완료인 행만 합산합니다.

실무에서 적용할 때 볼 것

COUNTIF 결과가 0으로 나오면 수식을 복잡하게 바꾸기 전에 먼저 세 가지를 확인해 보세요. 조건 셀 주소가 맞는지, 범위가 실제 데이터 행을 포함하는지, 그리고 값 앞뒤에 공백이 숨어 있는지입니다.

초보자에게는 정리용 열을 하나 만들어 확인하는 방식이 가장 안전합니다. 원본 D열을 바로 고치지 않고 F열에서 TRIM으로 정리한 뒤 COUNTIF를 걸면, 실수했을 때도 원본과 비교하면서 되돌리기 쉽습니다.

특히 완료와 미완료처럼 글자가 서로 포함되는 상태값은 별표를 이용한 포함 검색을 조심해야 합니다. 엑셀은 눈에 보이는 모양보다 셀 안의 실제 값을 기준으로 계산한다는 점만 기억해도, COUNTIF 0 나옴 문제의 절반은 빠르게 해결할 수 있습니다.