엑셀 SUMIFS 함수는 다중 조건 (1개 이상)에 해당하는 데이터의 합계를 구하는 함수로서, 실무에서 가장 많이 쓰이는 함수 중 하나로 알려져 있습니다.
SUMIF 대신 SUMIFS를 써야하는 이유와 예제를 통해서 엑셀 SUMIFS 함수에 대해서 알아보겠습니다.
1. 엑셀 SUMIFS 함수란
엑셀 SUMIFS 함수는 1개 이상의 조건들에 해당하는 합계를 구하는 함수입니다.
엑셀에서 업체들의 매출 합계, 날짜 합계, 제품별 합계 등 을 구할 수 있는 조건부 합계 함수 입니다.
SUMIFS 함수는 SUM 함수와 IFS 함수가 합쳐진 함수입니다.
SUM은 합계를 구하는 함수이고
IFS는 다중조건에 해당하는 참값을 나타내는 함수입니다.
1-1 SUMIFS 함수 인수 설명
SUMIFS는 필수인수 3개로 이루어진 함수입니다.
- SUM_RANGE : 숫자들이 있는 범위 (열)를 설정하는 인수입니다. 항상 COLUMN(열)으로 범위를 잡아야 합니다.
- CRITERIA_RANGE1 : CRITERIA가 있는 범위를 설정하는 인수입니다. 마찬가지로 항상 COLUMN(열)으로 범위를 잡아야합니다.
- CRITERIA1 : 합계를 구할 조건입니다.
여기서 중요한 점은 SUM_RANGE와 CRITERIA_RANGE의 범위는 일정해야 합니다.
예를 들면, SUM_RANGE는 A1:A10이고 CRITERIA_RANGE가 B1:B9이면 작동하지 않는 점을 주의해야 합니다.
또한 CRITERIA_RANGE와 CRITERIA는 항상 한 쌍으로 입력이 되어져야 합니다.
만약 CRITEIRA_RANGE2를 시작했으면 CRITERIA2 인수도 입력을 해야 함수가 작동을 합니다.
1-2 SUMIF 함수의 단점
SUMIF 함수보다 SUMIFS 함수가 더 유용한점은 SUMIF는 단일조건에 충족하는 합계를 구하는 반면에,
SUMIFS는 단일 조건 및 다중 조건에 해당하는 합계를 둘 다 구 할 수 있는 장점이 있습니다.
그래서, SUMIF를 사용하시는 것보다 SUMIFS를 더 활용하시는 걸 추천 드립니다. (찐 실무인 노하우)
SUMIF를 쓰다가 다중 조건의 합계를 구하려하면 SUMIF는 쓸 수 없게 됩니다.
그럼 SUMIFS로 바꾸려면 인수의 위치도 달라질 뿐더러 데이터 범위를 다시 잡아야 하는 번거로움을 굳이 감수할 필요가 없습니다.
처음부터 SUMIFS를 습관화 하시는게 좋습니다.
2. SUMIFS 함수 예제
SUMIFS 예제를 통해서 2가지 이상 다중조건의 합계를 구하는 방법에 대해서 알아보겠습니다.
위 이미지와 같은 데이터가 있습니다.
SUMIFS 함수를 사용해서 각 카페마다 제품별 판매된 수량의 합계를 구하려고 합니다.
첫 번째 조건은 카페명
두 번째 조건은 제품명
두 가지 조건에 충족하는 총 합계를 구해야 합니다.
2-1. SUMIFS 함수 첫 번째 조건 잡기
먼저 SUMIFS의 첫 번째 인수인
SUM_RANGE를 잡아야 합니다.
숫자가 있는 데이터는 날짜 열과 판매수량 열 입니다.
구하려고 하는 값은 판매 수량이기에 판매수량 범위를 첫 번째 인수로 잡아줍니다.
두번째 인수인 조건 범위는
JASPER카페와 JASPER카페2 데이터가 있는 카페명 범위를 두 번째 인수로 잡아줍니다.
세 번째 인수는 조건이기에 JASPER카페를 잡아줍니다.
중요!
1,2번째 인수는 범위를 잡아야하지만 3번쨰 인수는 하나의 셀 혹은 텍스트를 기준으로 잡아야 합니다.
텍스트를 입력할때는 항상 ” ” 큰따옴표를 씌워서 텍스트를 입력합니다.
셀을 직접 참조를 할 때는 셀을 클릭합니다.
입력된 함수는 다음과 같습니다.
=SUMIFS($H$4:$H$27,$F$4:$F$27,K$3)
첫 번째 인수 : $H$4:$H$27 (절대참조)
두 번째 인수 : $F$4:$F$27 (절대참조)
세 번째 인수 : K$3 (혼합참조)
범위의 위치가 따라서 내려오면 안되기에 절대참조(F4클릭)을 해서 고정을 시켜줍니다.
K$3을 해 놓은 이유는 JAPSER카페와 JASPER카페2의 값들이 오른쪽 왼쪽으로만 움직일 수 있게 4번 행에만 고정을 해 놓았습니다.
2-2. SUMIFS 함수 두 번째 조건 잡기
두 번째 조건은 제품명 입니다.
두 번째 조건 범위와 조건을 ,(콤마)를 찍어서 열어줍니다.
그럼 CRITERIA_RANGE2와 CRITERIA2 인수가 열립니다.
항상 한 쌍으로 입력이 되어져야 한다는 점을 주의해야 합니다.
입력된 수식은 다음과 같습니다.
=SUMIFS($H$4:$H$27,$F$4:$F$27,K$3,$G$4:$G$27,$J4)
첫 번째 인수 (합계 범위) : $H$4:$H$27 (절대참조)
두 번째 인수 (조건 범위1) : $F$4:$F$27 (절대참조)
세 번째 인수 (조건1) : K$3 (혼합참조)
네 번째 인수 (조건 범위2) : $G$4:$G$27 (절대참조)
다섯 번째 인수 (조건2) : $J4 (혼합참조)
를 통해서 SUMIFS 다중조건 수식이 완료 되었습니다.
만약 세가지 이상의 조건의 합계를 구하려면 조건범위3과 조건3을 열어서 범위와 조건을 입력해주면 됩니다.
판매수량을 잘 보여주기위해서 숫자를 1부터 나열을 했습니다.
JASPER카페에 (조건1) 해당하면서 동시에 아이스아메리카노 (조건2)에 해당하는 판매수량의 합계가 더해져서 입력된 걸 확인 할 수있습니다.
1+7+13+19 = 40 이 나옵니다.
수 많은 데이터의 매출의 합계나 다중 조건의 합계를 구 할 때는 위와 같은 방법으로 따라오시면 충분히 할 수 있습니다.
3. SUMIFS 함수 마무리
엑셀 SUMIFS 함수 사용법을 설명 드렸습니다.
처음에는 복잡 할 수 있습니다, 하지만 반복 풀이를 통해서 다른 파일에도 응용 할 수 있습니다.
문제를 한번 풀어보시라고 예제파일도 첨부하겠습니다. (아래 다운로드)
추후 SUMIFS를 활용한 두 날짜 사이 값의 합계를 구하거나 해당 날짜 이전 날의 합계를 구하는 함수도 올릴 예정입니다.
3-1. 알고 있으면 유용한 함수 및 기능들