SUMIFS로 여러 조건에 따라 합산하기

이미 SUMIF가 동부 지역의 모든 판매처럼 하나의 규칙에 일치하는 값의 합계를 계산한다는 것을 알고 있습니다. 하지만 실제 질문에는 보통 여러 조건이 포함됩니다. 1월의 동부 지역 판매액은 얼마였을까요? 이는 두 조건을 동시에 적용하는 경우입니다.

이럴 때 SUMIFS가 유용합니다. 끝의 S는 여러 조건을 함께 적용할 수 있다는 뜻이며, 지정한 모든 검사를 통과한 행만 합계에 더해집니다.

이 단원에서는 인수의 순서를 배우고, 처음으로 여러 조건을 사용하는 합계를 작성하며, 자주 발생하는 대표적인 실수를 피하는 방법을 알아봅니다.

SUMIFS는 SUMIF에서 예상할 수 있는 순서와 반대입니다. 더할 숫자가 먼저 나오고, 그다음 각 조건이 한 쌍으로 주어집니다.

• sum_range — 합계를 계산할 값
• criteria_range1, criteria1 — 첫 번째 검사
• criteria_range2, criteria2 — 두 번째 검사

범위와 조건의 쌍을 최대 127개의 조건까지 계속 추가할 수 있습니다. 다음 패턴을 소리 내어 읽어 보세요. 이 값을 합산하되, 이 값이 저 값과 같고, 또 이 값이 저 값과 같은 경우입니다.

A열에 지역, B열에 월, C열에 금액이 들어 있는 표를 생각해 보세요. 동부 지역의 1월 총 판매액을 구하려고 합니다.

더할 금액은 C:C에 있습니다. 첫 번째 조건은 A:A에서 “East”를 확인하고, 두 번째 조건은 B:B에서 “January”를 확인합니다. 두 조건이 모두 참인 경우에만 행이 계산됩니다.

이는 SUMIFS에서 가장 흔한 오류입니다. sum_range와 모든 criteria_range의 크기가 같아야 합니다. 즉, 행과 열의 개수가 같아야 합니다.

sum_range가 C2:C100인데 조건 범위가 A2:A99라면, 행의 위치가 서로 맞지 않기 때문에 수식에서 #VALUE! 오류가 반환됩니다.

가장 안전한 방법은 모든 범위에서 시작 행과 끝 행을 동일하게 사용하거나, A:A처럼 전체 열을 일관되게 사용하는 것입니다.

따옴표 안에 “동부”를 직접 입력해도 작동하지만, 유연한 보고서에서는 사용자가 선택할 수 있어야 합니다. 지역을 F1에, 월을 F2에 입력한 다음 해당 셀을 조건으로 참조하세요.

이제 F1이나 F2를 변경하면 합계가 즉시 다시 계산됩니다. 단순한 셀 참조에는 따옴표가 필요하지 않다는 점에 유의하세요. 따옴표는 수식 안에 직접 입력한 텍스트에만 사용합니다.

조건은 정확히 일치하는 텍스트로만 제한되지 않습니다. 숫자에는 비교 연산자를 사용할 수 있으며, 이 연산자는 따옴표로 묶어야 합니다.

• ">100" — 100보다 큼
• "<=50" — 50보다 작거나 같음
• "<>0" — 0과 같지 않음

여기서는 동부 지역의 금액을 합산하되, 금액 자체가 100보다 큰 행만 포함합니다. sum_range와 조건 범위가 같은 열일 수도 있다는 점에 유의하세요.

기준값이 직접 입력된 값이 아니라 셀에 들어 있다면 어떻게 해야 할까요? ">F1"이라고 작성하면 안 됩니다. 이는 F1이라는 문자 그대로의 텍스트를 찾기 때문입니다. 대신 & 기호로 연산자와 셀을 연결해야 합니다.

따라서 ">"&F1은 F1에 들어 있는 값보다 큼이라는 조건을 만듭니다. 이 연결 방식은 동적으로 사용자가 값을 지정하는 보고서에 꼭 필요합니다.

SUMIFS는 쉽게 확장할 수 있습니다. 새로운 규칙을 추가할 때마다 범위와 조건의 쌍을 하나씩 더하면 됩니다. D열에 판매 담당자가 있다고 가정해 보세요. 동부 지역에서 1월에 “마리아”가 판매한 금액을 합산할 수 있습니다.

각 조건은 결과의 범위를 더욱 좁힙니다. SUMIFS는 AND 논리를 사용하므로, 합계에 포함되려면 행이 세 가지 검사를 모두 통과해야 합니다.

텍스트 조건은 와일드카드를 지원합니다. 별표 *는 임의의 개수의 문자를 일치시키고, 물음표 ?는 정확히 하나의 문자를 일치시킵니다.

• "North*"는 North, Northeast, Northwest와 일치합니다.
• "*east*"는 east를 포함하는 모든 텍스트와 일치합니다.

여기서는 “북부”로 시작하는 모든 지역의 금액을 합산합니다. 지역 이름이 같은 접두사로 시작할 때 유용합니다.

인수 순서가 반대이므로 차이점을 외워 두는 것이 좋습니다.

• SUMIF: range, criteria, [sum_range] — 검사할 범위가 먼저 나오며, sum_range는 선택 사항이고 마지막에 옵니다.
• SUMIFS: sum_range, criteria_range1, criteria1, ... — sum_range가 항상 먼저 나옵니다.

팁: 조건이 둘 이상이라면 바로 SUMIFS를 사용하세요. 일관된 하나의 패턴을 유지하기 위해 조건이 하나일 때도 SUMIFS를 사용하는 사람이 많습니다.

SUMIFS가 0을 반환하면 대개 수식이 잘못된 것이 아니라 모든 조건에 일치하는 행이 없다는 뜻입니다. 텍스트에 숨은 공백이 있는지, 철자가 일치하는지, 숫자가 텍스트로 저장되어 있지는 않은지 다시 확인하세요.

빠른 진단 방법은 조건을 한 번에 하나씩 제거하는 것입니다. 특정 조건을 제거했을 때 합계가 나타난다면, 모든 행을 제외한 조건이 바로 그 조건입니다. 조건을 하나씩 분리해 확인하는 습관을 들이면 여러 조건을 사용하는 수식의 문제를 빠르게 해결할 수 있습니다.

이제 여러 조건에 해당하는 숫자를 한 번에 합산할 수 있습니다.

• SUMIFS(sum_range, range1, crit1, range2, crit2, ...) — sum_range가 먼저 나옵니다.
• 모든 범위의 크기가 같아야 하며, 그렇지 않으면 #VALUE!가 반환됩니다.
• ">"&F1을 사용해 셀과 비교하고, 와일드카드에는 * / ?를 사용하세요.
• 조건은 AND 논리를 사용하므로, 행이 모든 조건을 통과해야 합니다.

다음으로는 COUNTIFS를 사용해 여러 조건에 해당하는 행의 개수를 세어 보겠습니다.