SUMIF 함수, SUMIFS 함수, COUNTIF 함수, COUNTIFS 함수

포스트 목차

01. SUMIF 함수

02. SUMIFS 함수

03. COUNTIF 함수

04. COUNTIFS 함수

 

본 포스트에서는 통계작업을 할 때 많이 사용되는 SUMIF, SUMIFS, COUNTIF, COUNTIFS 함수에 대해 알아보려고 한다.

 

SUMIF 함수에 대해서는 지식인 해결 시리즈에서 다룬적이 있으니 더 알고 싶은 부분이 있다면 다음 링크를 참고하면 좋을 것 같다. 

 

https://eggdrop.tistory.com/38

Excel [지식인 해결 시리즈] 007. 상반기/하반기 매출 집계 (SUMIF)

 

01. SUMIF 함수

 

여러 데이터 중에서 특정 조건을 만족하는 데이터만 합하고 싶을 때 사용한다.

 

1) SUMIF 함수의 파라미터

  - range: 조건을 조사할 영역

  - criteria: 조사할 조건

  - sum_range: 합 연산을 할 범위이며, 생략시에 range 객체로 지정한 영역이 설정된다.

   * range 와 sum_range는 같은 크기의 영역이어야 한다.

  

 2) 함수동작 원리

  (1) range로 입력한 영역에 있는 모든 셀에 대하여 criteria의 만족여부를 확인한다.

  (2) criteria를 만족할 경우에는 sum_range로 입력한 영역의 같은 위치에 있는 데이터에 대한 합 연산을 수행한다.

 

 3) criteria 설명

  criteria 만족 여부를 확인하기 위해서는 결국 논리식의 값을 조사해야 한다.

  논리식은 다음과 같이 구성된다.

 

[조사할 셀에 들어있는 값]  [연산자] [기준값]

조사할 셀에 들어있는 값은 range 파라미터로 지정하기 때문에, criteria는 "연산자" 와 "기준값"만을 텍스트로 지정한다.

 

 

 

 criteria에서 사용가능한 연산자

연산자	설명
=	criteira에 포함되어 있는 기준값과 range에서 지정한 셀의 값이이 동일한 경우에 참 반환 =의 경우 연산자 생략이 가능하다. 기준값이 문자열인 경우 문자열를 그대로 사용하면된다.
<>	criteira에 포함되어 있는 기준값과 range에서 지정한 셀의 값이 같지 않은 경우에 참 반환 기준값 없이 <>만 사용할 경우, 값이 공백인 데이터를 의미
>	criteira에 포함되어 있는 기준값이 range에서 지정한 셀의 값보다 작은 경우 참 반환
<	criteira에 포함되어 있는 기준값이 range에서 지정한 셀의 값보다 큰 경우 참 반환
>=	criteira에 포함되어 있는 기준값이 range에서 지정한 셀의 값보다 작거나 같은 경우 참 반환
<=	criteira에 포함되어 있는 기준값이 range에서 지정한 셀의 값보다 크거나 같은 경우 참 반환

4) 예제

다음과 같은 데이터가 있을 때, 원하는 조건에 맞는 값을 합산해보자.

 

 (1) 텍스트 비교

 이 데이터 중에서 기업명을 기준으로 합을 구해보자.

- range는 A열에 있는 기업명 데이터로 지정했다.

- sum_range는 C열에 있는 매출액 데이터로 지정했다. (range와 같은 크기)

- criteria에는 J열에 있는 기업명이 들어간다. (연산자가 =인 경우 생략가능)

 

위의 그림은 SUMIF를 사용하여 기업별 매출액을 계산한 데이터이다.

range로 지정한 영역의 모든셀에 대해 criteria 만족여부를 검사하고, 결과가 만족일 sum_range의 같은 위치에 있는 값을 합한다.

 

 

 

(2) 값의 대소 관계

이번에는 매출액이 기준값을 초과하는 데이터에 대해서만 합을 해보자.

 

N열에 있는 기준값을 초과하는 매출액에 대해서만 합을 하려고 한다.

 - range 부분은 매출액이 있는 C열을 지정한다.

 - critera 부분은 ">"&N2 로 지정했는데, 연산자와 기준값으로 생각하면 된다.  (여기서 &은 문자열을 합쳐주는 연산자다)

  즉 range에 있는 값이 N열에 있는 기준값 보다 큰경우 criteria를 만족시키게 되어 합의 대상이 된다.

 - 이 예제에는 sum_range를 지정하지 않았는데, sum_range가 생략될 경우 range가 사용되게 된다.

 

 

(3) 날짜 비교

이번에는 특정 날짜 조건을 만족하는 값만 합산해보자.

 

9월 22일 또는 이전에 발생한 매출액만 합하고 싶다.

- range: 날짜를 비교할 예정이므로, B열을 지정한다.

- sum_range: 날짜를 비교하지만 매출액의 합을 원하므로 C열을 지정한다.

- criteria 날짜가 9월 22일 또는 이전일 경우만 합하고 싶기 때문에, "<="&N9으로 지정했다. (여기서 &은 문자열을 합쳐주는 연산자다)

 

9월 12일 이후에 발생한 데이터만  합하고 싶다.

- range: 날짜를 비교할 예정이므로, B열을 지정한다.

- sum_range: 날짜를 비교하지만 매출액의 합을 원하므로 C열을 지정한다.

