다른 엑셀 파일의 데이터를 SUMIFS로 합산했는데 원본 파일이 열려
있을 때는 정상적으로 계산되고, 원본 파일을 닫은 뒤
#VALUE! 오류가 나타날 수 있습니다.
이 경우 수식의 괄호나 조건을 잘못 입력한 것이 아니라, 닫힌 외부 통합 문서를
참조하는 SUMIF·SUMIFS의 제한일 수 있습니다. 먼저
원본 파일을 열었을 때 오류가 사라지는지 확인한 뒤 작업 방식에 맞는 해결
방법을 선택해야 합니다.
|
| 원본 통합 문서를 닫은 뒤 SUMIFS가 #VALUE!를 반환하면 파일을 열어 새로 고치거나 배열 수식, SUMPRODUCT 또는 Power Query 중 작업 방식에 맞는 대안을 선택합니다. |
이 글은 Microsoft의 SUMIF·SUMIFS 오류 안내, 배열 수식, SUMPRODUCT와 Power Query 공식 문서를 기준으로 작성했습니다. 예제의 파일 이름, 시트 이름과 셀 범위는 실제 파일에 맞게 변경해야 합니다.
가장 빠른 확인: 원본 파일을 열면 오류가 사라지는가?
수식을 바로 변경하기 전에 먼저 오류가 닫힌 파일 참조 때문에 발생한 것인지 확인하세요.
- 수식이 들어 있는 결과 파일을 엽니다.
- 수식에서 참조하는 원본 Excel 파일도 함께 엽니다.
- 결과 파일로 돌아옵니다.
F9를 눌러 수식을 다시 계산합니다.
닫힌 외부 통합 문서를 참조하는 SUMIFS 제한일 가능성이 높습니다. 아래 해결 방법 중 하나를 선택하세요.
닫힌 파일 제한만의 문제가 아닐 수 있습니다. 이 글 아래쪽의 ‘원본 파일을 열어도 오류가 계속될 때’ 부분에서 범위 크기와 외부 링크를 확인하세요.
왜 원본 파일을 닫으면 #VALUE!가 나타날까?
다음과 같은 수식이 있다고 가정하겠습니다.
- 원본 파일:
매출원본.xlsx - 원본 시트:
Data - A열: 담당자
- B열: 진행 상태
- C열: 매출 금액
- 결과 파일 E2: 찾을 담당자
- 결과 파일 F2: 찾을 진행 상태
원래 SUMIFS 수식은 다음과 같은 형태입니다.
=SUMIFS(
'C:\업무\[매출원본.xlsx]Data'!$C$2:$C$5000,
'C:\업무\[매출원본.xlsx]Data'!$A$2:$A$5000,$E2,
'C:\업무\[매출원본.xlsx]Data'!$B$2:$B$5000,$F2
)
원본 파일이 열려 있을 때는 조건 범위와 합계 범위를 직접 계산할 수 있지만,
원본 파일이 닫혀 있으면 SUMIF·SUMIFS에서
#VALUE! 오류가 발생할 수 있습니다.
이 문제는 파일이 손상됐다는 의미와는 다릅니다. 원본 파일을 열었을 때 같은 수식이 정상 계산된다면 수식 구조보다 외부 통합 문서가 닫혀 있다는 조건을 먼저 확인해야 합니다.
해결 방법 1: 원본 파일을 열고 F9로 새로 계산
가장 간단한 방법은 원본 파일과 결과 파일을 함께 여는 것입니다.
- 원본 통합 문서를 엽니다.
- 결과 통합 문서를 엽니다.
- 결과 파일에서
F9를 누릅니다. - 결과가 갱신됐는지 확인합니다.
- 결과 파일을 저장합니다.
한 달에 한 번처럼 가끔 계산하는 파일이라면 이 방법이 가장 단순합니다. 그러나 결과 파일을 열 때마다 원본 파일도 함께 열어야 한다면 자동 보고서나 반복 업무에는 불편할 수 있습니다.
원본 파일을 열 수 없는 사용자도 결과 파일을 사용해야 한다면 다음 배열 수식이나 Power Query 방식을 검토하세요.
해결 방법 2: Microsoft 공식 대안인 SUM과 IF 배열 수식
Microsoft는 닫힌 통합 문서를 참조하는 SUMIF·SUMIFS의 대안으로
SUM과 IF를 결합한 배열 수식을 안내합니다.
앞의 SUMIFS 예제를 두 조건의 SUM·IF 배열 수식으로 바꾸면 다음과 같은 형태가 됩니다.
=SUM(
IF(
('C:\업무\[매출원본.xlsx]Data'!$A$2:$A$5000=$E2)*
('C:\업무\[매출원본.xlsx]Data'!$B$2:$B$5000=$F2),
'C:\업무\[매출원본.xlsx]Data'!$C$2:$C$5000,
0
)
)이 수식은 A열의 담당자와 E2가 같고, B열의 상태와 F2가 같은 행만 골라 C열의 금액을 합산합니다.
수식 입력 방법
수식을 입력한 뒤 일반적으로
Enter로 확인할 수 있습니다.
수식을 입력한 뒤
Ctrl + Shift + Enter를 함께 눌러 배열 수식으로
입력해야 할 수 있습니다.
배열 수식에 표시되는 중괄호는 직접 입력하지 마세요. 이전 Excel에서는
Ctrl + Shift + Enter로 입력했을 때 Excel이 중괄호를 자동으로
표시합니다.
예제의 A열, B열과 C열 범위는 모두 2행부터 5000행까지입니다. 한 범위만 2행부터 3000행처럼 다르게 지정하면 올바르게 계산되지 않을 수 있습니다.
해결 방법 3: SUMPRODUCT로 조건 합계 계산
SUMPRODUCT는 각 조건의 참·거짓 결과를 곱하고, 조건을 모두
만족하는 행의 금액을 합산하는 방식으로 사용할 수 있습니다.
같은 예제를 SUMPRODUCT로 작성하면 다음과 같습니다.
=SUMPRODUCT(
--('C:\업무\[매출원본.xlsx]Data'!$A$2:$A$5000=$E2),
--('C:\업무\[매출원본.xlsx]Data'!$B$2:$B$5000=$F2),
'C:\업무\[매출원본.xlsx]Data'!$C$2:$C$5000
)앞의 두 조건이 모두 맞는 행은 계산 과정에서 1로 처리되고, 조건이 맞지 않는 행은 0으로 처리됩니다. 마지막 C열의 금액과 곱한 결과를 합산합니다.
SUMPRODUCT 방식은 파일과 Excel 버전에 따라 외부 참조 동작을 확인해야 하므로 원본과 결과 파일의 복사본에서 먼저 시험하세요. Microsoft가 닫힌 SUMIFS 문제에 직접 제시하는 공식 우회 방법은 SUM·IF 배열 수식입니다.
전체 열 참조는 사용하지 않는 것이 좋습니다
다음처럼 전체 열을 사용하지 마세요.
=SUMPRODUCT(
--('C:\업무\[매출원본.xlsx]Data'!$A:$A=$E2),
'C:\업무\[매출원본.xlsx]Data'!$C:$C
)Excel 한 열에는 1,048,576개의 셀이 있으므로 전체 열을 배열 계산에 사용하면 실제 데이터가 적더라도 불필요하게 넓은 범위를 계산합니다.
실제 데이터가 있는 범위만 지정하세요.
$A$2:$A$5000
$B$2:$B$5000
$C$2:$C$5000해결 방법 4: 반복 보고서라면 Power Query로 원본 가져오기
원본 파일의 행이 많고 매주 또는 매월 반복해서 집계한다면 외부 수식을 계속 유지하는 것보다 Power Query로 원본 데이터를 현재 통합 문서 안에 가져오는 방식이 더 관리하기 쉬울 수 있습니다.
- 결과 Excel 파일을 엽니다.
- 데이터 탭을 선택합니다.
- 데이터 가져오기 → 파일에서 → Excel 통합 문서에서를 선택합니다.
- 원본 Excel 파일을 선택하고 열기를 누릅니다.
- 탐색 창에서 필요한 시트나 표를 선택합니다.
- 바로 가져오려면 로드를 선택합니다.
- 열 삭제나 형식 변경이 필요하면 데이터 변환을 선택합니다.
데이터를 현재 통합 문서 안으로 가져온 뒤에는 가져온 표를 대상으로 일반적인 SUMIFS를 사용할 수 있습니다. 원본 내용이 변경됐을 때는 데이터 → 모두 새로 고침으로 갱신합니다.
원본 행이 많거나, 매번 같은 파일을 집계하거나, 여러 사용자가 결과 파일을 확인하거나, 외부 수식을 수백 개 유지하기 어려운 경우에 검토합니다.
원본 파일 이름이나 열 구조가 자주 바뀐다면 새로 고침이 실패할 수 있습니다. 파일 위치, 시트 이름과 열 제목을 일정하게 유지하는 것이 좋습니다.
원본 파일을 열어도 오류가 계속될 때
원본 통합 문서를 열고 F9를 눌렀는데도 #VALUE!가
남는다면 다음 세 항목을 확인하세요.
1. 합계 범위와 조건 범위의 크기
다음 수식은 범위의 마지막 행이 서로 다릅니다.
=SUMIFS(
C2:C5000,
A2:A3000,E2,
B2:B5000,F2
)합계 범위는 5000행까지지만 첫 번째 조건 범위는 3000행까지만 지정돼 있습니다. 모든 범위의 시작 행과 마지막 행을 같게 변경하세요.
=SUMIFS(
C2:C5000,
A2:A5000,E2,
B2:B5000,F2
)2. 조건 문자열이 지나치게 긴지 확인
SUMIF·SUMIFS에서 비교하는 조건 문자열이 255자를 넘는 경우 잘못된 결과나 오류가 나타날 수 있습니다. 긴 문장 전체를 조건으로 사용하고 있다면 조건을 줄이거나 별도의 짧은 코드 열을 만들어 비교하는 방법을 검토하세요.
3. 원본 파일의 위치가 바뀌었는지 확인
원본 파일의 폴더, 파일 이름 또는 드라이브 문자가 변경되면 기존 외부 참조가 이전 위치를 계속 가리킬 수 있습니다.
- 데이터 탭을 선택합니다.
- 쿼리 및 연결 → 통합 문서 링크를 선택합니다.
- 연결된 원본 파일 이름을 확인합니다.
- 필요하면 원본 변경을 선택합니다.
- 현재 사용 중인 원본 파일을 다시 지정합니다.
- 새로 고침을 실행합니다.
사용 중인 Excel 버전에 따라 통합 문서 링크 대신 연결 편집이라는 이름으로 표시될 수 있습니다.
통합 문서 링크를 끊으면 외부 파일을 참조하던 수식이 현재 계산값으로 변경될 수 있습니다. 이후 원본 데이터가 변경돼도 자동 갱신되지 않으며, 작업을 되돌리기 어려울 수 있으므로 파일 복사본을 먼저 만드세요.
오류를 IFERROR로 먼저 숨기지 마세요
다음처럼 오류를 0으로 바꾸면 화면에서는 오류가 사라집니다.
=IFERROR(
SUMIFS(외부_합계범위,외부_조건범위,E2),
0
)하지만 원본 파일을 닫았을 때 계산되지 않는 문제는 그대로 남습니다. 실제 합계가 0인지, 오류 때문에 0으로 표시됐는지 구분하기도 어려워집니다.
먼저 원본 파일 열기, 수식 변경 또는 Power Query 적용으로 원인을 해결한 뒤 예상 가능한 오류를 표시할 필요가 있을 때만 IFERROR를 사용하세요.
작업 방식에 따라 해결 방법 선택하기
원본 통합 문서를 함께 열고 F9로 새로 계산합니다.
Microsoft 공식 대안인 SUM·IF 배열 수식을 먼저 검토합니다.
같은 크기의 제한된 범위를 사용해 SUMPRODUCT 방식을 복사본에서 확인할 수 있습니다.
Power Query로 원본 데이터를 가져오고 새로 고침하는 구조를 검토합니다.
내용 확인에 사용한 Microsoft 공식 자료
공식 문서 확인일: 2026년 7월 27일
댓글 쓰기