SUMIF 함수는 SUM 함수와 IF함수의 기능을 합했다고 보시면 됩니다.
즉, 자료에서 전체의 합을 나타내는 SUM 함수의 기능과
조건값에 불/부합을 판단하는 IF 함수의 기능이 결합되어
IF 함수의 조건에 부합되는, 기준값과 동일하거나 참값에 해당하는 셀값들의 합을 구하는 함수입니다.
1. SUMIF 함수
가. 수식 : =SUMIF(range, criteria, [sum_range])
나. 구성
range : 기준값 또는 조건값(criteria)과 일치하거나 참값이 되는지 비교할 영역
criteria : '기준'이라는 뜻으로, range 와 비교하여 참값을 추출하게 되는 기준값
> range 영역의 셀들과 critera값을 비교하게 된다.
[sum_range] : range 영역 안에서 criteria와 비교하여 참값에 해당하는 셀과 동일한 행 또는 대응하는 셀 영역
> 결과값으로 이 영역에서 기준값에 해당하는 셀들의 값을 합하게 된다.
다. 설명
range와 criteria를 비교하여 해당하는 [sum_range]영역의 셀값을 합하는 수식
[예시] 아래는 스타*스 매장의 9월 24일 판매된 음료 현황입니다.
이 매장에서의 판매량 중에서 "에스프레소" 제품군의 판매량을 알아보도록 하겠습니다.
그냥 전체 합계를 구하려면 SUM 함수만 쓰면 되겠지만, "에스프레소"라는 제품군에 해당하는 상품의 판매량만 알고 싶은, '조건'이란게 생긴 것입니다.
앞서 설명드린 SUMIF 함수의 3가지 구성을 염두에 두시고 수식을 작성하면...
range는 '구분'이 됩니다. 해서 영역설정을 B5:B25 입니다.
criteria는 range 안에서 비교할 기준값(에스프레소의 판매량만 확인할꺼니깐)이므로 "에스프레소"가 됩니다.
엑셀에서 text값을 인식 시키기 위해서는 ""(큰따옴표)를 써야하기 때문에 에스프레소라는 단어 양옆으로 큰따옴표로 표시합니다.
[sum_range]는 최종 결과값으로, 판매량에 해당하는 영역인 I5:I25 이며, 이 중에서 "에스프레소"에 해당하는 상품의 판매량만 합(SUM)하게 됩니다.
※ 주 : 이때, range와 [sum_range]의 범위 수준은 동일해야 합니다. range의 영역이 세로로 21행(5행~25행)이므로, [sum_range]의 영역 역시 세로로 21행(5행~25행)이어야 합니다.
최종 수식으로, =SUMIF(B5:B25,"에스프레소",I5:I25)가 되어, 에스프레소 상품군에 해당하는 상품의 판매량은 104개가 됩니다.
두번째 케이스로 설명을 이어 가겠습니다.
첫번째의 경우는 조건값에 해당하는 내용이 '상품군' 중에서 '에스프레소'의 판매량을 구하였습니다.
즉, '상품군'='에스프레소'인 조건에 해당되는 것이지요.
두번째의 케이스로 정확하게 딱 맞는 조건이 아니라 ~이상, ~이하 와 같이 범위를 조건으로 하는 경우를 알아보겠습니다.
- 문제 : 스타*스 판매 자료에서 5,000원 이상의 음료수의 판매량은 얼마인가요?
자, 이 경우의 SUMIF 함수에 들어갈 항목들을 생각해봅니다.
range는 '가격'이 되기 때문에, H5:H25 입니다.
criteria는 '5000원 이상' 이라는 조건인데, 이때 부등호를 사용하여 ">=5000" 이 됩니다.
(부등호 사용 시에도 역시 큰따옴표를 사용해야 합니다.)
[sum_range]는 '판매량'이 되기 때문에, I5:I25 입니다.
최종 수식으로는 =SUMIF(H5:H25,">=5000",I5:I25)가 되어, 5,000원 이상 상품의 판매량은 177개가 됩니다.
이렇게 SUMIF 함수는 특정한 조건값을 주고, 해당 조건에 부합하는 행의 원하는 값을 모두 합하는 로직의 함수 입니다.
2. SUMIFS 함수
가. 수식 : =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)
나. 구성
sum_range : 각 criteria_range 영역안에서 criteria와 비교하여 모두 참값에 해당하는 셀과 동일한 행 또는 대응하는 셀 영역
> 결과값으로 이 영역에서 기준값들에 해당하는 셀들의 값을 합하게 된다.
criteria_range1, 2, ... : 기준값 또는 조건값(criteria1, 2, ...)과 일치하거나 참값이 되는지 비교할 영역
criteria1, 2, ... : '기준'이라는 뜻으로, criteria_range 와 비교하여 참값을 추출하게 되는 기준값
> criteira_range1 영역의 셀들과 critera1값을 비교, criteira_range2 영역의 셀들과 critera2값을 비교, ...
다. 설명
각 range와 criteria를 비교하여, 모든 조건에 부합하는 셀들의 대응하는 sum_range영역의 셀값을 합하는 수식
SUMIFS 함수는 SUMIF 함수의 다중 조건 함수라고 할수 있습니다.
SUMIF 함수의 경우, 조건이 1개인 경우에 사용되나, SUMIFS 함수는 조건이 1개 이상의 경우에 사용할 수 있습니다.
※ SUMIF 함수와 SUMIFS 함수의 수식에 들어가는 항목(index)의 순서에 유념하여야 합니다.
= SUMIF(조건영역, 조건, 추출영역)
= SUMIFS(추출영역, 조건영역1, 조건1, 조건영역2, 조건2...)
위에서 보다시피, 최종 원하는 결과값의 영역(sum_range)이 SUMIF의 경우 제일 마지막 index로 위치하나, SUMIFS 함수의 경우는 제일 앞에 위치합니다.
[예시] 마찬가지 스타*스의 판매량 자료를 기준으로, '칼로리가 100kcal 이상의 음료 중, 20개 이상 팔린 상품의 매출 금액'을 구해 보도록 하겠습니다. 이때, 조건은 2가지가 됩니다.
- 조건1 : 칼로리가 100Kcal 이상 음료
- 조건2 : 판매량 20개 이상
- 결과값 : 매출 금액
SUMIFS 함수의 구성하는 index를 고려하면서 보면,
sum_range는 최종 결과값의 영역이기에 J5:J25 가 됩니다.
첫번째 조건과 비교할 영역인 criteria_range1은 칼로리 자료가 있는 D5:D25 입니다.
이 첫번째 조건으로 100Kcal 이상의 의미로 criteria1은 ">=100" 입니다.
두번째 조건과 비교할 영역인 criteria_range2는 판매 수량 자료가 있는 I5:I25 가 됩니다.
이 두번째 조건으로 판매량 20개 이상의 의미로 criteria2는 ">=20" 입니다.
따라서, 최종적으로 수식은 =SUMIFS(J5:J25,D5:D25,">=100",I5:I25,">=20") 이며,
100Kcal 이상의 음료 중 20개 이상 판매된 상품의 매출 금액은 825,200원입니다.
ⅰ. 정리
1. SUMIF 함수 : 1가지 조건에 부합하는 셀들의 상호 대응하는 셀 값 모두 합산하는 함수
2. SUMIFS 함수 : 1가지 이상(다중) 조건에 부합하는 셀들의 상호 대응하는 셀 값 모두 합산하는 함수
ⅱ. 연습해보기 : 문제는 유첨 파일안에 있습니다.
'엑셀(excel)' 카테고리의 다른 글
| 엑셀 점수 등급 매기기 (feat. lookup) (1) | 2022.07.21 |
|---|---|
| 엑셀 다중 선택 데이터 유효성 검사 (feat. indirect) (0) | 2022.07.18 |
| 엑셀 SUM 함수와 SUMPRODUCT 함수 (0) | 2019.09.24 |
| 엑셀 COUNTIF 함수와 COUNTIFS 함수 (2) | 2019.09.01 |
| 엑셀 COUNT 함수와 친구들 (1) | 2019.08.31 |
