SUMIF 범위 오류 VALUE 뜨는 이유와 범위 크기 맞추는 법

지난달 정산 시트에서 월별 매출을 SUMIF로 합산하다가 갑자기 VALUE 오류가 떠서 한참 헤맨 적이 있다. 원인을 뜯어보니 조건 범위는 A2:A50인데 합계 범위를 실수로 C2:C51까지 한 칸 더 잡아버린 게 문제였다. 행이 50개인 범위와 51개인 범위를 SUMIF에 함께 넣으면 Excel은 두 범위를 한 칸씩 짝지어 계산하려다 마지막 한 칸을 매칭시키지 못해 곧바로 #VALUE! 오류를 던진다. 반복되는 정산·매출 집계 시트에서 SUMIF 범위 오류로 시간을 잡아먹는 실무자를 대상으로, 오류가 뜨는 이유부터 바로 따라 할 수 있는 해결 순서, 그리고 같은 문제가 재발하지 않도록 막는 습관까지 정리했다.

SUMIF 범위 오류로 VALUE가 뜨는 가장 흔한 이유는 criteria_range와 sum_range의 행과 열 개수가 서로 다르기 때문이다. 실무 시트를 놓고 보면 이 범위 크기 불일치가 전체 오류 사례의 절반 이상을 차지하고, 그다음으로 흔한 원인은 합계 범위 안 숫자가 텍스트 형식으로 저장돼 있는 경우다. 이 두 가지만 먼저 점검해도 대부분의 상황은 5분 안에 해결된다.

SUMIF 범위 오류, VALUE는 왜 뜨는 걸까요

SUMIF 범위 오류의 핵심 원인은 조건 범위(criteria_range)와 합계 범위(sum_range)의 크기 불일치다. 두 범위의 행 개수나 열 개수가 하나라도 다르면 Excel은 어느 셀을 더해야 할지 판단하지 못해 VALUE 오류를 낸다. 예를 들어 조건 범위가 3행짜리 A2:A4인데 합계 범위가 4행짜리 C2:C5라면, Excel은 A4에 대응하는 합계 셀이 C5인지 C4인지 알 수 없어 계산 자체를 포기해 버린다.

그 외에도 실무에서 자주 마주치는 원인은 다음 네 가지로 정리된다.

• 범위 크기 불일치 — criteria_range와 sum_range의 행·열 개수가 서로 다른 경우
• 텍스트 숫자 — 합계 범위의 숫자가 실제로는 문자 형식으로 저장된 경우
• 외부 파일 참조 — 닫혀 있는 다른 통합문서의 셀을 sum_range로 지정한 경우
• 배열 조건 — MONTH(), TEXT() 같은 함수의 결과를 범위째 조건으로 사용한 경우

마이크로소프트 공식 지원 문서에서도 이 네 가지를 SUMIF·SUMIFS의 대표적인 VALUE 오류 원인으로 안내하고 있으며, 실제 상담 사례를 봐도 이 네 유형을 벗어나는 경우는 드물다.

SUMIF 범위 오류 해결하는 방법 순서대로 따라하기

가장 먼저 criteria_range와 sum_range의 크기부터 맞추면 이 문제의 대부분이 풀린다. 스크린샷 없이도 그대로 따라 할 수 있도록 메뉴 경로와 순서, 그리고 각 단계에서 무엇을 눈으로 확인해야 하는지까지 구체적으로 적었다.

