엑셀이 편해지는 SUMIF 함수 실전 활용법 3가지
SUMIF 함수 활용법 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [실무기초] 엑셀 SUMIF 함수, 실무자를 위한 3가지 활용법예제파일
초보자를 위한 SUMIF 꿀팁 2가지
엑셀에서 VLOOKUP과 함께 직장인이 가장 많이 쓰는 대표 함수, 바로 SUMIF 함수입니다. 단순히 조건에 맞는 합계를 구하는 간단한 함수이지만, 의외로 많은 실무자들이 기본적인 사용 과정에서 치명적인 실수를 저지르곤 합니다. 이번 첫번째 챕터에서는 실무자가 꼭 알아야 할 SUMIF 함수 필수 꿀팁 2가지를 알아보겠습니다.
- 먼저 예제파일에 [기초]시트에서 각 제품별 수량의 합계를 계산해보겠습니다. H8셀에 다음과 같이 SUMIF 함수를 작성합니다.
=SUMIF(C8:C24,G8,E8:E24)
SUMIF 함수로 제품별 수량 합계를 계산합니다. - 함수를 실행하면 A세트의 수량 합계로 108이 계산됩니다.
수량 합계가 계산됩니다. - 실무에서는 이렇게 작성한 SUMIF 함수를 자동채우기해서 사용하게 됩니다. 따라서 작성한 수식에서 참조하는 범위는 절대참조 형식으로 작성하는 것이 안전합니다. 수식에서 범위로 참조한 C8:C24, E8:E24 범위는 F4를 눌러 절대참조 형식으로 변경한 후, 수식을 아래로 자동채우기하면 각 제품별 수량 합계가 올바르게 계산됩니다.
SUMIF 함수에서 참조하는 범위는 절대참조로 입력합니다. - 이렇게 SUMIF 함수로 작성한 보고서는 간혹 '오탈자'나 '보이지 않는 공백'으로 인해 잘못 계산되는 경우가 있습니다. 따라서, 보고서를 제출하기 전 검토하는 과정을 반드시 거쳐야 합니다. SUMIF 함수로 집계한 범위를 선택하면, 엑셀 화면 좌측 아래 상태표시줄에 합계로 308이 계산됩니다.
보고서를 제출하기 전, 상태표시줄에서 합계를 검토합니다. - 이제 원본 데이터에서 수량이 입력된 E열을 선택해 합계를 검토합니다. 그러면 원본 데이터의 합계도 308로 올바르게 계산된 것을 확인할 수 있습니다.
원본데이터와 일치하는 것을 확인합니다.
SUMIF 여러 항목 합계 구하기 (OR조건)
실무에서 SUMIF 함수를 사용하면서 가장 첫번째로 부딪히는 난관은 바로 '여러 항목의 합계'를 구하는 것입니다. 예를 들어, 지역 범위에서 "서울, 제주, 세종 지역의 매출 합계"를 구해야 할 때 일반적인 SUMIFS 함수로는 집계가 어려워, 여러 개의 SUMIF 덧셈으로 계산하곤 합니다. 그럴 때, SUMPRODUCT 함수를 활용해 여러 항목 합계를 편리하게 구할 수 있습니다.
- 예제파일에서 [다중SUMIF] 시트로 이동합니다. 먼저 I8셀에는 지역이 '서울'이면서 제품이 'C세트'인 경우 수량 합계를 계산해보겠습니다. I8셀에 다음과 같이 SUMIFS 함수를 작성합니다. 함수를 실행하면 합계로 9가 계산됩니다.
=SUMIFS(E8:E24,B8:B24,G8,C8:C24,H8)
SUMIFS 함수로 여러 조건을 만족하는 합계를 구합니다. - SUMIFS 함수는 입력한 모든 조건을 동시에 만족하는 경우의 합계를 구합니다. 따라서, 밑에 있는 여러 지역의 합계의 경우, 지역이 "서울, 제주, 세종"을 동시에 만족할 수 없으므로 일반적인 SUMIFS 함수를 작성하면 합계로 0이 계산됩니다.
SUMIFS 함수는 모든 조건을 동시에 만족하는 경우의 합계를 구합니다. - 그럴 때, SUMPRODUCT 함수를 활용해 여러 항목의 합계를 편리하게 계산할 수 있습니다. I13셀에 다음과 같이 SUMPRODUCT + SUMIF 수식을 작성합니다.
=SUMPRODUCT(SUMIF(B8:B24,G13:G15,E8:E24))
SUMPRODUCT 함수로 여러 항목 합계를 구할 수 있습니다. - 함수를 실행하면 목록에 작성된 모든 지역의 합계가 한 번에 계산됩니다.
여러 항목의 SUMIF 합계가 계산됩니다.
월별 합계 쉽게 구하는 방법
SUMIF 함수로 날짜 데이터를 다루다보면, 특정 년도나 특정 월의 합계를 구해야 할 때가 많습니다. 실무에서는 주로 원본데이터 옆에 MONTH 함수로 월을 추출하는 보조열을 추가해 합계를 구하지만, SUMIFS 함수를 활용하면 보조 열 없이 월별 합계를 편리하게 구할 수 있습니다.
- 예제파일에서 [월별합계] 시트로 이동한 후, H8셀에 SUMIFS 함수를 작성합니다. SUMIFS 함수로 월별 합계를 구할 때는, 선택한 월의 '시작일'과 '말일'을 기준으로 합계를 구합니다. 따라서, H8셀에 다음과 같이 DATE 함수를 활용해 SUMIFS 함수 공식을 작성합니다.
=SUMIFS($E$8:$E$24,$D$8:$D$24,">="&DATE(2025,G8,1),D8:D24,"<="&DATE(2025,G8+1,0))
'DATE 함수의 일로 0을 입력하면, 이전 월의 말일을 반환합니다.
월별 합계를 구할 땐, 시작일과 말일 사이의 합계로 구합니다. - 작성한 수식을 아래로 자동채우기하면 월별 합계가 한 번에 계산됩니다.
수식을 자동채우기하면 월별 합계가 한 번에 계산됩니다.
SUMIF 자동 보고서 만들기 (2021 이후)
만약 엑셀 2021 이후 버전을 사용 중이라면 UNIQUE 함수를 활용해 SUMIF 자동 보고서를 만들 수 있습니다. M365 최신 버전을 사용중이라면 GROUPBY 함수를 사용할 수도 있는데요. GROUPBY 함수의 더 자세한 사용법은 아래 영상에서 알기 쉽게 정리했으니 확인해보시길 바랍니다.
- 예제파일의 [자동보고서] 시트에서 G7셀에 UNIQUE 함수를 다음과 같이 입력합니다. 함수를 실행하면 지역의 고유목록이 반환됩니다.
UNIQUE 함수로 SUMIF 함수의 조건 목록을 출력합니다. - 엑셀 2021 이후 버전부터는 '동적 배열'이 제공되면서, 해시(#) 기호를 활용한 함수 자동화 보고서를 편리하게 만들 수 있게 되었습니다. H7셀에는 다음과 같이 SUMIF 함수를 작성합니다.
=SUMIF(B7:B23,G7#,E7:E23)
해시(#) 기호로 조건 범위를 동적으로 참조합니다. 오빠두Tip : 해시(#) 기호를 활용한 분산 범위의 더 다양한 예제는 아래 기초 입문 강의를 참고하세요!👇
- 함수를 실행하면 모든 지역의 수량 합계가 한 번에 계산됩니다. 이제 지역의 데이터를 변경하면 보고서가 실시간으로 업데이트 되는 것을 확인할 수 있습니다.
SUMIF 함수 자동 집계 보고서가 완성됩니다.
- 먼저 예제파일에 [기초]시트에서 각 제품별 수량의 합계를 계산해보겠습니다. H8셀에 다음과 같이 SUMIF 함수를 작성합니다.
