여러가지 조건을 만족하는 개수 세기(2) - 배열 수식, SumProduct

여러가지 조건을 만족하는 개수 세기(완성)1.xlsx

0.01MB

 

3. 해법 2 

배열 수식을 이용해서 여러 가지 조건을 *(and)로 연결해서 값을 구할 수 있습니다.

 

가. 한 가지 조건을 만족하는 경우

(1) 수식 1

수식은

=SUM(IF(B2:B23>0,1,0))

라고 할 수 있습니다.

배열 수식이기 때문에 엔터 키가 아니라 Ctrl + Shift + Enter키를 눌러 입력해야 하는데, Microsoft 365 버전이기 때문에 누르지 않아도 되고, 좌우에 중괄호 표시도 없습니다.

 

(2) 수식 2

B열 전체를 지정해서

=SUM(IF(B:B>0,1,0))-1

라고 할 수도 있는데, 맨 뒤에 -1을 한 것은 cnt_1이 0보다 큰 것으로 값이 나오기 때문입니다.

 

나. 두 가지 조건을 만족하는 경우

 

두 가지 조건을 만족하는 경우는 *를 이용해야 하므로

=SUM(IF(($B$2:$B$33>0)*($C$2:$C$33>0),1,0))

가 됩니다.

수식의 의미는 B2에서 B33까지가 0보다 크고, C2에서 C33까지가 0보다 크면 1이 반환되고, 아니면 0이 반환되는데, 합계를 구하므로 개수가 되는 것입니다.

 

이때 주의해야 할 것은 한 가지 조건을 만족하는 경우는 조건을 괄호로 묶지 않는데, 조건 2개를 *로 연결해야 하므로 괄호로 묶어야 한다는 것입니다.

 

다. 다섯 가지 조건을 만족하는 경우

 

(1) 수식 1

다섯 가지 조건을 만족하는 수식은

=SUM(IF(($B$2:$B$33>0)*($C$2:$C$33>0)*($D$2:$D$33>0)*($E$2:$E$33>0)*($F$2:$F$33>0),1,0))

로서 5개의 조건이 *로 연결되어 있습니다.

 

(2) 수식 2

=SUM(IF(($B:$B>0)*($C:$C>0)*($D:$D>0)*($E:$E>0)*($F:$F>0),1,0))-1

마찬가지로 1행이 참이기 때문에 -1을 해야 합니다.

 

4. 해법 3

배열 수식을 SumProduct 함수 안에 넣어서 조건을 만족하는 개수를 구할 수 있습니다.

 

가. 한 가지 조건을 만족하는 경우

한 가지 조건을 만족하는 경우는

=SUMPRODUCT((B2:B33>0)*1) 또는 =SUMPRODUCT(--(B2:B33>0))를 사용해야 합니다.

 

(B2:B33>0)의 결괏값이 True 또는 False이기 때문에 합을 구할 수 없어서 1을 곱하거나 --를 사용한 것이고, if 함수를 사용할 필요도 없습니다.

 

수식을 원래대로 돌리기 위해 Esc키를 누릅니다.

 

나. 두 가지 이상 조건을 만족하는 경우

조건을 곱하면 1 또는 0이 반환되기 때문에

=SUMPRODUCT(($B$2:$B$33>0)*($C$2:$C$33>0))

라고 1을 곱하지 않아도 됩니다.

 

($B$2:$B$33>0)*($C$2:$C$33>0)를 범위로 잡고 F9키를 누르면 1과 0이 반환됩니다.

 

여러가지 조건을 만족하는 개수 세기(완성)2.xlsx

0.01MB