엑셀 COUNTIFS 함수 – 여러 조건을 만족하는 셀의 개수 구하기

공개됨 글쓴이 Admin댓글 남기기

엑셀에서 COUNTIF 함수를 이용하면

  • 한번에 하나의 조건을 만족하는 셀의 개수를 구할 수 있지만

COUNTIFS를 이용하면

  • 여러 개의 조건을 만족하는 셀의 개수를 구할 수 있다.

COUNTIFS는 COUNTIF함수의 IF에 S가 붙은 형태이다.
S는 영어에서 복수형일 때 붙으므로 IF가 여러 개 있는…
즉 조건을 여러 개 정해서 셀의 개수를 구하는 함수라고 생각하면 된다.

▶ COUNTIF함수가 궁금하면 여기를 클릭!

※ COUNTIFS는 어떻게 발음해야 할까? 마이크로소트프 홈페이지 동영상 강의를 보면 “카운트이프스”라고 하는데 우리나라 사람들은 “카운트이프에스”라고도 많이 부른다.
구문(Syntax)
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)

여러 조건(criteria)에 맞는 셀의 개수를 구함

인수 :
– criteria_range1 : 첫번째 조건을 적용할 범위
– criteria1 : 개수를 구할 첫번째 조건
– criteria_range2 : 두번째 조건을 적용할 범위
– criteria2 : 개수를 구할 두번째 조건

criteria_range, criteria 는 최대 127개까지 지정할 수 있다.

 

사용예 :

1) 특정 조건을 만족하는 셀의 개수 구하기

아래 그림의 자료에서

  • 분류가 “노트류”이면서
  • 상품이 “스프링노트”인

것의 개수를 구하기 위해 다음과 같이 수식을 입력해 보자.

=COUNTIFS(C5:C12,”노트류”,D5:D12,”스프링노트”)

엑셀함수 COUNTIFS

수식을 정리해 보면 COUNTIFS함수는 아래 그림과 같이

  • 첫번째 조건범위 “C5:C12″에서 값이 “노트류”이고
  • 두번째 조건범위 “D5:D12″에서 값이 “스프링노트”인 것을

만족하는 행의 갯수, 2를 구해준다.

엑셀함수 COUNTIFS

 

 

2) 와일드카드 문자로 찾아서 조건을 만족하는 셀의 개수 구하기

이번에는 다음 표에서

  • 분류가 “노트류”이면서
  • 상품이름에  “스프링노트”가 포함된 것의 개수를 구해보자.

엑셀함수 COUNTIFS

=COUNTIFS(C19:C26,”노트류”,D19:D26,”*스프링노트*”)
를 입력한다.

“*”는 여러 문자를 대신하므로 “스프링노트”라는 문자열 앞뒤로 몇개의 문자열이 오든 상관없이 “스프링노트”라는 값을 포함한 셀은 개수에 포함된다.

분류가 노트류이고 상품이름에 “스프링노트”를 포함한 것은 스프링노트고급형 1개, 스프링노트 2개가 있으므로 COUNTIFS함수는 개수 3을 구해준다.

※ 참고 : 와일드카드 문자는 좀 더 정교한 방법으로 값을 찾을 때 사용하는데 다음 2가지가 있다.

  • 물음표(?) : 한 개의 임의의 문자열을 의미
  • 별(*) 표시 : 여러 개의 임의의 문자열을 의미

 

3) 숫자 조건을 만족하는 셀의 개수 구하기

이번에는 다음 표에서

  • 분류가 “노트류”이면서
  • 판매금액이 10만원 이상(10만원보다 크거나 같은)인 행의 갯수를 구해보자.

엑셀함수 COUNTIFS

=COUNTIFS(C33:C40,”노트류”,G33:G40,”>=100000″)
를 입력한다.

위에서 분류가 “노트류”이고, 판매금액이 10만원 이상인 것은

  • 33행의 210,000원
  • 34행의 300,000원 이므로

위 수식은 개수 2을 구해준다

※ 참고 : 위 수식에서 “>=” 수학기호가 사용되었는데 특정 값보다 크거나 같은 것을 의미한다.
수학기호를 이용하면 다음과 같이 다양한 방법으로 숫자 조건을 설정할 수 있다.

  • “=100” : 100인
  • “<>100” : 100이 아닌
  • “>100” : 100보다 큰
  • “>=100” 100보다 크거나 같은(100이상인)
  • “<100” : 100보다 작은(100미만인)
  • “<=100” : 100보다 작거나 같은

 

4) 다른 시트의 자료 집계하기

이번에는 COUNTIFS함수를 이용해서 실무에서 많이 사용하는 “다른 시트의 자료를 집계하는 방법”을 알아보자.

다음과 같은 교육참석현황자료가 있는데 교육과정별,참석부서별 참석자수를 구해보자.

엑셀함수 COUNTIFS

 

먼저 집계를 위해 다음과 같이 집계용 표를 만들자.

실무에서는 집계표를 만들 때 아래 표와 같이

  • 교육과정명과 부서를 미리 표에 입력하고
  • 수식에서는 미리 입력된 셀을 참조하도록 한다.

이렇게 하면 교육과정명과 부서가 변경될 때 표에서 교육과정명과 부서명만 바꾸면, 바꾼이름으로 COUNTIFS함수가 작동되므로 편리하다.

엑셀함수 COUNTIFS로 집계표만들기

– 정원은 보통 미리 정해진 인원수이므로 그냥 값을 입력한다

– 참석자수를 구하는 수식은 E47셀에 다음과 같이 입력한다.
=COUNTIFS(교육참석현황!$A$2:$A$22,집계!B47,교육참석현황!$C$2:$C$22,집계!C47)

– 불참자수는 정원에서 참석자수를 빼주도록 입력한다(=D47-E47).

– 마지막으로 E47:F47에 입력된 수식을 복사하여 E48:F55범위에 붙여 넣으면 집계표가 완성된다.

 

참석자수를 구하는 수식을 정리해 보면 COUNTIFS함수는 아래 그림과 같이

  • 1-1) 첫번째 조건범위인 교육참석현황 시트의 $A$2:$A$22 범위의 값이
  • 1-2) 집계 시트의 B47셀의 값과 같은 “직장내 성희롱 예방교육”이고
  • 2-1) 두번째 조건범위인 교육참석현황 시트의 C$2:$C$22 범위의 값이
  • 2-2) 집계 시트의 C47셀의 값과 같은 “인사팀”인 것을

만족하는 행의 갯수, 3을 구해준다.

엑셀함수 COUNTIFS로 집계표만들기

 

[ 통계 함수 ]

댓글 남기기

이메일은 공개되지 않습니다.