엑셀 SUMIF 0 나옴? 거래처명 공백 때문에 조건별 합계 안 맞을 때 초보 해결법

엑셀에서 SUMIF 0 나옴, COUNTIF 안됨 같은 문제를 검색하게 되는 순간이 있습니다. 분명히 거래처명이 같은데 합계가 덜 나오고, 필터로 보면 데이터도 있는데 수식 결과만 이상한 경우입니다. 초보자 입장에서는 함수가 틀린 것처럼 보이지만, 실제 원인은 거래처명 앞뒤 공백이나 줄바꿈인 경우가 정말 많습니다.

특히 ERP나 쇼핑몰 관리자 화면에서 엑셀로 내려받은 파일은 눈으로 보기에는 같은 이름이어도 셀 안에 보이지 않는 빈칸이 붙어 있는 일이 있습니다. 오늘은 작은 주문표를 기준으로, 어디 셀에 무엇을 입력해야 하는지부터 차근차근 정리해 보겠습니다.

실무에서 자주 터지는 실수: 거래처명은 같은데 SUMIF 합계가 덜 나옴

아래처럼 주문 내역이 있다고 해보겠습니다. A열은 주문일, B열은 거래처, C열은 상품코드, D열은 수량, E열은 단가, F열은 금액입니다. 데이터는 1행에 제목이 있고, 실제 주문 데이터는 A2:F8 범위에 들어 있습니다.

표에서 B4의 ␠ 표시는 뒤에 공백이 있다는 뜻이고, B7의 ↵ 표시는 셀 안에 줄바꿈이 들어 있다는 뜻입니다. 실제 엑셀 화면에서는 이런 표시가 보이지 않을 수 있습니다. 그래서 더 헷갈립니다.

H2 셀에는 찾을 거래처명으로 한빛상사를 입력합니다. I2 셀에는 일반적인 SUMIF 수식을 넣어 보겠습니다.

=SUMIF($B$2:$B$8,H2,$F$2:$F$8)

이 수식의 뜻은 B2:B8 범위에서 H2와 같은 거래처를 찾고, 같은 행의 F2:F8 금액을 더하라는 의미입니다. 그런데 결과가 70,000이 아니라 48,000으로 나올 수 있습니다. B2와 B6만 같은 값으로 인정되고, B4와 B7은 눈으로는 비슷해도 엑셀 입장에서는 다른 글자로 보기 때문입니다.

왜 문제가 되는지: 엑셀은 ‘비슷해 보이는 값’을 같은 값으로 보지 않습니다

SUMIF와 COUNTIF는 조건을 비교할 때 생각보다 엄격합니다. 한빛상사와 한빛상사 뒤에 공백이 붙은 값은 서로 다릅니다. 한빛상사와 셀 안 줄바꿈이 포함된 한빛상사도 서로 다르게 처리됩니다.

초보자가 여기서 많이 헷갈리는 이유는 화면상으로는 티가 거의 나지 않기 때문입니다. B열 너비가 좁거나 왼쪽 정렬로 되어 있으면 뒤 공백은 더더욱 보이지 않습니다. 그래서 수식이 틀렸다고 생각하고 SUMIF 범위만 계속 바꾸게 됩니다.

먼저 COUNTIF로 몇 건이 잡히는지 확인하면 원인을 빨리 찾을 수 있습니다. K2 셀에 아래 수식을 입력해 보세요.

=COUNTIF($B$2:$B$8,H2)

한빛상사가 총 4건처럼 보이는데 COUNTIF 결과가 2로 나오면, 거래처명 중 일부가 정확히 같지 않다는 뜻입니다. 이때는 합계 수식보다 원본 텍스트 정리를 먼저 해야 합니다.

안전한 처리법: 원본을 바로 고치지 말고 정리용 열을 하나 만듭니다

실무 파일에서는 B열 거래처명을 바로 덮어쓰지 않는 편이 안전합니다. 원본 데이터가 어떻게 들어왔는지 확인해야 할 때가 있고, 잘못 바꿨을 때 되돌리기 어렵기 때문입니다. 그래서 G열에 정리거래처라는 보조 열을 하나 만듭니다.

G1 셀에 정리거래처라고 입력합니다. 그리고 G2 셀에 아래 수식을 넣습니다.

=TRIM(CLEAN(B2))

TRIM은 앞뒤의 불필요한 공백과 중간의 반복 공백을 정리하는 함수입니다. CLEAN은 셀 안 줄바꿈처럼 눈에 잘 안 보이는 제어 문자를 정리할 때 도움이 됩니다. 즉, B2에 들어 있는 거래처명을 계산용으로 깔끔하게 바꿔 주는 보조 수식입니다.

G2 셀 오른쪽 아래 모서리에 마우스를 가져가면 작은 십자 모양이 보입니다. 이것을 G8까지 아래로 끌어 내려서 G2:G8 범위에 같은 수식을 복사합니다. 이제 SUMIF의 조건 범위를 B열 원본이 아니라 G열 정리거래처로 바꿔야 합니다.

J2 셀에 정리 후 합계를 표시한다고 정하고, 아래 수식을 입력합니다.

=SUMIF($G$2:$G$8,TRIM(CLEAN(H2)),$F$2:$F$8)

이 수식은 G2:G8의 정리된 거래처명에서 H2의 거래처명을 찾아 F열 금액을 더합니다. H2 셀에도 혹시 모를 공백이 있을 수 있으니 조건 부분에도 TRIM과 CLEAN을 같이 씌웠습니다. 이렇게 하면 한빛상사의 합계가 70,000으로 맞아야 합니다.

흔한 실수: Ctrl+H로 공백을 전부 지우면 더 위험할 수 있습니다

