엑셀 SUMIF 결과가 0 나올 때 — 3가지 원인과 해결법

엑셀에서 SUMIF 결과가 0이면 수식을 무작정 바꾸기보다 조건이 몇 행과 일치했는지와 합계 열이 실제 숫자인지를 따로 확인해야 합니다. 화면에는 같은 이름과 금액으로 보여도 뒤쪽 공백, 텍스트 형식 숫자, 어긋난 범위 때문에 계산 대상에서 빠질 수 있습니다. 이 글은 작은 가상 표로 증상을 재현한 뒤 COUNTIF, LEN, ISNUMBER, ROWS를 차례로 써서 원인을 한 가지씩 좁히는 방법을 설명합니다.

아래 이름과 금액은 수식 동작을 확인하기 위한 가상 입력값입니다. 실제 매출이나 특정 조직의 처리 결과가 아닙니다.

관찰 가능한 증상

예를 들어 F2 셀에 김대리를 입력하고 다음 수식을 사용했다고 가정합니다.

주제 개요와 전체 흐름

=SUMIF(A2:A6,F2,B2:B6)

조건 열에 김대리가 보이는데도 결과가 0이거나 예상 합계보다 작다면 먼저 오류 메시지와 계산 결과를 구분합니다. #VALUE!처럼 오류가 표시되는 경우와 숫자 0이 표시되는 경우는 진단 출발점이 다릅니다. 여기서는 수식이 계산되지만 결과가 0 또는 예상보다 작은 상황만 다룹니다.

다음과 같은 징후가 있으면 이 절차를 적용할 수 있습니다.

• COUNTIF로 센 일치 건수부터 예상과 다릅니다.
• 이름 셀을 편집 모드로 열었을 때 앞뒤 공백이 보입니다.
• 금액 셀 왼쪽 위에 숫자 저장 방식 경고가 있거나 ISNUMBER 결과가 거짓입니다.
• 조건 범위와 합계 범위의 시작 행 또는 끝 행이 다릅니다.
• 원본을 붙여 넣은 뒤부터 같은 수식의 결과가 달라졌습니다.

가상 입력표로 문제 재현하기

빈 시트의 A1:B6에 아래 값을 입력합니다. A4의 김대리 뒤에는 일반 공백 하나가 있고, B5의 50000은 앞에 작은따옴표를 붙여 텍스트로 저장합니다.

단계별 실행 절차

F2에는 김대리를 입력합니다. 원래 수식은 A2와 A5의 조건을 찾지만 B5가 텍스트이므로 숫자 120000만 합계에 반영됩니다. A4는 눈으로 비슷해 보여도 뒤 공백 때문에 조건과 일치하지 않습니다. 이 예제에서 정리 후 기대하는 합계는 120000, 70000, 50000을 더한 240000입니다.

원인 가설 세 가지

조건 텍스트가 실제로 다름

점검 체크리스트

Excel은 화면에서 비슷하게 보이는 문자열도 저장된 문자가 다르면 다른 조건으로 취급합니다. 일반 공백뿐 아니라 웹이나 외부 시스템에서 복사한 줄바꿈·탭·줄 바꿈 없는 공백이 섞일 수 있습니다. TRIM만 먼저 적용해 보고 해결되지 않으면 어떤 문자가 남았는지 길이와 문자 코드를 추가로 확인해야 합니다.

합계 열이 텍스트임

셀에 50000이 보인다는 사실만으로 숫자라고 단정할 수 없습니다. 작은따옴표가 붙었거나 가져오기 과정에서 텍스트 형식이 되면 조건은 찾아도 해당 값은 합계에서 빠질 수 있습니다. 표시 형식을 숫자로 바꾸는 것만으로 저장된 값의 형식이 변하지 않는 경우도 있으므로 ISNUMBER 또는 TYPE으로 확인합니다.

범위의 위치와 크기가 다름

range와 sum_range는 같은 시작 행과 같은 크기로 두는 편이 안전합니다. Microsoft 공식 설명도 합계 범위의 크기와 모양을 조건 범위와 맞추도록 안내합니다. 크기가 다르다고 언제나 0이 되는 것은 아니므로 이것만 원인이라고 단정하지 말고, 먼저 참조 범위를 나란히 비교합니다.

진단 순서

1. 조건 일치 건수를 셉니다. 빈 셀에 =COUNTIF(A2:A6,F2)를 입력합니다. 가상 표의 첫 결과는 2입니다. 기대한 건수보다 작으면 합계 열보다 조건 열을 먼저 조사합니다.

2. 의심 셀의 길이를 비교합니다. 원본 A4를 그대로 두고 빈 H2에 =LEN(A4), 빈 I2에 =LEN(TRIM(A4))를 입력합니다. 이 예제의 기대값은 각각 4와 3입니다. 원본 A4에 진단 수식을 넣으면 원본이 사라지고 순환 참조도 생길 수 있습니다. 두 길이가 같아도 문제가 남으면 줄 바꿈 없는 공백처럼 TRIM이 바로 지우지 못하는 문자를 확인합니다.

