이 글의 목적은 엑셀에서 COUNTIFS, SUMIFS 함수가 로케일(지역 설정) 차이로 인해 에러가 발생할 때 근본 원인을 정확히 진단하고, 재현 가능한 표준 절차와 예시 공식을 통해 현장에서 즉시 해결하도록 돕는 것이다.
1. 로케일로 인한 오류의 핵심 개념
엑셀 수식은 운영체제와 오피스의 지역 설정에 영향을 받는다. 특히 목록 구분 기호(argument separator)와 소수점 구분 기호(decimal symbol), 날짜 형식, 천 단위 구분 기호가 COUNTIFS·SUMIFS의 조건(criteria) 문자열 해석에 직접 관여한다. 구분 기호가 기대 값과 다르면 함수가 #VALUE!, #NAME? 또는 잘못된 결과를 반환한다.
| 구분 | 영향 항목 | 예시(미국식) | 예시(유럽식) | 오류 증상 |
|---|---|---|---|---|
| 목록 구분 기호 | 인수 구분 | 콤마 , | 세미콜론 ; | 인수 개수 오류 |
| 소수점 기호 | 숫자 파싱 | 점 . 예: 1.5 | 콤마 , 예: 1,5 | 조건 비교 실패 |
| 날짜 형식 | 조건 문자열 | MM/DD/YYYY | DD.MM.YYYY 등 | 날짜 인식 실패 |
| 천 단위 기호 | 조건 문자열 | , 예: 1,000 | . 예: 1.000 | 숫자→텍스트 취급 |
주의 : 수식 내부의 인수 구분 기호는 엑셀이 자동으로 변환하지 않는다. 잘못된 구분 기호를 쓰면 함수 자체가 해석되지 않는다.
2. 문제를 재현하는 대표 상황
2.1 구분 기호 불일치
시스템 구분 기호가 세미콜론인데 아래와 같이 콤마로 인수를 나누면 오류가 발생한다.
=SUMIFS($C:$C,$A:$A,"A",$B:$B,">=1.5") ' 세미콜론 로케일에서 잘못된 예 올바른 예는 다음과 같다.
=SUMIFS($C:$C;$A:$A;"A";$B:$B;">=1,5") 2.2 소수점 기호 불일치
소수점이 점인 환경에서 ">=1,5"와 같이 콤마를 쓰면 텍스트 비교가 되어 결과가 달라진다.
=COUNTIFS($B:$B, ">=1,5") ' 점 소수점 로케일에서 잘못된 예 다음처럼 점을 사용하거나 숫자 결합으로 안전하게 작성한다.
=COUNTIFS($B:$B, ">=1.5") =COUNTIFS($B:$B, ">=" & 1.5) 2.3 날짜 비교의 로케일 의존
문자열 날짜는 로케일에 따라 파싱 실패가 잦다.
=SUMIFS($C:$C,$A:$A,">=2025-01-01",$A:$A,"<2026-01-01") ' 일부 로케일에서 실패 DATE 함수를 사용하면 로케일 영향을 제거한다.
=SUMIFS($C:$C,$A:$A,">=" & DATE(2025,1,1),$A:$A,"<" & DATE(2026,1,1)) 3. 진단 체크리스트
아래 순서로 확인하면 원인을 빠르게 좁힐 수 있다.
- 현재 파일에서 인수 구분 기호가 무엇인지 확인한다. 빈 셀에
=ROW(1,2)또는=ROW(1;2)를 입력하여 에러가 나지 않는 쪽이 정답이다. - 소수점·천 단위 기호를 확인한다. 숫자 1.5를 입력했을 때 자동으로 1,5로 바뀐다면 소수점은 콤마다.
- 날짜 셀이 실제 날짜인지 확인한다. 셀 서식을
일반으로 바꿔 일련번호가 보이면 날짜이다. - 조건 문자열에 천 단위 구분 기호가 포함되어 있지 않은지 확인한다.
">1000"처럼 순수 숫자만 사용한다. - 조건 결합 시 문자열이 아닌 숫자 결합을 우선한다.
">=" & 1.5형태가 안전하다.
4. 근본 해결 전략
4.1 인수 구분 기호 표준화
팀 내 표준을 세미콜론 또는 콤마로 정하고, 모든 공식을 동일 규칙으로 유지한다. 혼재를 막기 위해 템플릿 통합 문서의 샘플 수식을 제공한다.
주의 : 파일을 타 로케일 사용자와 공유할 때 구분 기호가 자동 변환되지 않는다. 수식 문자열 검색으로 대체 작업을 문서화한다.
4.2 숫자·날짜는 함수로 생성
조건에 문자열 숫자나 문자열 날짜를 직접 쓰지 않는다. 숫자는 그대로 결합하고, 날짜는 DATE·EOMONTH로 생성한다.
=SUMIFS($C:$C,$B:$B,">=" & 0.8,$B:$B,"<" & 1.2) =COUNTIFS($A:$A,">=" & DATE(2025,1,1),$A:$A,"<" & EOMONTH(DATE(2025,12,1),0)) 4.3 NUMBERVALUE로 소수점 안전 처리
NUMBERVALUE는 문자열 숫자를 명시적 구분 기호로 숫자화한다. 서로 다른 로케일 간 파일 이동 시 유효하다.
=SUMIFS($C:$C,$B:$B,">=" & NUMBERVALUE("1.5",".",",")) ' 점=소수, 콤마=천 단위로 해석 4.4 천 단위 구분 기호 제거
데이터 소스에서 천 단위가 문자열로 들어올 때 비교가 실패한다. 비교 전 정규화 열을 만든다.
정규화열 = NUMBERVALUE(SUBSTITUTE([@[원값]],",","")) ' 미국식 정규화열 = NUMBERVALUE(SUBSTITUTE([@[원값]],".",""),",",".") ' 유럽식 4.5 지역 불문 패턴(권장 템플릿)
아래 패턴은 로케일 불일치에 강하다.
' 1) 날짜 범위 =SUMIFS($C:$C,$A:$A,">=" & DATE(2025,1,1),$A:$A,"<" & DATE(2026,1,1))
' 2) 숫자 범위(소수 포함)
=COUNTIFS($B:$B,">=" & 1.5,$B:$B,"<=" & 2)
' 3) 다중 조건 + 공백·대소문자 이슈 무시
=SUMIFS($C:$C,$D:$D,TRIM(LOWER("A")), $E:$E,">=" & 0, $E:$E,"<" & 100)
5. Windows·Excel 설정으로 해결
5.1 Windows 지역 고급 설정
- 제어판 > 국가 또는 지역 > 형식 > 추가 설정을 연다.
- 소수 구분 기호와 자릿수 구분 기호, 목록 구분 기호를 조직 표준에 맞춘다.
- 파일 공유가 잦다면 목록 구분 기호를 프로젝트 표준으로 고정한다.
주의 : 시스템 구분 기호를 바꾸면 다른 응용프로그램의 동작에도 영향이 있다. 변경 전·후를 기록하고 팀에 공지한다.
5.2 Excel 내 시스템 구분 기호 사용 옵션
- 파일 > 옵션 > 고급에서 시스템 구분 기호 사용을 확인한다.
- 보고서 작성 일시적으로만 다른 구분 기호가 필요하면 체크를 해제하고 수동 입력한다.
6. 안전한 COUNTIFS·SUMIFS 작성 레시피
6.1 숫자 조건은 문자열이 아닌 연산 결합
=COUNTIFS($B:$B,">" & 0, $B:$B,"<=" & 100) =SUMIFS($C:$C,$B:$B,">=" & 1.5,$D:$D,"<" & 3) 위 방식은 소수점 기호 차이에 둔감하다.
6.2 날짜 조건은 DATE·TEXTJOIN 지양
TEXT로 만든 날짜는 파싱 실패 가능성이 높다. DATE를 쓰고, 월말은 EOMONTH를 쓴다.
=SUMIFS(매출[금액],매출[일자],">=" & DATE(2025,9,1),매출[일자],"<" & EOMONTH(DATE(2025,9,1),0)+1) 6.3 범주형 조건은 정규화 후 비교
전처리 열을 추가해 공백·대소문자·꼴찌문자 이슈를 제거한다.
정규화 = LOWER(TRIM(SUBSTITUTE([@[품목]],CHAR(160)," "))) =SUMIFS($C:$C,$D:$D,LOWER(TRIM("Premium"))) 7. 공유 파일의 로케일 호환 작업
7.1 수식 일괄 치환 절차
- 백업을 만든다.
- 찾기/바꾸기에서
=앞 공백,,↔;대체 규칙을 문서화한 뒤 적용한다. - 샘플 범위로 계산 검증 후 전체 범위에 반영한다.
7.2 바꾸기 규칙 권장표
| 현 로케일 | 대상 파일 | 치환 규칙 | 비고 |
|---|---|---|---|
| 세미콜론 | 콤마 로케일에서 작성 | , → ; | 함수 인수만 대상 |
| 콤마 | 세미콜론 로케일에서 작성 | ; → , | CSV 내용은 제외 |
주의 : 데이터 영역의 구분 기호와 수식 영역의 구분 기호를 혼동하지 않는다. 대량 치환 전 범위 지정이 필수이다.
8. 실무 예제 모음
8.1 제품별 월간 매출 합계
=SUMIFS(매출[금액]; 매출[제품]; "A"; 매출[일자]; ">=" & DATE(2025,10,1); 매출[일자]; "<" & DATE(2025,11,1)) 세미콜론 로케일을 가정하였다. 콤마 로케일은 인수 구분만 콤마로 바꾸면 된다.
8.2 소수 조건의 안전 비교
=COUNTIFS(검사[두께], ">=" & 1.25, 검사[두께], "<" & 1.30) 두께가 소수점 로케일에 관계없이 숫자로 비교된다.
8.3 문자열 숫자 정규화 후 합계
정규화금액 = NUMBERVALUE(SUBSTITUTE(원본[금액],".",""),",",".") =SUMIFS(정규화[금액],원본[거래처],"ACME") 8.4 공백·비가시문자 제거
정규화품목 = LOWER(TRIM(SUBSTITUTE(SUBSTITUTE([@[품목]],CHAR(160)," "),CHAR(9),""))) =COUNTIFS(정규화[품목],LOWER(TRIM("Premium"))) 9. Power Query 사용 시 주의
Power Query에서 데이터를 로드할 때 열 형식이 텍스트로 들어오면 COUNTIFS·SUMIFS 비교가 실패한다. 쿼리 단계에서 데이터 형식을 숫자·날짜로 명시 변환하고, 지역 지정이 필요한 경우 형식 유추 대신 형식 사용 및 로케일 지정을 선택한다.
10. VBA로 일괄 변환(선택)
서식이 혼합된 대규모 파일에 대해 구분 기호 자동 치환이 필요하면 다음 매크로를 활용할 수 있다. 수식만 대상으로 하며, 데이터 문자열은 변경하지 않는다.
Option Explicit Sub SwapArgSeparators() Dim ws As Worksheet, c As Range Dim useComma As Boolean, fromSep As String, toSep As String ' 환경 감지: 테스트 수식 시도 On Error Resume Next Application.Calculation = xlCalculationManual ' 간단 감지: FORMULATEXT 기반은 언어 무관 useComma = (Application.DecimalSeparator = ".") If useComma Then fromSep = ";": toSep = "," Else fromSep = ",": toSep = ";" End If For Each ws In ActiveWorkbook.Worksheets For Each c In ws.UsedRange.Cells If c.HasFormula Then If InStr(1, c.Formula, fromSep, vbTextCompare) > 0 Then c.Formula = Replace(c.Formula, fromSep, toSep) End If End If Next c Next ws Application.Calculation = xlCalculationAutomatic End Sub 주의 : 배포 전 백업은 필수이다. UDF·배열 수식·동적 배열이 포함된 통합 문서는 검증 범위를 축소해 단계적으로 적용한다.
11. 로케일 불일치 방지 운영 체크리스트
- 프로젝트 시작 시 인수 구분·소수점·날짜 표준을 합의한다.
- 템플릿 시트에 COUNTIFS·SUMIFS 샘플을 포함한다.
- 외부 데이터는 NUMBERVALUE·DATE로 정규화한다.
- 수식 리뷰 체크리스트에 구분 기호 검사를 포함한다.
- Power Query 로드 시 형식·로케일을 명시한다.
FAQ
COUNTIFS와 SUMIFS 중 어느 쪽이 로케일에 더 민감한가?
두 함수 모두 인수 구분·소수점·날짜 형식에 동일하게 민감하다. 조건을 숫자·DATE로 생성하면 민감도가 크게 낮아진다.
로케일이 다른 두 팀이 같은 파일을 공동 편집할 수 있는가?
가능하다. 다만 인수 구분·소수점 표준을 문서화하고, 수식은 숫자 결합·DATE 사용 원칙을 지켜 작성해야 한다. 필요 시 VBA로 일괄 치환 절차를 마련한다.
조건에 천 단위 기호를 써도 되는가?
권장하지 않는다. 비교 전 NUMBERVALUE로 정규화하거나, 조건에는 순수 숫자만 사용한다.
로케일이 바뀌면 기존 피벗과 수식이 모두 깨지는가?
피벗은 상대적으로 안전하나, 수식은 인수 구분 차이로 문제가 생길 수 있다. 공유 전 샘플 검증을 수행한다.
영문판 함수명을 강제로 사용할 수 있는가?
함수명은 UI 언어에 종속되며 자동 변환되지 않는다. 다국어 환경을 고려하면 함수명보다 인수·조건 작성 규칙을 표준화하는 것이 실용적이다.