엑셀 SUMIFS 함수 여러 조건에 맞는 합계 구하는 실무 예제

엑셀에서 여러 조건을 만족하는 합계를 구하는 방법

직장 생활을 하다 보면 엑셀을 다루는 일이 피할 수 없는 일상이 됩니다. 그중에서도 데이터를 분석하고 보고서를 작성할 때 가장 빈번하게 쓰이는 작업 중 하나는 특정 조건에 맞는 값들의 합계를 구하는 것입니다. 단순한 합계라면 SUM 함수 하나로 충분하겠지만 실무에서는 그렇게 단순한 데이터만 마주하는 경우가 드뭅니다.

예를 들어 서울 지역에서 판매된 특정 제품의 총 매출액을 구하거나 3팀 소속이면서 영업 실적이 100만 원 이상인 직원들의 인센티브 총합을 구해야 하는 상황이 발생합니다. 이처럼 두 가지 이상의 까다로운 조건을 동시에 충족하는 데이터의 합계를 구해야 할 때 구세주처럼 등장하는 함수가 바로 SUMIFS입니다. 이 기능을 능숙하게 다루게 되면 야근 시간을 획기적으로 줄일 수 있을 뿐만 아니라 데이터 분석의 정확도도 크게 높일 수 있습니다.

엑셀 SUMIFS 함수의 기본 구조 이해하기

SUMIFS 함수는 여러 개의 조건을 검사하여 그에 해당하는 셀들의 합계를 구해주는 강력한 도구입니다. 기존에 쓰이던 SUMIF 함수가 하나의 조건만 처리할 수 있었다면 SUMIFS 함수는 이름에 복수를 뜻하는 S가 붙어 여러 조건을 동시에 처리할 수 있도록 진화한 형태입니다.

이 함수를 사용하기 위해서는 기본적인 작성 순서와 각 인수의 의미를 정확히 알고 있어야 합니다. 함수의 생김새는 다음과 같습니다.

• 합계 범위: 실제로 더하고자 하는 숫자 데이터가 들어있는 셀들의 영역입니다
• 조건 범위 1: 첫 번째 조건을 검사할 셀들의 영역입니다
• 조건 1: 첫 번째 조건에 해당하는 값이나 기준입니다
• 조건 범위 2: 두 번째 조건을 검사할 셀들의 영역입니다 (선택 사항)
• 조건 2: 두 번째 조건에 해당하는 값이나 기준입니다 (선택 사항)

여기서 주의해야 할 점은 다른 조건문 함수들과는 달리 합계 범위가 맨 앞에 온다는 사실입니다. 많은 초보자들이 SUMIF 함수와 헷갈려서 범위를 뒤에 적는 실수를 하곤 하니 이 순서만큼은 꼭 기억해 두는 것이 좋습니다.

실무에서 바로 쓰는 구체적인 활용 예제

백 마디 이론보다 하나의 실습이 더 큰 도움이 됩니다. 직장에서 흔히 볼 수 있는 판매 관리 대장을 바탕으로 SUMIFS 함수가 어떻게 쓰이는지 살펴보겠습니다. A열부터 D열까지 각각 날짜, 지역, 담당자, 판매 금액이 기록된 데이터가 있다고 가정해 봅시다.

서울 지역의 홍길동 담당자 판매 총액 구하기

서울에서 근무하는 홍길동이라는 직원이 총 얼마의 매출을 올렸는지 계산해야 하는 상황입니다. 이때 적용할 공식은 다음과 같습니다.

=SUMIFS(D2:D100, B2:B100, "서울", C2:C100, "홍길동")

이 공식을 뜯어보면 판매 금액이 들어있는 D열의 범위에서 값을 찾되 B열에서 서울을 찾고 동시에 C열에서 홍길동을 찾아 일치하는 행들의 판매 금액만 쏙쏙 골라 더하게 됩니다.

특정 기간 동안의 조건별 합계 구하기

날짜를 기준으로 조건을 걸 때도 SUMIFS 함수는 아주 유용하게 쓰입니다. 예를 들어 2023년 5월 한 달 동안 발생한 부산 지역의 매출을 구하고 싶다면 부등호를 활용할 수 있습니다.

=SUMIFS(D2:D100, B2:B100, "부산", A2:A100, ">=2023-05-01", A2:A100, "<=2023-05-31")

이처럼 날짜나 숫자 데이터는 크거나 같다 또는 작거나 같다 같은 비교 연산자를 큰따옴표로 묶어서 활용할 수 있습니다. 조건이 여러 개라 복잡해 보이지만 기준을 명확히 세우면 어떤 데이터든 자유롭게 추출할 수 있습니다.

작업 속도를 높여주는 유용한 팁과 노하우

