Excel에서 INDIRECT 함수가 동적 범위(:$E 또는 :$F와 같은 열린 범위)를 지원하지 않음

Excel에서 SUMIF(INDIRECT(...)) 오류 발생 원인 및 해결 방법

위의 수식은 Google 스프레드시트에서는 정상 작동하지만, Excel에서는 오류가 발생할 가능성이 높습니다.

 

=SUMIF(INDIRECT("'" & $A$1 & "'!$E$5:$E"), B3, INDIRECT("'" & $A$1 & "'!$F$5:$F"))

🔍 오류 발생 원인

1. Excel에서 INDIRECT 함수가 동적 범위(:$E 또는 :$F와 같은 열린 범위)를 지원하지 않음
   • Google 스프레드시트는 INDIRECT("'" & $A$1 & "'!$E$5:$E")처럼 열린 범위를 사용해도 자동으로 확장하여 처리할 수 있습니다.
   • 하지만 Excel에서는 :$E 같은 열린 범위를 SUMIF 함수에서 지원하지 않기 때문에 #REF! 오류 또는 계산이 제대로 되지 않는 문제가 발생할 수 있습니다.
2. Excel의 SUMIF 함수는 INDIRECT로 참조된 범위를 올바르게 인식하지 못할 수 있음
   • INDIRECT("'" & $A$1 & "'!$E$5:$E")는 문자열을 기반으로 동적으로 참조를 생성하지만,
     SUMIF에서 사용하면 배열 범위 인식 문제가 발생할 가능성이 있음.
3. 외부 시트를 참조하는 경우, 해당 시트가 열려 있지 않으면 INDIRECT가 동작하지 않음
   • Excel에서는 INDIRECT로 외부 파일의 데이터를 참조할 때, 해당 파일이 열려 있어야 합니다.
     그렇지 않으면 #REF! 오류가 발생할 수 있습니다.

---

✅ 해결 방법

방법 1: 열린 범위를 고정 범위로 변경

Excel에서는 열린 범위를 사용할 수 없으므로, 명확한 범위를 지정하는 것이 필요합니다.
예를 들어 $E$5:$E1000처럼 고정된 범위를 사용하면 해결될 수 있습니다.

 

=SUMIF(INDIRECT("'" & $A$1 & "'!$E$5:$E1000"), B3, INDIRECT("'" & $A$1 & "'!$F$5:$F1000"))

🔹 이 방법의 장점:

• Excel에서도 오류 없이 동작할 가능성이 높아짐
• 데이터가 일정한 범위를 넘지 않는다면 충분히 실용적임

🔹 단점:

• $E$5:$E1000처럼 최대 범위를 정해야 하므로, 데이터가 많을 경우 성능이 저하될 수 있음