~IF 함수 (엑셀 함수 - COUNTIF, SUMIF, AVERAGEIF)

COUNTIF, SUMIF, AVERAGEIF 함수에 대해 알아보겠습니다. 먼저 함수명을 보시면 동일하게 ~IF라고 들어갑니다.

그리고 이미 COUNT, COUNTA, SUM, AVERAGE에 대해서는 알아 보았습니다. 각 함수에 IF만 붙은 것이죠 그리고 IF도 몇 차레 다루었습니다. 만약 COUNT, COUNTA, SUM, AVERAGE 함수를 모르신다면 이전 강좌를 보시고 다시 봐 주세요

 

일단 기본 함수들의 구문을 보겠습니다.

함수	구문
COUNT	COUNT(value1, [value2], ...)
COUNTA	COUNTA(value1, [value2], ...)
SUM	SUM(number1,[number2],...)
AVERAGE	AVERAGE(number1, [number2], ...)

COUNT와 COUNTA함수의 구문은 동일하고 SUM과 AVERAGE함수도 구문 또한 동일합니다. 그리고 이 4개 함수의 인수는 value~이거나 number~이거나 모두 범위를 참조해서 사용했습니다. 물론 number~의 경우 숫자값만을 가진 셀을 범위로 참조한다는 점이 다른 점이기는 하나 범위라는 점에서는 동일합니다. 즉 인수로 범위를 참조한다는 공통점이 있습니다.

 

다시 정리하면

COUNT는 숫자 값을 가진 셀 범위의 개수를

COUNTA는 비어있지 않은 셀 범위의 개수를

SUM은 숫자 값을 가진 셀 범위의 합을

AVERAGE는 숫자 값을 가진 셀 범위의 평균을

계산하여 계산해 주는 함수들이었습니다.

 

그리고 IF는 조건에 따라 참과 거짓을 처리하는 함수였습니다.

그래서 IF와 함께 위의 함수를 다시 정리하면

COUNTIF는 어떤 셀 범위 안에 같은 조건을 가진 개수를

SUMIF는 어떤 셀 범위 안에 같은 조건을 가진 값들의 합을

AVERAGEIF는 어떤 셀 범위 안에 같은 조건을 가진 값들의 평균을 계산해준다라고 생각하시면 정리가 쉬우리라 생각됩니다.

 

COUNTIF

기준을 충족하는 셀의 개수를 계산

COUNTIF(range, criteria)

• range - 숫자, 배열, 이름이 지정된 범위 또는 숫자를 포함하는 참조 영역
• criteria - range에서 찾고자하는 조건
  (">32"와 같은 비교 또는  "사과"와 같은 단어)

SUMIF

지정한 조건을 충족하는 범위 값을 합산합니다

SUMIF(range, criteria, [sum_range])

• range - 조건 범위 숫자, 배열, 이름이 지정된 범위 또는 숫자를 포함하는 참조 영역
• criteria - range에서 찾고자하는 조건(숫자, 식, 셀 참조, 텍스트 또는 함수 형식의 조건)
• [sum_range] - 실제 합칠 값의 범위로 range와 동일하다면 생략가능
  (주의 : range의 범위와 동일한 모양과 크기로 지정해야 함)

AVERAGEIF

지정한 조건을 충족하는 범위 값의 평균을 계산합니다.

AVERAGEIF(range, criteria, [average_range])

• range - 조건 범위 숫자, 배열, 이름이 지정된 범위 또는 숫자를 포함하는 참조 영역
• criteria - range에서 찾고자하는 조건(숫자, 식, 셀 참조, 텍스트 또는 함수 형식의 조건)
• [average_range] - 실제 평균으로 계산할 값의 범위로 range와 동일하다면 생략가능
  (주의 : range의 범위와 동일한 모양과 크기로 지정해야 함)

 

함수 사용법은 위와 같습니다. 간단하게 IF가 없던 기본함수 COUNTA, SUM, AVERAGE에 IF가 붙는다면, 구문은 조건 범위에 해당하는 인수 range와 range에서 찾고자 하는 조건인 criteria가 추가 되는 것이죠

COUNTIF의 경우는 합이나 평균처럼 어떤 계산을 수행하는 것이 아니고 조건의 개수만 세는 것이기 때문에 [sum_range]나 [average_range]와 같은 범위가 불필요한 것이고요.

SUMIF와 AVERAGEIF의 경우에도 range와 [sum_range](합칠 값의 범위) 또는

range와 [average_range](평균 계산할 범위)가 같다면 생략할 수 있습니다.

 

최종으로 정리하자면

함수(범위) => 함수IF(조건범위, 조건,범위)