함수를 입력할 때 매번 큰따옴표를 치고 텍스트를 직접 입력하는 것은 번거롭고 오타를 유발하는 주원인이 됩니다. 실무에서 더 효율적으로 엑셀을 다루기 위한 몇 가지 요령을 소개합니다.

• 셀 주소 참조 활용하기: 조건 값에 직접 글자를 적는 대신 조건이 적혀 있는 다른 셀을 클릭해서 지정하면 공식을 복사해서 사용할 때 매우 편리합니다
• 와일드카드 문자 활용하기: 특정 단어로 시작하거나 포함하는 데이터를 찾고 싶다면 별표()나 물음표(?) 같은 와일드카드를 조건에 사용할 수 있습니다. 예를 들어 김으로 시작하는 모든 담당자의 실적을 더하고 싶다면 "김"이라고 입력하면 됩니다
• 이름 정의 기능 사용하기: 데이터 범위가 너무 길어서 드래그하기 힘들다면 해당 범위에 이름을 정의해 두고 함수 안에서 그 이름을 대신 불러다 쓰면 가독성이 훨씬 좋아집니다

흔히 저지르는 실수와 해결 방법

SUMIFS 함수를 처음 배울 때 가장 많이 하는 실수는 각 범위의 크기를 다르게 지정하는 것입니다. 합계 범위는 100행까지 지정했는데 조건 범위는 50행까지만 지정하면 엑셀은 어김없이 오류 메시지를 띄웁니다.

모든 조건 범위와 합계 범위는 반드시 행의 크기와 열의 형태가 완벽하게 일치해야 합니다. 예를 들어 합계 범위가 D2부터 D100이라면 모든 조건 범위 역시 2행에서 시작해서 100행으로 끝나는 구조를 유지해야 오작동을 막을 수 있습니다.

또 다른 흔한 실수는 텍스트 조건 앞뒤에 큰따옴표를 빠뜨리는 것입니다. 엑셀은 큰따옴표가 없는 글자는 셀 주소나 정의된 이름으로 인식하기 때문에 텍스트로 된 조건을 입력할 때는 반드시 쌍따옴표로 감싸주어야 한다는 점을 잊지 말아야 합니다.

데이터 분석을 한 단계 업그레이드하는 대안

SUMIFS 함수는 여러 조건을 처리하는 데 훌륭하지만 조건이 너무 많아지거나 데이터의 구조가 복잡할 때는 한계를 드러내기도 합니다. 이럴 때 실무 전문가들이 즐겨 사용하는 또 다른 방법들이 있습니다.

피벗 테이블은 마우스 드래그 몇 번만으로도 수많은 조건의 합계와 평균을 시각적으로 훌륭하게 보여줍니다. 복잡한 함수 수식을 길게 작성하기 부담스러운 상황이라면 피벗 테이블을 활용하는 것이 시간적으로 훨씬 경제적일 수 있습니다.

또한 최신 버전의 엑셀을 사용하고 있다면 SUMPRODUCT 함수나 FILTER 함수를 결합하여 더욱 유연하게 데이터를 다룰 수도 있습니다. 하지만 기본적으로 SUMIFS 함수는 엑셀 생태계에서 가장 호환성이 높고 어디서나 안정적으로 작동하므로 가장 먼저 익혀야 할 필수 교양과도 같습니다.

실무자들이 자주 묻는 질문

함수를 사용하다 보면 예기치 않은 상황에 부딪히기 마련입니다. 현장에서 가장 자주 제기되는 의문점들을 정리했습니다.

대소문자를 구분해서 조건을 걸고 싶을 때는 어떻게 하나요

기본적인 SUMIFS 함수는 알파벳의 대소문자를 구분하지 못하고 똑같이 처리합니다. 만약 영문자 코드의 대소문자를 엄격하게 구분해서 합계를 구해야 한다면 SUMPRODUCT 함수와 EXACT 함수를 조합하는 우회적인 방식을 사용해야 합니다.

다른 시트에 있는 데이터를 참조해서 합계를 구할 수 있나요

가능합니다. 조건 범위나 합계 범위를 지정할 때 마우스로 다른 시트의 탭을 클릭하여 원하는 영역을 드래그해주기만 하면 엑셀이 알아서 시트 이름을 포함한 주소를 생성해 줍니다. 이를 통해 연간 종합 보고서나 부서별 취합 시트를 손쉽게 완성할 수 있습니다.

결과가 자꾸 0으로 나오거나 오류가 발생하는데 원인이 무엇인가요

대부분은 조건에 사용된 데이터의 형태가 일치하지 않기 때문에 발생합니다. 숫자로 저장되어야 할 데이터가 텍스트 형식으로 입력되어 있거나 반대로 텍스트에 보이지 않는 공백이 포함되어 있는 경우가 대표적입니다. 이럴 때는 TRIM 함수 등으로 공백을 제거하거나 데이터 형식을 통일해 주어야 정상적인 결과값을 얻을 수 있습니다.