- criteria 날짜가 9월 12일 이후일 경우만 합하고 싶기 때문에, ">"&N10으로 지정했다. (여기서 &은 문자열을 합쳐주는 연산자다)

 

실행 결과는 다음과 같다.

 

02. SUMIFS 함수

 

SUMIF 함수를 사용하다보면 이러한 의문이 들 수 있다. 만약 조사하고 싶은 조건이 2개 이상이라면 어떤방식으로 처리해야될까? 

 

단순히 값이 일치할 경우만 합산하는 것이라면, 조건이 2개 이상이어도 아래의 예제처럼 아이디를 제작하여 계산하는 것이 가능하다.

https://eggdrop.tistory.com/38

Excel [지식인 해결 시리즈] 007. 상반기/하반기 매출 집계 (SUMIF)

 

하지만 "=" 외의 다른 연산자를 사용해야되면 위의 방식으로 처리하는 것은 불가능하다.

 

이럴 때 사용할 수 있는 함수는 SUMIFS 함수이다.

SUMIF와 동작은 유사하지만, Criteria를 여러개 추가할 수 있다는 장점이 있다.

 

 

 

예를 들어 SUMIF 에서 사용한 데이터에서 기업명/반기별 매출을 합산하고 싶다고 가정해보자.

 

위와 같은 방식으로 사용할 수 있다.

파라미터부터 알아보자.

 - sum_range: SUMIF의 동명 파라미터와 마찬가지로 합이 수행될 데이터가 있는 영역을 나타낸다.

 - criteria_range1: 첫번째  criteria가 조사될 영역을 의미한다. SUMIF의 range와 유사하다고 볼 수 있다.

 - criteria1: 첫번째 criteria를 나타낸다.

 - criteria_range2: 두번째  criteria가 조사될 영역을 의미한다. SUMIF의 range와 유사하다고 볼 수 있다.

 - criteria2: 두번째 criteria를 나타낸다. 

 

 *criteria_range와 criteria는 계속 추가가 가능하며, 주의할 점은 criteria_range의 경우 sum_range와 크기가 같아야 한다는 점이다. 

 

다시 예제로 돌아가서

  - sum_range는 매출액이 있는 C열로 지정한다.

  - criteria_range1은 기업명이 있는 A열로 지정한다.

  - criteria1은 조사하고자 하는 기업명이 있는 F열의 셀로 지정

  - criteria_range2는 반기 정보가 있는  D열로 지정한다.

  - criteria2은 조사하고자 하는 반기정보(상반기/하반기)가  있는 G열의 셀로 지정 

 

실행 결과는 다음과 같다.

 

 

이번에는 날짜에 두가지 조건을 걸어보자. 9월 22일 보다 이전, 9월 12일 이후인 날짜에 대한 매출합

 

  - sum_range는 매출액이 있는 C열로 지정한다.

  - criteria_range1은 매출일이 있는 B열로 지정한다.

  - criteria1은 "<"&N9으로 지정하여 9월 22일보다 날짜가 빠른 경우의 셀로 지정한다.

  - criteria_range2은 매출일이 있는 B열로 지정한다.

  - criteria2은 ">"&N10으로 지정하여 9월 10일보다 날짜가 큰 경우의 셀로 지정한다.

  criteria1과 criteria2를 모두 만족할 경우에만 합산을 수행하기 때문에 결국, 9월10일 이후, 9월 22일 이전에 발생한  매출만 집계된다.

 

03. COUNTIF 함수

SUMIF 함수가 조건에 맞는 데이터를 합하느 목적이었다면, COUNTIF 함수는 조건에 맞는 데이터의 수를 세는 기능을 한다.

 

(1) COUNTIF의 파라미터

  - range: 조사할 영역

  - criteria: 조사할 조건

 criteria의 경우 SUMIF와 동일하기 때문에 자세한 설명을 생략한다.

 

바로 예제로 넘어가서 매출액이 10,000 보다 작은 데이터의 수를 구한다고하면 위의 그림처럼 수식을 지정할 수 있다.

 - range: 매출액이 있는 C열로 지정한다.

 - criteria: "<10000"을 입력하여  C열의 셀 값이 10000 보다 작은 데이터의 수만 카운트한다.

 

04. COUNTIFS 함수

 SUMIFS와 마찬가지로 criteria을 여러개 지정할 수 있는 COUNTIFS 함수도 있다.

 

(1)  COUNTIFS의 파라미터

- criteria_range1: 첫번째 조건으로 조사할 영역

- criteria1: 첫번째 조건

- criteria_range2: 두번째 조건으로 조사할 영역

- criteria2: 두번째 조건

criteria에 들어갈 값의 사용법은 SUMIF, SUMIFS, COUNTIF와 동일함

 

다음은 매출액이 10,000 보다 작고, 기업명이 삼성이 데이터의 수를 구하는 예제이다.

 

- criteria_range1: 매출액이 저장되어 있는 C열

- criteria1: "<10000" 10000보다 작음을 나타냄

- criteria_range2: 기업명이 저장되어 있는 A열

- criteria2: 기업명이 "삼성"인 경우를 나타냄

 

실행 결과는 위의 그림과 같다.

 

본 포스트에서는 통계에 유용한 SUMIF, SUMIFS, COUNTIF, COUNTIFS 소개했다. 위의 함수들을 활용하여 통계작업을 좀 더 쉽게 해보자.