엑셀 SUMIF와 SUMIFS로 조건별·월별 합계를 자동화하는 방법은?

TL;DR

조건 하나의 합계는 SUMIF, 여러 조건을 모두 만족하는 합계는 SUMIFS로 계산하며, 자동 채우기 전 데이터 범위를 절대참조로 고정해야 합니다. 여러 항목 중 하나에 해당하는 합계는 SUMPRODUCT와 SUMIF를 결합하고, 월별 합계는 해당 월의 첫날부터 말일까지를 조건으로 지정합니다. 엑셀 2021 이후에는 UNIQUE와 분산 범위 참조를 이용해 원본 변경을 반영하는 자동 보고서도 만들 수 있습니다.

Transcript

여러분 안녕하세요. 오빠도 엑셀 전진관입니다. 자, 엑셀에서 서유프 함수 많이 사용하시죠? 아마 브6컵 함수와 함께 실무에서 가장 많이 쓰는 대표 함수 중 하나일 텐데요. 뭐 이렇게 자주 쓰는만큼 오류도 종종 발생합니다. 그래서 오늘은 서빙프 함수를 사용하면서 직장인이 가장 많이 경는 대표적인 두 가지 요류의 해결 방법과 자동화 서식을 만드는 과정까지 모두 알아보겠습니다. [박수] 자, 먼저 서뮤 함수의 기초 사용법을 빠르게 알아보겠습니다.이 이 서뮤 함수는 특정 범위에서이 조건을 만족하는 값의 합기를 구합니다. 자, 그래서이 수량 밑에다가 서밋 함수를 입력하시고요. 먼저 조건 범위로는 제품이 입력된 범위를 선택합니다.... Read More

Key Insights

• SUMIF는 하나의 조건 범위에서 지정한 조건을 만족하는 행만 골라 대응하는 합계 범위의 값을 더하는 함수다.
• 자동 채우기를 사용할 때 조건 범위와 합계 범위를 F4 키로 절대참조로 고정하면 수식 복사 중 범위 이동을 막을 수 있다.
• 보고서 제출 전 계산 결과 범위와 원본 수량 범위를 각각 선택해 상태표시줄 합계가 모두 308인지 비교할 수 있다.
• SUMIFS는 서울이면서 C세트인 경우처럼 여러 조건을 동시에 만족하는 행의 수량 합계를 계산하며, 예시 결과는 9다.
• 같은 지역 열에 서울, 제주, 세종을 SUMIFS의 연속 조건으로 넣으면 한 행이 세 지역을 동시에 만족할 수 없어 결과가 0이 된다.
• 서울, 제주, 세종 중 어느 하나에 해당하는 합계는 SUMIF에 지역 목록을 전달하고 그 결과를 SUMPRODUCT로 묶어 계산한다.
• 월별 합계는 날짜가 DATE로 만든 해당 월 1일 이상이고 다음 달 0일, 즉 해당 월 말일 이하가 되도록 조건을 설정한다.
• 엑셀 2021 이후에는 UNIQUE로 고유 지역을 추출하고 시작 셀 뒤에 해시 기호를 붙여 분산 범위를 동적으로 참조할 수 있다.
• 🎬 이 채널의 다른 영상:
  • 엑셀 FILTER 함수로 다중 조건과 검색 목록을 자동 필터링하는 방법은?
  • 직장인은 ChatGPT로 회의록·학습·식단 업무를 어떻게 효율적으로 자동화할까?
  • 구글 시트와 AI로 무료 웹 앱을 만들고 배포하려면 어떻게 해야 하나?
• 📺 이 채널: 오빠두엑셀 l 엑셀 강의 대표채널

Questions & Answers

Q: 엑셀에서 SUMIF로 제품별 수량 합계를 어떻게 계산하나요?

SUMIF에 제품이 입력된 범위를 조건 범위로, 집계할 제품명을 조건으로, 수량이 입력된 범위를 합계 범위로 지정하면 됩니다. 수식을 아래로 자동 채우기 전에 제품 범위와 수량 범위를 F4 키로 절대참조로 바꾸면 각 행에서도 동일한 원본 범위를 기준으로 제품별 합계가 계산됩니다.

Q: SUMIF 수식을 자동 채우면 결과가 달라지는 이유는 무엇인가요?

자동 채우기 과정에서 상대참조로 입력된 조건 범위와 합계 범위가 함께 이동하기 때문입니다. 반복해서 사용해야 하는 데이터 범위를 선택한 뒤 F4 키를 눌러 절대참조로 고정하면 수식을 아래로 복사해도 원본 범위가 변하지 않아 의도한 조건별 합계를 유지할 수 있습니다.

Q: SUMIF 계산 결과가 올바른지 제출 전에 어떻게 검토하나요?

계산된 제품별 합계 범위를 선택해 상태표시줄에 나타나는 전체 합계를 확인한 뒤, 원본 데이터의 수량 범위도 선택해 같은 방식으로 합계를 비교하면 됩니다. 강의 예시에서는 두 범위의 상태표시줄 합계가 모두 308인지 확인해 집계 누락이나 중복 여부를 검토합니다.

