여러가지 조건을 만족하는 개수 세기(1) - CountIfs

1. 문제

여러가지 조건을 만족하는 개수 세기.xlsx

0.01MB

 

 

아래와 같이 왼쪽에 id별로 cnt_1부터 cnt_5까지의 개수가 있는데,

cnt_1부터 cnt_5까지의 최대 개수는 H2셀부터 기재되어 있습니다.

다시 말해 cnt_1은 0부터 5까지 6가지 경우의 수가 있고,

cnt_2는 0부터 3까지 4가지 경우의 수가 있습니다.

 

이때 cnt1이 1에서 5 사이인 것, 그리고, cnt1이 1에서 5 사이이고 cnt2가 1에서 3 사이인, 다시 말해 둘 다 만족하는 개수를 세려고 하며, 이것을 cnt_5까지 반복하려고 합니다.

 

 

2. 해법 1

한 가지 조건만 만족할 때는 CountIf, 두 가지 조건이상을 만족할 때는 CountIfs 함수를 이용하면 사용하면 되는데, 한 가지 조건을 만족하는 개수를 셀 때도 CountIfs를 사용할 수도 있습니다.

 

가. cnt_1이 0보다 큰 경우

 

(1) 수식

cnt_1이 1에서 5 사이인 것의 개수를 세려면

H3셀에

=countifs(b2:b33,">0")

라고 입력하면 0보다 큰 개수 21이 구해지는데,

 

b2:b33은 F4키를 눌러 절대 참조 형식으로 바꿉니다.

 

B1셀 필터 버튼을 눌러 0의 체크 표시를 눌러서 0을 제거하면

 

아래와 같이 0보다 큰 숫자만 표시되는데,

B3셀을 클릭하고, Shift + Ctrl + ↓키를 동시에 눌러서 개수를 세면

 

아래 상태 표시줄에 개수: 21, 숫자 데이터 개수: 21이 표시됩니다.

수식으로 구한 값과 일치합니다.

 

 

(2) 상태 표시줄에 개수 등 추가적인 계산 값 표시하기

상태 표시줄의 기본 값은 합계만 표시되는 것인데,

다른 값을 추가하려면

상태 표시줄에서 마우스 오른쪽 버튼을 누르고

추가하려고 하는 평균, 개수 등을 클릭하면 됩니다.

 

(3) 수식을 다르게 만들기

=countifs($b$2:$b$32,"<>0")

라고 0이 아닌 경우를 셀 수도 있습니다.

 

나. cnt_1이 0보다 크고, cnt_2가 0보다 큰 경우

(1) 수식

=COUNTIFS($B$2:$B$33,">0",$C$2:$C$33,">0")

라고

조건 2개를 입력하는데,

$B$2:$B$33,">0"를 복사한 다음, 쉼표를 넣고, 붙여 넣은 다음 B를 C로 수정하면 됩니다.

 

수식의 의미는 b2셀에서 b33셀의 값이 0보다 크고, c2셀에서 c33셀까지의 값도 0보다 큰 경우의 수를 세는 것으로 5가 구해졌습니다.

 

값이 5가 구해졌는데, 눈으로 C열의 0보다 큰 것의 개수를 세어봐도 5가 맞습니다.

 

C1셀의 필터도 0을 제거하면 5개 맞습니다.

그러나, 3행은 둘 다 0보다 큰 경우가 아니라서 H3셀은 보이지 않습니다.

 

(2) 필터 지우기

필터가 걸린 것을 일시에 지우려면

홈 탭 > 편집그룹에서 

'정렬 및 필터'를 누르고, 지우기를 누르면 됩니다.

 

다. cnt_1에서 cnt_5까지 0보다 큰 수의 개수 구하기

위와 같이 조건을 5번 줘야 합니다.

그러면 수식은

=COUNTIFS($B$2:$B$32,">0",$C$2:$C$32,">0",$D$2:$D$32,">0",$E$2:$E$32,">0",$F$2:$F$32,">0")

이 되고, 값은 2가 구해집니다.

 

cnt_1부터 cnt_5까지 필터에서 0의 체크를 해제하면

2개 맞습니다.

 

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

0.01MB