숨겨진 셀은 제외하고 카운트하는 방법

위의 경우에서처럼, 3행이 숨겨져 있는데, 거기에 숫자 3이 쓰여있다

countif함수를 써서 A1:A5범위에서 3의 개수를 찾으라고 하면 

숨겨진 3행도 포함하여 찾기에 1이라는 결과가 나온다

만약, 숨겨진 셀은 제외하고, 보이는 셀에서만 countif함수를 적용하려면 어떻게 할까?

subtotal이라는 함수는 숨겨진 셀을 제외하고 보이는 셀만 셀 수 있는 함수다. 

다만, countif처럼 조건을 줄 수는 없고

위에 링크된 글에서 보듯이,

셀의 개수, 평균, 합계, 최대값, 최소값 등을 낼 수 있다. 

지식인에 질문글을 올렸더니 기발한 답이 도착했다.

답은 아래와 같다.

=SUMPRODUCT(--(A1:A5=C9), SUBTOTAL(103,OFFSET(A1,ROW(A1:A5)-ROW(A1),0)))

여기서 핵심은 아래 부분이다.

SUBTOTAL(103,OFFSET(A1,ROW(A1:A5)-ROW(A1),0) 

아래 부분을 진행하면

OFFSET(A1,ROW(A1:A5)-ROW(A1)

그 결과가 아래와 같이 나온다

OFFSET(A1, {0;1;2;3;4}, 0)

이 결과는 (비록 수식 계산 기능에서는 에러로 나오지만) 다음과 같다

{A1;A2;A3;A4;A5}

즉,  A1에서 0만큼, 1만큼, 2만큼... 아래로 이동한 결과이다

그럼 결국

SUBTOTAL(103, {A1;A2;A3;A4;A5}) 이 되는데, 이 결과는

{1;1;0;1;1}이다. 

(103옵션을 준 subtotal함수는 숨겨진 셀은 무시하고 범위 내에서 counta를 행하는데, 3행은 숨겨져있고 나머지는 보이는 셀이므로)

앞에 있는 --(A1:A5=C9) 의 결과는 

{0;0;1;0;0}이므로

SUMPRODUCT({0;0;1;0;0},{1;1;0;1;1}) 의 결과는 0이 된다.

질문.xlsx