Q: 지역과 제품을 동시에 만족하는 수량 합계는 어떻게 구하나요?

SUMIFS에서 수량 범위를 합계 범위로 지정하고, 지역 범위와 원하는 지역을 첫 번째 조건 쌍으로, 제품 범위와 원하는 제품을 두 번째 조건 쌍으로 지정하면 됩니다. 강의의 서울 지역이면서 C세트인 사례에서는 두 조건을 모두 만족하는 수량 합계가 9로 계산됩니다.

Q: SUMIFS에 서울, 제주, 세종을 연속 조건으로 넣으면 왜 0이 나오나요?

SUMIFS는 입력된 모든 조건을 동시에 만족하는 행만 합산하기 때문입니다. 하나의 지역 값이 서울이면서 제주이고 동시에 세종일 수는 없으므로 조건을 충족하는 행이 없어 결과가 0이 됩니다. 여러 지역 중 하나에 해당하는 값을 더하려면 조건 결합 방식을 바꿔야 합니다.

Q: 서울, 제주, 세종 중 하나에 해당하는 수량을 한 번에 어떻게 합산하나요?

SUMIF의 조건 범위에 지역 열을 지정하고 조건에는 서울, 제주, 세종이 입력된 목록 범위를 전달한 뒤, 반환된 지역별 합계를 SUMPRODUCT로 묶으면 됩니다. 이 방식은 모든 지역을 동시에 만족하도록 요구하지 않고 목록의 각 항목에 해당하는 합계를 계산한 후 하나로 더합니다.

Q: 엑셀 2019 이전 버전에서도 여러 지역 합계를 계산할 수 있나요?

가능합니다. 엑셀 2019 이전 버전은 동적 배열을 제공하지 않아 지역 목록을 조건으로 넣은 SUMIF 결과가 첫 번째 셀에만 표시될 수 있지만, 해당 SUMIF 수식을 SUMPRODUCT로 감싸 실행하면 목록에 포함된 여러 지역의 합계를 동일하게 구할 수 있습니다.

Q: 보조열 없이 특정 월의 수량 합계를 어떻게 구하나요?

SUMIFS에서 날짜 범위가 해당 월의 첫날 이상이고 해당 월의 말일 이하가 되도록 두 조건을 지정하면 됩니다. 시작일은 DATE에 연도, 월, 1을 넣고, 말일은 DATE에 연도, 월에 1을 더한 값, 0을 넣어 계산합니다. 비교 연산자는 문자이므로 큰따옴표로 묶고 날짜와 연결합니다.

Q: 월별 합계를 구할 때 DATE 함수에 다음 달의 0일을 넣는 이유는 무엇인가요?

DATE 함수에서 다음 달과 0일을 지정하면 다음 달 1일에서 하루를 뺀 날짜, 즉 집계하려는 달의 말일이 계산되기 때문입니다. 이를 날짜 범위의 이하 조건으로 사용하고 해당 월 1일을 이상의 조건으로 사용하면 그 달에 포함된 데이터만 SUMIFS로 합산할 수 있습니다.

Q: UNIQUE와 SUMIFS로 자동 보고서를 어떻게 만드나요?

엑셀 2021 이후 버전에서 UNIQUE에 지역 범위를 전달해 고유 지역 목록을 분산시키고, SUMIFS의 조건에는 UNIQUE 수식이 시작된 셀 뒤에 해시 기호를 붙인 분산 범위를 사용하면 됩니다. 원본의 서울을 수원으로 바꾸거나 제주를 판교로 바꾸면 지역 목록과 합계가 함께 갱신됩니다.

Summary & Key Takeaways

• SUMIF는 조건 범위에서 지정한 조건을 만족하는 행의 값만 합산한다. 제품별 수량을 계산할 때는 제품 범위, 제품명, 수량 범위를 차례로 지정한다. 수식을 아래로 자동 채우려면 반복해서 참조할 데이터 범위를 F4 키로 절대참조로 고정해야 한다.

• 여러 조건을 동시에 만족해야 하면 SUMIFS를 사용한다. 서울이면서 C세트인 수량처럼 조건 범위와 조건을 쌍으로 추가할 수 있다. 반면 서울, 제주, 세종 중 하나인 행을 합칠 때는 같은 지역 열에 SUMIFS 조건을 연속 지정하지 말고 SUMPRODUCT와 SUMIF를 결합한다.

• 월별 집계는 날짜에서 월만 추출하는 보조열 없이, 해당 월의 첫날 이상이면서 말일 이하인 날짜를 SUMIFS 조건으로 지정할 수 있다. 엑셀 2021 이후에는 UNIQUE로 고유 지역 목록을 만들고 해시 기호로 분산 범위를 참조해 원본 변경에 따라 갱신되는 보고서를 구성한다.