1. 수식 입력줄에서 SUMIF(criteria_range, criteria, sum_range) 세 인수를 하나씩 확인한다. Ctrl과 물결표(`)를 함께 누르면 시트 전체 수식이 한 화면에 표시되어 범위를 빠르게 비교할 수 있다.
2. criteria_range와 sum_range를 각각 드래그해 시작 행과 끝 행이 정확히 같은지 확인한다. 예를 들어 A2:A50이면 sum_range도 반드시 C2:C50이어야 하고, C2:C51처럼 한 칸이라도 밀리거나 C1:C50처럼 시작 행이 다르면 곧바로 오류가 난다.
3. sum_range 셀이 왼쪽으로 정렬돼 있다면 텍스트로 저장된 숫자다. 데이터 탭 > 데이터 도구 > 텍스트 나누기를 클릭하고 구분 기호로 분리됨을 선택한 뒤 그대로 마침을 누르면 대부분 숫자로 바뀐다. 셀 왼쪽 위에 초록색 삼각형 표시가 보이면 텍스트 숫자일 가능성이 높으니 함께 확인한다.
4. 텍스트 나누기로도 안 바뀌면 빈 열에 =VALUE(해당 셀)을 입력하고 채우기 핸들로 복사한 다음, 새로 만든 숫자 열을 sum_range로 다시 지정한다. 숫자에 콤마(,)가 섞여 있다면 =SUBSTITUTE(셀,”,”,””)로 콤마를 먼저 제거한 뒤 VALUE로 변환해야 정확히 숫자로 바뀐다.
5. 범위를 모두 손봤다면 수식을 다시 입력하고 Enter를 누른 뒤 값이 정상적으로 나오는지 확인한다. 이후 데이터가 계속 늘어날 시트라면 범위를 넉넉히 잡거나 표(테이블) 기능으로 바꿔 두면 같은 오류가 재발하지 않는다.

범위 크기를 맞췄는데도 안 된다면 어디를 봐야 할까

범위 크기와 숫자 형식을 모두 확인했는데도 SUMIF 범위 오류가 남아 있다면 아래 표에서 나머지 원인을 원인별로 짚어보면 된다.

표에 있는 여섯 가지 원인 중에서는 텍스트 숫자와 오류값 혼입이 특히 자주 나타난다. 예를 들어 ERP나 회계 프로그램에서 내려받은 매출 데이터를 그대로 붙여넣은 경우, 겉보기엔 숫자 같아도 실제로는 ‘1,200’처럼 천 단위 구분 기호가 포함된 문자열인 경우가 많다. 이런 셀은 앞서 소개한 SUBSTITUTE와 VALUE 조합으로 콤마를 제거한 뒤 숫자로 바꿔야 정상적으로 합산된다. 또한 다른 수식의 결과값이 끼어 있는 범위를 그대로 sum_range로 지정하면, 그 수식이 하나라도 #N/A나 #REF! 같은 오류를 내는 순간 SUMIF 전체가 VALUE 오류로 이어지므로 IFERROR로 미리 감싸두는 습관이 필요하다.

예를 들어 범위 크기가 다른 경우를 표로 비교하면 다음과 같다.

표에서 보듯 행 개수가 단 1개만 어긋나도 오류가 발생한다. 여러 열을 한 번에 참조하는 SUMIF(A2:B50, 조건, D2:D50)처럼 열 개수가 다른 경우도 같은 원리로 오류가 나므로, 범위를 드래그할 때는 행뿐 아니라 열 개수도 함께 확인해야 한다.

SUMIF 오류 예방하려면 평소 어떻게 써야 할까

SUMIF 범위 오류를 아예 줄이려면 범위를 지정할 때부터 같은 행 번호로 끝나는 습관을 들이는 게 가장 확실하다. 표가 자주 늘어나는 시트라면 처음부터 범위를 넉넉히 잡거나 표(테이블) 기능으로 전환해 자동 확장되게 만드는 것도 방법이다.

• 표(테이블, Ctrl+T) 기능으로 전환해 데이터가 추가될 때 범위가 자동으로 늘어나게 한다.
• 표로 바꾸기 어려운 시트라면 범위를 실제 데이터보다 넉넉히 잡아둔다. 예: A2:A50 대신 A2:A1000.
• 수식을 여러 셀에 복사할 계획이라면 범위 앞에 $를 붙여 절대참조로 고정해 둔다.
• 조건 범위에 MONTH(), TEXT() 같은 함수를 직접 쓰지 않고, 별도 보조열에 값을 미리 계산해 둔 뒤 그 보조열을 참조한다.

조건이 여러 개라 SUMIFS를 쓸 때도 규칙은 같다. range 인수 하나라도 크기가 다르면 SUMIF 범위 오류와 똑같이 VALUE가 뜬다. 조건에 와일드카드(* 또는 ?)를 쓸 때는 텍스트 조건에만 적용되고, 숫자 조건은 ">100"처럼 부등호를 따옴표로 묶어야 한다. 조건이 3개 이상으로 늘어나면 range 인수도 그만큼 늘어나는데, 이때는 인수 하나하나를 눈으로 비교하기보다 조건 범위와 합계 범위를 표 기능으로 통일해 애초에 크기가 어긋날 수 없게 만드는 편이 관리하기 쉽다.

수식 자체는 맞는데 다른 함수에서도 비슷한 오류가 반복된다면 엑셀 프로그램 파일 손상일 가능성도 있다. 이럴 때는 윈도우 프로그램 설치 오류 해결 방법을 참고해 재설치 여부를 확인해 보는 것도 방법이다. 재설치 전에는 반드시 원본 파일을 백업해 두고, 애드인이나 매크로가 걸려 있는 문서라면 재설치 후 다시 활성화해야 한다는 점도 함께 기억해 두면 좋다.

자주 묻는 질문

Q. 범위 크기는 맞는데도 VALUE 오류가 계속 떠요.
조건 범위나 합계 범위 안에 다른 함수의 배열 결과나 #N/A 같은 참조 오류가 섞여 있을 가능성이 높다. Ctrl+G로 이동 옵션을 열어 수식 오류가 있는 셀을 먼저 찾아 IFERROR로 감싸보자. 범위 안에 병합된 셀이 있는 경우에도 행 개수 계산이 어긋나 같은 오류가 날 수 있으니 병합 셀 여부도 함께 확인한다.

Q. 다른 파일을 참조하는 SUMIF는 항상 열어둬야 하나요.
참조 대상 통합문서가 닫혀 있으면 이 오류가 발생하는 경우가 많다. 파일을 열어두고 계산하거나 SUMPRODUCT 배열 수식으로 바꾸면 닫힌 상태에서도 계산된다. 매일 값이 바뀌는 외부 파일을 자주 참조한다면 아예 필요한 데이터만 현재 파일로 복사해 오는 방식이 더 안정적이다.

Q. SUMIF 대신 SUMIFS를 써야 하는 경우는 언제인가요.
조건이 2개 이상일 때는 SUMIFS를 써야 하며, 이때도 모든 range 인수의 크기를 동일하게 맞춰야 VALUE 오류를 피할 수 있다. 조건이 하나뿐이라도 나중에 조건이 추가될 가능성이 있다면 처음부터 SUMIFS로 작성해 두는 편이 수식을 다시 고치는 수고를 줄여준다.

Q. SUMIF 조건에 와일드카드를 쓸 때 오류가 나는 이유는 무엇인가요.
와일드카드(*, ?)는 텍스트 조건에서만 동작한다. 숫자나 날짜 조건에 물음표나 별표를 넣으면 조건 자체가 텍스트로 인식되어 값이 하나도 걸러지지 않거나 합계가 0으로 나올 수 있다. 숫자 조건은 ">=100"처럼 부등호와 숫자를 함께 따옴표로 묶어 입력해야 한다.

여기까지가 SUMIF 범위 오류로 VALUE가 뜰 때 순서대로 확인하면 좋은 내용이다. 범위 크기부터 텍스트 형식, 닫힌 파일 참조까지 하나씩 짚어보면 대부분 어렵지 않게 풀리고, 표 기능과 절대참조를 습관으로 들이면 애초에 오류가 날 여지도 크게 줄어든다. 더 자세한 공식 설명은 Microsoft 공식 지원 문서에서도 확인할 수 있다. 여러분은 SUMIF 오류 겪었을 때 어떤 원인이었는지, 아래 댓글로 공유해 주면 다음 실무 팁 글에 반영하겠다.

출처: Editlab, https://editlab.luvpp.com

EDITLAB이 매체는 Editlab이 발행합니다분야별 전문 매체 8곳과 무료 데이터 도구를 함께 운영합니다.네트워크 보기 ›

글 Editlab 편집부 · 발행 전 자동 점검(중복·형식·규칙) · 정정 요청 · Google 검색에서 이 매체를 우선 출처로 등록