SUMIF로 조건에 따라 합산하기

일반적인 SUM은 범위에 있는 모든 숫자를 더합니다. 하지만 실제로는 그중 일부만 더하고 싶은 경우가 많습니다. A열에 지역이, B열에 금액이 들어 있는 판매 시트를 생각해 보세요. 모든 판매액의 합계가 아니라 동부 지역의 합계만 필요할 수 있습니다.

이를 조건부 합계라고 하며, 이를 위해 만들어진 함수가 SUMIF입니다. 일치하는 조건이 충족될 때만 숫자를 더하고 나머지는 자동으로 건너뜁니다.

SUMIF는 다음 세 가지 요소를 순서대로 받습니다.

• 범위 조건과 비교할 셀
• 조건 찾으려는 조건
• 합계 범위 실제로 더할 셀

따라서 스프레드시트는 범위를 살펴보고 조건을 충족하는 행을 찾은 다음, 합계 범위에서 일치하는 셀의 합계를 계산합니다.

A2:A10에 지역 이름이 있고 B2:B10에 판매 금액이 있다고 해 보겠습니다. 동부 지역의 판매액만 합산하려면 A열에서 동부라는 단어를 확인하고 일치하는 금액을 B열에서 더합니다.

스프레드시트는 각 지역 셀을 확인합니다. 동부라고 표시된 셀을 찾으면 해당 행의 금액을 가져와 누적 합계에 더합니다. 다른 지역은 무시됩니다.

조건이 단어일 때는 큰따옴표로 묶으세요: "East". 따옴표가 없으면 스프레드시트는 East를 이름이나 레이블로 인식하여 0을 반환할 가능성이 큽니다.

일치 여부는 대소문자를 구분하지 않으므로 "east"와 "East"는 같은 행과 일치합니다. 단, 불필요한 공백이 없는지 확인하는 등 철자가 데이터와 정확히 일치하도록 하세요.

조건을 직접 입력해도 되지만, 셀에 넣어 두면 더 깔끔합니다. D1에 동부라는 단어가 들어 있다면 텍스트를 직접 작성하는 대신 D1을 참조하세요.

이제 D1의 지역을 변경하면 합계가 즉시 업데이트됩니다. 따라서 수식을 재사용할 수 있고, 하나의 셀을 보고서의 간단한 제어 스위치처럼 사용할 수 있습니다.

조건은 텍스트일 필요가 없습니다. 정확히 500인 모든 금액의 합계를 구하려면 숫자를 조건으로 사용하면 됩니다.

검사할 열과 합산할 열이 같다면 세 번째 인수를 생략할 수도 있습니다. 여기서는 B열을 검사하면서 동시에 합산하므로 B가 500인 행만 더해집니다.

SUMIF는 정확히 일치하는 경우 외에도 사용할 수 있습니다. 따옴표 안에 비교 연산자를 넣으면 숫자의 범위를 합산할 수 있습니다.

• ">100" 100보다 큼
• "<=50" 50 이하
• "<>0" 0과 같지 않음

이 수식은 100보다 큰 모든 판매액의 합계를 계산합니다. 연산자와 숫자는 모두 하나의 따옴표 쌍 안에 있습니다.

기준값이 셀에 들어 있다면 어떻게 해야 할까요? 예를 들어 D1에 100이 있고 그보다 큰 금액을 찾으려는 경우입니다. ">D1"이라고 작성하면 D1이 리터럴 텍스트가 되므로 사용할 수 없습니다. 대신 앰퍼샌드로 연산자와 셀을 연결하세요.

앰퍼샌드는 ">" 텍스트와 D1 안의 값을 이어 붙여 조건 ">100"을 즉석에서 만듭니다.

범위와 합계 범위는 높이가 같고 같은 행에서 시작해야 합니다. 지역 이름이 A2:A10에 있다면 금액은 B3:B11이 아니라 B2:B10에 있어야 합니다.

두 범위가 어긋나면 스프레드시트가 잘못된 지역과 잘못된 금액을 짝지어 합계를 조용히 잘못 계산합니다. 항상 두 범위가 정확히 같은 행을 포함하는지 다시 확인하세요.

A열에 범주가, B열에 비용이 있는 지출 시트를 생각해 보세요. 여행, 식비, 사무실의 합계를 보여 주는 작은 요약 표를 만들려고 합니다.

D2, D3, D4 셀에 각 범주 레이블을 넣은 다음 레이블 셀을 참조하는 SUMIF 하나를 작성하세요. 수식을 아래로 채우면 각 행에서 해당 범주의 합계를 계산합니다. 하나의 수식 패턴으로 세 개의 소계가 즉시 완성됩니다.

앞의 예제에서 A:A와 B:B, 즉 전체 열을 사용한 것을 확인해 보세요. 행이 계속 추가될 때 범위를 확장할 필요가 없으므로 편리합니다.

단, 머리글 행의 텍스트가 실수로 조건과 일치하지 않도록 하고 두 열의 위치를 맞춰 두세요. 매우 큰 시트에서는 A2:A1000처럼 범위를 좁히면 계산이 조금 더 빨라질 수 있습니다.

이제 SUMIF로 조건에 따라 숫자를 더하는 방법을 알게 되었습니다. 핵심 내용은 다음과 같습니다.

• 순서는 범위, 조건, 합계 범위입니다.
• 텍스트 조건에는 따옴표가 필요하며, 일치 여부는 대소문자를 무시합니다.
• ">100"과 같은 연산자는 따옴표 안에 있으며, 연산자와 셀은 &로 연결합니다.
• 범위와 합계 범위가 같은 행에 맞도록 하세요.

다음으로 COUNTIF를 사용해 일치하는 행의 개수를 세어 보겠습니다.