[컴활 2급] 엑셀 SUMIFS 함수로 다중 조건 합 구하기

컴퓨터활용능력 2급 실기를 위해 익혀야할 엑셀 함수 SUMIFS 함수입니다. 대한상공회의소의 컴퓨터활용능력 2012년 개정에 추가된 SUMIFS 함수는 마이크로소프트 오피스 엑셀 2007 에 추가된 함수 이기도 합니다. 하여 이전 버전의 엑셀 버전에서는 사용할 수 없는 함수입니다. 이 SUMIFS 함수와 비슷한 함수로 SUMIF 함수가 있습니다. 모두 원하는 조건을 찾아 원하는 범위의 합을 구하는 함수입니다. SUMIFS 함수가 달라진 점이라면 검색조건을 다양하게 설정할 수 있다는 것입니다. 또 이 함수는 텍스트 검색시 와일드 카드의 사용이 가능합니다.

SUMIFS 함수 기본 설명

설명 : SUMIFS 함수는 합을 구할 범위 ( sum_range ) 의 합을 구합니다. 단, 조건범위 ( criteria_range1 ) 에서 그 조건 ( criteria1 ) 을 만족하는 셀들의 합계만을 구합니다.
구문 : SUMIFS ( sum_range , criteria_range1 , criteria1 , [criteria_range2] , [criteria2] , .... )
인수 :
    sum_range : 합계를 구할 수치값의 범위 혹은 수치배열입니다. 조건이 만족할 경우 합계로 계산됩니다.
    criteria_range1 : 검색할 범위 혹은 배열 입니다.
    criteria1 : 검색조건 입니다. 수치값, 텍스트, 조건수식 이 가능합니다.
    [criteria_range2] : 검색할 범위2 혹은 배열 입니다. 옵션인수로 사용됩니다.
    [criteria2] : 검색조건2 입니다. 옵션인수 입니다.
   
    노트1 : 검색범위 와 검색조건 은 127개의 쌍을 지정할 수 있습니다.

노트2 : SUMIFS 함수는 대소문자를 구분하지 않습니다.
참고1 : 인수로 한글을 사용치않고 영문을 쓰는 것은 이 인수명들이 엑셀의 함수식에서 그대로 표시되기 때문입니다. 이를 익혀 두시면 함수를 익히고 사용하시기가 편리해 지실 것입니다.

 

 

SUMIFS 함수 사용예제

[그림1] 이를 참고하여 아래의 사용예제를 살펴보세요.

사용 예제1 : =SUMIFS( A4:A8, B4:B8, "경양" )
사용 결과1 : 400
B4:B8의 업체들 중에 "경양" 을 찾아 A4:A8 의 직원수의 합을 구합니다. 지금은 "경양" 이란 업체가 1개 뿐이라 400 이란 결과가 바로 보이네요.

사용 예제2 : =SUMIFS( A4:A8, A4:A8, ">100" )
사용 결과2 : 765
A4:A8 의 criteria_range1 에서 검색조건( criteria1 ) 이 100 을 초과하는 A4:A8 의 합계범위 ( sum_range ) 의 합을 구합니다. 쉽게 표현하면 직원수가 100명을 넘는 셀들의 직원수 합을 구합니다.

노트3 : 위에서처럼 검색조건으로 사용되는 조건식은 SUMIF 함수에서처럼 문자열 형태로 표현됩니다.

사용 예제3 : =SUMIFS( A4:A8, B4:B8, "경*" )
사용 결과3 : 645
조건범위 ( criteria_range1  ) B4:B8 의 영역에서 '경' 으로 시작하는 문자열 ( "경*" ) 을 찾아 동일 행의 A4:A8 , 합계범위 ( sum_range ) 의 합을 구합니다. 이번 사용예제 에서는 와일드카드 문자가 사용되었습니다.

노트4 : 와일드카드문자 * 은 모든 문자를 대신합니다. ? 은 한 문자를 대신하여 표현됩니다.

사용 예제4 : =SUMIFS( A4:A8, A4:A8, "<150", C4:C8, "전라" )
사용 결과4 : 180
A4:A8 의 조건범위에서 150 미만 이면서 C4:C8 의 조건범위가 "전라" 인 A4:A8 의 합계를 구합니다. 이렇게 다중 조건을 설정할 수 있는 것이 SUMIFS 함수의 장점입니다.

문제1 : '행정구역' 이 "북도" 로 끝나고 시의 수가 7개 미만인 시의 합을 구하라.

[그림2] SUMIFS 함수 문제1 풀이

함수식 : =SUMIFS(B4:B12, A4:A12, "*북도", B4:B12, "<7")
문제1의 결과 : 9

B4:B12 의 합계를 구합니다. 조건이 있습니다. A4:A12 의 '행정구역' 범위에서 "북도" 로 끝나고 B4:B12 의 '시의 수' 가 "<7" 셀들의 합계를 구합니다.

문제2 : 2015년도 영화 중 최고순위  3위 이하의 영화의 전 세계 수익의 합계를 구하라. (직접 풀어보세요)

[그림3] SUMIFS 함수 문제2

함수식 : =SUMIFS(D2:D16, E2:E16, 2015, B2:B16, "<=3" )
문제2의 결과 : $3,615

문제2 엑셀 파일 :

SUMIFS함수_문제2.xlsx

다운로드

 

SUMIFS 함수 요약

- 원하는 조건이나 데이터를 찾아 합계를 구하는 함수.
- 다양한 조건 범위 설정 가능.
- 와일드카드 사용가능.
- 대소문자 구분하지 않음.
- 검색조건은 문자열로 사용. 검색값은 수치입력가능.

SUMIF 함수와 수식을 비교해 드립니다.

SUMIF 함수 : SUMIF( range, criteria, [sum_range] )
SUMIFS 함수 : SUMIFS( sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )

보시듯이 SUMIF 함수의 단일 검색조건 에 비해 SUMIFS 함수는 여러개의 조건을 찾을 수 있으면 SUMIF 함수가 합계범위가 옵션 인수로 이가 생략되면 range 인수가 합계범위가 됨과는 달리 SUMIFS 함수는 합계범위가 분리 되어 있습니다. 동일한 의도로 사용되는 함수이기에 두 함수를 혼동하지 않도록 유의하시기 바랍니다.

이렇게 컴활2급 실기 출제기준에 포함된 엑셀의 수학삼각함수 SUMIFS 함수를 살펴 보았습니다. 또 하나의 함수를 익히고 컴활 합격으로 천천히 걸어가고 있습니다.

마늘빵.