3. 합계 값의 형식을 검사합니다. 원본 B2와 B5는 수정하지 않습니다. 빈 H3에 =ISNUMBER(B2), 빈 I3에 =ISNUMBER(B5), 빈 J3에 =TYPE(B5)를 입력합니다. 기대값은 차례로 TRUE, FALSE, 2이며 B5는 숫자처럼 보이는 텍스트입니다.

4. 참조 행 수를 맞춥니다. =ROWS(A2:A6)과 =ROWS(B2:B6)의 결과가 모두 5인지 확인합니다. 수식 표시줄에서도 두 범위의 시작 행과 끝 행이 같은지 봅니다.

5. 원본 수식의 중간 결과를 기록합니다. COUNTIF 결과, 텍스트로 판정된 금액 셀, 범위 행 수를 메모한 뒤 한 원인씩만 수정합니다. 여러 변환을 한꺼번에 적용하면 어느 수정이 효과가 있었는지 알기 어렵습니다.

이 순서를 지키면 조건이 0건인 상황, 조건은 찾았지만 합계 값이 텍스트인 상황, 범위가 어긋난 상황을 분리할 수 있습니다.

수정 단계

원본 열을 바로 덮어쓰지 말고 C열과 D열을 진단용 보조열로 사용합니다.

1. C1에 정리된 담당자, D1에 숫자 금액을 입력합니다.

2. C2에 다음 수식을 넣고 C6까지 채웁니다. SUBSTITUTE가 UNICHAR(160)으로 지정한 U+00A0 공백을 일반 공백으로 바꾼 뒤 CLEAN과 TRIM으로 정리합니다. UNICHAR는 유니코드 번호를 사용합니다. CHAR는 컴퓨터의 문자 집합을 사용하므로 같은 숫자가 모든 환경에서 같은 문자를 뜻한다고 가정하지 않습니다. 사용 중인 Excel에서 UNICHAR 지원 여부를 확인하세요.

=TRIM(CLEAN(SUBSTITUTE(A2,UNICHAR(160)," ")))

3. D2에는 다음 수식을 넣고 D6까지 채웁니다. 쉼표가 포함된 텍스트 숫자를 정리한 뒤 변환하며, 변환할 수 없는 값은 빈칸으로 남겨 사람이 확인할 수 있게 합니다.

=IFERROR(VALUE(SUBSTITUTE(B2,",","")),"")

금액 변환의 전제: 이 수식은 쉼표가 천 단위 구분자임을 이미 확인한 입력에만 사용합니다. 쉼표를 무조건 지우므로 12,3도 123으로 바뀌며, 외국식 소수 구분자나 잘못 찍힌 쉼표를 판별하지 못합니다. 애매한 값은 변환 전에 별도 확인하고, 변환 실패를 빈칸으로 만든 행은 아래 COUNT 검산으로 찾아 원문과 대조하세요. 이 수식 자체가 금액의 타당성을 검증하는 것은 아닙니다.

4. 정리된 두 열을 대상으로 합계를 계산합니다.

=SUMIF(C2:C6,F2,D2:D6)

5. 결과가 240000인지 확인한 뒤에만 실제 파일에 같은 보조열 방식을 적용합니다. 통과 후에는 수식을 값으로 붙여 넣을지, 원본 가져오기 단계에서 정리할지 운영 방식에 맞춰 결정합니다.

=SUMIF(A:A,TRIM(F2),B:B)처럼 조건 셀만 TRIM하는 수식은 A열 각 셀의 숨은 공백을 지우지 못합니다. VALUE(B:B)를 SUMIF의 합계 범위 자리에 바로 넣는 방식은 버전과 계산 방식에 따라 의도대로 동작하지 않을 수 있으므로, 확인 가능한 보조열을 사용하는 편이 안전합니다.

확인 대상·방법·합격 값

SUMIF와 SUMPRODUCT가 같은 값을 내고, 정리 후 조건 건수가 예상 행 수와 같을 때 이 예제는 통과입니다. 실제 파일에서는 240000이라는 숫자를 그대로 기대값으로 쓰지 말고, 원본 증빙이나 별도 집계로 계산한 자신의 기대 합계를 적습니다.

결과가 여전히 0일 때의 분기

조건 건수가 여전히 0이면 F2의 값과 C열 값을 =EXACT(C2,F2)로 한 행씩 비교합니다. 대소문자 구분이 필요한 문제라면 SUMIF만으로 해결하려 하지 말고 데이터 정규화 규칙부터 정합니다. 날짜나 코드가 숫자와 텍스트로 섞였다면 담당자명 문제와 별도로 형식을 통일합니다.

