[함수] 여러시트에 sumif 적용하기

sumif 함수는 여러 시트에 걸쳐서 적용할 수는 없다는 내용의 글을 이전에 작성한 적이 있습니다.

그런 종류의 질문이 올라오면 VBA를 이용하던지 데이터- 통합 기능을 이용하라는 답변을 달았습니다.

그런데, 우연히 이런 답변을 발견했습니다.

역시 큰 형님의 작품입니다.

여러시트SUMIF.xlsx

비슷한 예제를 만들어 첨부하였습니다. 해당 예제에서 수식 계산 기능을 이용해, 이 수식이 어떤 식으로 작동하는지 보려고 합니다.

=SUMPRODUCT(SUMIF(INDIRECT(ROW(INDIRECT("1:3"))&"월분!$C$5:$C$12"),C4,INDIRECT(ROW(INDIRECT("1:3"))&"월분!$D$5:$D$12")))

일단, 수식을 전체적으로 보면

맨 바깥에 sumproduct함수가 감싸고 있고

그 안에 sumif함수가 있습니다.

sumif함수의 인수는 "조건범위, 조건, 합계범위" 이렇게 되는데

위의 수식에서

조건범위는 INDIRECT(ROW(INDIRECT("1:3"))&"월분!$C$5:$C$12") 라고 되어있습니다.

합계범위는 C를 D로 바꾼 것 밖에 없으므로, 조건범위를 나타내는 수식을 제대로 이해하면 해결될 것 같습니다.

1:3은  indirect함수를 씌우면 "1행부터 3행까지"를 의미하게 됩니다

그 앞에 ROW함수가 있으므로 결국 1,2,3이라는 숫자가 나오게 됩니다.

결국 (ROW(INDIRECT("1:3"))까지의 수식은 {1,2,3}을 만들기 위한 수식으로, 다른 방법으로 표현할 수도 있을 것 같습니다.

(예를 들면, COLUMN(INDIRECT("A:C")) 이런 식으로....

뒤에 있는 "월분!C5:C12"와 Indirect함수에 의해 합치게 되면

여기까지가 조건범위를 나타내는 수식입니다.

여기까지 수식이 진행되면, sumif함수의 조건범위는

1월분 시트에서 3월분 시트까지의 C5:C12범위를 지정하게 됩니다.

그런데 이 수식이 신기한 것은, 이 다음 단계로 넘어가면 조건범위 부분이 모두 에러를 발생시킵니다.

즉, 이전의 글에서 쓴 것처럼 sumif함수의 조건범위 부분은 단일한 시트에서만 가능하지, 여러 시트에 걸쳐서 조건범위를 주는 것은 가능하지 않으므로 이런 오류가 나는 것은 당연한 것입니다.

그런데

합계 범위도 조건범위와 마찬가지로 수식 계산이 진행되면, 위와 같이 1월분 시트부터 3월분 시트까지의 D5:D12범위를 합계범위로 지정하는 모양새가 됩니다.

그러므로, 결국, 조건범위는 1월분 시트에서 3월분 시트까지의 C5:C12

조건은 "김우수"

합계범위는 1월분 시트에서 3월분 시트까지의 D5:D12가 됩니다만

sumif함수의 한계로 조건범위와 합계범위가 모두 에러가 납니다.

그런데 여기서 수식계산을 계속 진행시키면

에러가 났던 세 시트의 sumif값이 그대로 계산이 되고,

sumproduct에 의해 그 값들이 합해져서, 원하는 결과값이 나오게 됩니다.

수식계산 도중에 에러가 났는데 어째서 결과는 제대로 나오는 것인지 이해하기 힘듭니다만, 어쨋든 이 수식을 이용하면 여러 시트에 걸쳐서 sumif함수를 적용할 수 있다는 것은 분명합니다.