원래 함수(범위)로 사용하던것에  IF를 붙이고 첫번째 인수를 조건범위로, 두번째 인수를 조건으로 사용했다 보시면 무방하리가 생각됩니다.

 

반응형

 

이제 아래 예제 파일을 보겠습니다.

 

이전 강좌에서 지역과 대분류를 데이터 유효성 검사를 통해 목록화한 표입니다. (F열인 제품명은 숨기기 했습니다.)

 

먼저 전체의 주문 개수를 구하겠습니다. 주문 개수는 주문일, 고객명, 지역, 대분류, 수량, 금액 중 어느 것을 범위로 잡아도 사실 상관 없습니다. 필요한 것을 행의 수 이기때문에 조건이 없는 COUNTA를 사용한다면 지금과 같은 내역서에서는 어느것이라도 상관없습니다. 저는 고객명으로 해 보겠습니다.

 

1. K11셀 선택
2. 수식입력줄에 함수작성 => =COUNTA(
3. 범위 선택. C5:C60(C5셀 클릭  > ctrl+shift+↓(아래방향키) ) => =COUNTA(C5:C60
4. ENTER로 작성 완료 => =COUNTA(C5:C60)

다음 전체 금액 합

 

1. K12셀 선택
2. 수식입력줄에 함수작성 => =SUM(
3. 범위 선택. H5:H60(H5셀 클릭  > ctrl+shift+↓(아래방향키) ) => =SUM(H5:H60
4. ENTER로 작성 완료 => =SUM(H5:H60)

다음 전체 금액 평균은 금액 합과 완전히 동일하되 함수명만 AVERAGE로 하시면 작성 완료됩니다.

 

이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.

 

 

이제 드디어 ~IF를 할 차례입니다.

 

1. 함수를 작성할 L11셀 선택
2. 수식입력줄에 함수작성 => =COUNTIF(
3. 조건 범위인 첫번째 인수 range 지정(D5셀 클릭  > ctrl+shift+↓(아래방향키) )  => =COUNTIF(D5:D60
4. 콤마 입력후 range 범위에서 조건이 될 두번째 인수 criteria 선택 => =COUNTIF(D5:D60,L10
5. ENTER로 작성 완료 => =COUNTIF(D5:D60,L10)

서울 지역의 주문 개수가 계산되어 반환됩니다.

다음 서울 지역 금액 합

 

1. 함수를 작성할 L12셀 선택
2. 수식입력줄에 함수작성 => =SUMIF(
3. 조건 범위인 첫번째 인수 range 지정(D5셀 클릭  > ctrl+shift+↓(아래방향키) ) => =SUMIF(D5:D60
4. 콤마 입력후 range 범위에서 조건이 될 두번째 인수 criteria 선택 => =SUMIF(D5:D60,L10
5. 합칠 값 범위인 sum_range 범위 지정(H5셀 클릭  > ctrl+shift+↓(아래방향키) ) => =SUMIF(D5:D60,L10,H5:H60
6. ENTER로 작성완료 => =SUMIF(D5:D60,L10,H5:H60)

서울 지역의 금액 합이 계산되었습니다.

서울 지역 금액 평균은 SUMIF완 완전히 동일한 방법으로 하면됩니다. 함수명망 AVERAGEIF가 되겠지요

수식만 확인 하겠습니다. L13셀을 선택후

=AVERAGEIF(D5:D60,L10,H5:H60)

작성하면 서울 지역의 주문 개수, 금액 합, 금액 평균을 함수로 계산했습니다.

 

 

그리고 L10셀은 데이터 유효성 검사로 목록화 했으니 마우스로 선택하여 지역을 변경하면 변경된 지역의 주문 개수, 금액 합, 금액평균이 자동 계산되어 확인 할 수 있습니다.

 

이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.

 

 

사무용품도 동일한 방법으로 구할 수 있습니다. 

range 범위와, criteria번위만 달라지면 됩니다. 수식만 확인 하겠습니다.

 

M11 셀 => =COUNTIF(E5:E60,M10)

M12 셀 => =SUMIF(E5:E60,M10,H5:H60)

M13 셀 => =AVERAGEIF(E5:E60,M10,H5:H60)

 

그리고 K11:M13 셀 범위를 선택하고 홈 탭 > 표시 형식의 ,(콤마 버튼)을 클릭해서 정수 천단위 구분기호를 서식에 적용하고 글자 크기를 9로 바꿉니다. 결과

 

L10셀과 M10셀을 변경하면서 지역별 또는 대분류 별로 주문 개수, 금액 합, 금액 평균의 계산을 확인 할 수 있습니다.

 

 

countif,sumif,averageif.xlsx

0.01MB