공백 문제가 보이면 바로 Ctrl+H를 눌러 찾기 및 바꾸기로 빈칸을 전부 없애는 경우가 있습니다. 그런데 이 방법은 조심해야 합니다. 예를 들어 거래처명이 서울 문구센터처럼 단어 사이에 정상적인 띄어쓰기가 있는 경우, 서울문구센터로 붙어 버릴 수 있습니다.

그래서 처음에는 원본 열을 직접 수정하지 말고 G열처럼 정리용 열을 만들어 검산하는 방식이 안전합니다. 결과가 맞는지 확인한 뒤에만 필요한 경우 값으로 붙여넣기 하면 됩니다. 값 붙여넣기는 정리된 G열을 복사한 뒤, B열에 붙일 위치를 선택하고 홈 탭의 붙여넣기 아래 화살표에서 값 붙여넣기를 선택하면 됩니다.

점검 포인트: 수식이 맞는데도 결과가 이상할 때 보는 순서

• 조건 셀 확인: H2에 입력한 거래처명 앞뒤에 공백이 없는지 확인합니다.
• 건수 먼저 확인: SUMIF 전에 COUNTIF로 몇 행이 잡히는지 봅니다.
• 원본 범위 확인: 조건 범위는 B2:B8 또는 G2:G8, 합계 범위는 F2:F8처럼 행 개수가 같아야 합니다.
• 금액 열 확인: F열 금액이 숫자인지 확인합니다. 왼쪽 정렬된 텍스트 금액이면 합계가 이상할 수 있습니다.
• 보조 열 활용: 원본을 바로 고치지 말고 정리거래처 열로 먼저 검산합니다.

여기서 특히 많이 틀리는 부분은 조건 범위와 합계 범위의 행 개수입니다. 예를 들어 조건 범위는 B2:B8인데 합계 범위를 F:F 전체 열로 잡으면 당장 계산은 될 수 있어도 나중에 데이터가 늘어났을 때 관리가 헷갈립니다. 초보 단계에서는 시작 행과 끝 행을 맞추는 습관이 중요합니다.

응용 팁: 월별 거래처 합계에도 같은 방식으로 연결하기

거래처명 공백 문제를 정리해 두면 SUMIFS에도 그대로 응용할 수 있습니다. 예를 들어 I5 셀에 기준 시작일 2026-08-01, J5 셀에 기준 종료일 2026-08-31이 있고, H5 셀에 한빛상사를 입력했다고 해보겠습니다. 이때 G열 정리거래처를 기준으로 월별 거래처 합계를 구할 수 있습니다.

=SUMIFS($F$2:$F$8,$G$2:$G$8,TRIM(CLEAN(H5)),$A$2:$A$8,">="&I5,$A$2:$A$8,"<="&J5)

위 수식은 거래처 조건과 주문일 조건을 동시에 보는 방식입니다. SUMIF는 조건이 하나일 때 쓰고, SUMIFS는 조건이 여러 개일 때 쓴다고 기억하면 쉽습니다. 거래처명 정리 열을 만들어 두면 월별, 담당자별, 상품별 집계에서도 같은 문제를 줄일 수 있습니다.

반복 파일을 자주 받는다면: 선택 범위 공백 정리 매크로

매주 같은 양식의 주문 파일을 받는다면 보조 열 방식으로 먼저 검산한 뒤, 반복 작업은 간단한 매크로로 줄일 수 있습니다. 다만 매크로를 실행하면 실행 전 상태로 Ctrl+Z 되돌리기가 안 되는 경우가 많습니다. 반드시 파일을 복사해 백업본을 만든 뒤 테스트하세요.

아래 코드는 선택한 셀 범위의 앞뒤 공백, 반복 공백, 줄바꿈 일부를 정리하는 예시입니다. 거래처명 열인 B2:B8처럼 정리할 범위를 먼저 마우스로 선택한 다음 실행하는 방식입니다.

Sub 선택범위_공백정리()
    Dim cell As Range
    For Each cell In Selection
        If Not IsError(cell.Value) Then
            cell.Value = WorksheetFunction.Trim(WorksheetFunction.Clean(Replace(cell.Value, Chr(160), " ")))
        End If
    Next cell
End Sub

사용 위치는 개발 도구 탭의 Visual Basic에서 모듈을 추가한 뒤 붙여넣으면 됩니다. 개발 도구 탭이 안 보이면 파일, 옵션, 리본 사용자 지정에서 개발 도구를 체크하면 됩니다. 처음에는 전체 열을 선택하지 말고 B2:B8처럼 작은 범위에서 테스트하는 것이 안전합니다.

되돌리는 방법은 실행 전 시트를 복사해 두는 것입니다. 시트 탭을 마우스 오른쪽 버튼으로 클릭하고 이동/복사를 선택한 뒤 복사본 만들기를 체크하면 됩니다. 매크로는 편하지만 원본을 직접 바꾸는 작업이므로, 보조 열 검산 후 적용하는 흐름을 추천합니다.

실무 체크: 합계가 이상하면 함수보다 데이터 모양부터 봅니다

SUMIF나 COUNTIF가 이상할 때는 수식을 복잡하게 바꾸기 전에 조건 값이 정말 같은지 먼저 확인하는 것이 빠릅니다. 거래처명, 상품코드, 사번, 부서명처럼 텍스트 조건으로 집계하는 파일에서는 공백 하나 때문에 결과가 달라질 수 있습니다.

H2 조건 셀, B열 원본 거래처, G열 정리거래처, F열 금액 범위를 차례대로 확인해 보세요. 원본은 보존하고 정리용 열로 검산하는 습관만 들여도, 실무에서 발생하는 조건별 합계 오류의 상당수를 훨씬 안정적으로 잡을 수 있습니다.