조건 건수는 정상인데 합계만 0이면 D열에서 ISNUMBER가 거짓인 행과 빈칸인 행을 필터링합니다. 음수와 양수가 상쇄되어 0이 된 경우도 있으므로 =SUM(D2:D6)과 각 조건 행을 함께 봅니다. 범위를 수정한 뒤 결과가 달라졌다면 수식의 시작 셀과 끝 셀을 기록해 어떤 행이 새로 포함됐는지 확인합니다.

실패와 복구·롤백

1. 원본 시트를 복제하거나 파일을 다른 이름으로 저장한 뒤 수정합니다.

2. 보조열 변환 결과에 빈칸이 생기면 원본 값을 덮어쓰지 말고 해당 행만 변환 실패로 표시합니다.

3. 기대 합계와 독립 검산이 다르면 새 수식을 배포하지 않고 마지막 정상 파일로 되돌립니다.

4. 잘못 바꾼 범위는 수식 기록에서 이전 A·B 범위를 복원한 뒤 ROWS부터 다시 확인합니다.

5. 문제가 해결되면 원인, 수정 수식, 검산값을 한 줄로 남겨 다음 가져오기 때 같은 정리 규칙을 재사용합니다.

보조열을 쓰면 원본을 보존한 채 수정 결과를 비교할 수 있습니다. 값 붙여넣기나 전체 바꾸기를 먼저 실행하면 숨은 문자가 사라져 원인 확인이 어려워질 수 있으므로 검산이 끝날 때까지 원본 열을 유지합니다.

다른 도구로 넘길 경계

한 파일의 몇 행만 문제라면 보조열로 충분합니다. 그러나 매주 같은 외부 시스템에서 수천 행을 가져오고 공백 종류·숫자 구분자가 계속 달라진다면 수식을 계속 추가하기보다 Power Query나 원본 내보내기 규칙에서 형식을 정리하는 편이 관리하기 쉽습니다. 여러 조건을 동시에 합산해야 한다면 SUMIFS, 행별 복잡한 변환이 필요하다면 정리 테이블을 먼저 만든 뒤 집계합니다.

담당자 코드에 보이지 않는 문자가 반복되거나 원본 파일의 문자 인코딩을 알 수 없다면 파일 제공자에게 데이터 사전을 요청합니다. 재무 보고처럼 합계 오류의 영향이 큰 파일은 수식 결과만 믿지 말고 원장 또는 승인된 집계와 대조한 뒤 사용합니다.

적용 한계

이 글의 예제는 텍스트 조건 하나와 금액 열 하나를 다룹니다. 와일드카드 조건, 날짜 구간, 여러 조건, 오류 값이 섞인 배열은 별도 진단이 필요합니다. TRIM, CLEAN, UNICHAR(160) 조합도 모든 유니코드 공백을 자동으로 제거하지는 않습니다. 정리 수식으로 해결되지 않는 문자는 찾기·바꾸기나 가져오기 단계에서 실제 문자 코드를 확인합니다.

전체 열 참조는 간단하지만 큰 통합 문서에서는 불필요한 셀까지 계산할 수 있으므로 예제처럼 실제 데이터 범위를 명확히 두는 편이 추적하기 쉽습니다. 무엇보다 0이 나온다는 결과만 보고 한 원인으로 단정하지 말고 조건 건수, 값 형식, 범위를 따로 기록해야 합니다.

공식 자료

• SUMIF 함수 – Microsoft 지원: 함수 구문과 range, sum_range의 대응 방식을 확인할 수 있습니다.
• Microsoft Excel에서 COUNTIF 함수 사용 – Microsoft 지원: 조건과 일치하는 셀 개수를 세는 기본 사용법입니다.
• 텍스트로 저장된 숫자를 Excel 숫자로 변환 – Microsoft 지원: 텍스트 숫자 확인과 변환 방법을 설명합니다.

자료 확인일: 2026-09-02

바로 실행할 최소 점검

실제 파일을 바꾸기 전에 문제 행 다섯 개만 복사하고 COUNTIF, ISNUMBER, ROWS 결과를 적어 보세요. 조건 건수, 숫자 형식, 범위 가운데 어느 지점에서 기대와 달라지는지 확인한 뒤 해당 단계만 수정하면 불필요한 전체 재작성을 피할 수 있습니다.

메뉴 이름과 옵션은 프로그램 버전마다 다를 수 있으니, 적용 전에 공식 문서나 실제 화면에서 한 번 더 확인하세요.

함께 읽으면 좋은 글

• 엑셀 SPILL! 오류 — 6가지 원인과 해결 절차 확인
• CHOOSE 함수 활용법 — 번호로 값을 고르는 실무 패턴 5가지

작성자 소개

최지원

엑셀·VBA·파이썬·AI 업무 자동화의 사용 절차와 실습 자료를 정리합니다.

WP 게시물 작성자의 글 보기