재고 담당자 A씨는 "카테고리가 가전이거나 문구인 상품 중, 재고가 10개 미만인 것들의 총 재고 금액"을 구해야 했다. SUMIFS로 시도해봤지만 '이거나(OR)' 조건이 걸리는 순간 수식이 꼬이기 시작했다. 이럴 때 등장하는 함수가 SUMPRODUCT다. 이름 그대로 '곱한 것들의 합(sum of products)'인데, 이 단순한 이름 안에 조건부 집계 함수들과는 결이 다른 발상이 숨어 있다.
SUMPRODUCT는 원래 무엇을 위한 함수였나
SUMPRODUCT의 원래 용도는 사실 단순하다. 두 개 이상의 배열을 같은 위치끼리 곱한 뒤 그 결과를 모두 더하는 것이다.
=SUMPRODUCT(A2:A5, B2:B5)A열이 수량, B열이 단가라면 이 수식은 (A2×B2)+(A3×B3)+(A4×B4)+(A5×B5), 즉 각 행의 금액을 계산해 모두 더한 총 판매금액을 한 번에 구해준다. 여기까지는 조건과 아무 상관이 없다. 그런데 이 '배열끼리 곱해서 더한다'는 성질이, 조건을 표현하는 데도 그대로 활용될 수 있다는 점이 SUMPRODUCT를 특별하게 만든다.
핵심 원리 — 조건을 0과 1로 바꿔서 곱하기
엑셀에서 (A2:A100="가전")처럼 범위와 조건을 비교하면, 그 결과는 TRUE 또는 FALSE로 이루어진 배열이 된다. 그리고 엑셀 내부에서 TRUE는 1로, FALSE는 0으로 계산에 참여한다. 즉 조건 하나를 '만족하면 1, 아니면 0'인 숫자 배열로 바꿀 수 있다는 뜻이다. SUMPRODUCT는 바로 이 성질을 이용해, 조건 배열과 금액 배열을 곱하는 방식으로 조건부 합계를 만들어낸다.
| 행 | 카테고리="가전" | 재고<10 | 두 조건 곱(AND) | 재고금액 | 곱해서 더할 값 |
|---|---|---|---|---|---|
| 1 | 1 (참) | 1 (참) | 1 | 50,000 | 50,000 |
| 2 | 1 (참) | 0 (거짓) | 0 | 30,000 | 0 |
| 3 | 0 (거짓) | 1 (참) | 0 | 20,000 | 0 |
이 표처럼 각 조건을 0/1 배열로 만든 뒤 서로 곱하면, 두 조건을 모두 만족하는 행만 1이 남고 나머지는 0이 되어 자동으로 걸러진다. 이 0/1 배열을 다시 금액 배열과 곱해서 더하면 조건에 맞는 행의 금액만 합산된다.
=SUMPRODUCT((B2:B100="가전")*(C2:C100<10)*D2:D100)SUMIFS로는 안 되는 OR 조건도 표현할 수 있다
A씨가 막혔던 '가전이거나 문구'라는 OR 조건은 덧셈으로 표현하면 된다. 조건을 곱하면 AND, 더하면 OR이 된다는 원리다.
=SUMPRODUCT(((B2:B100="가전")+(B2:B100="문구"))*(C2:C100<10)*D2:D100)여기서 (B2:B100="가전")+(B2:B100="문구") 부분은 둘 중 하나만 만족해도 1 이상이 되므로, 사실상 OR 조건 역할을 한다. SUMIFS가 조건들 사이에 AND만 지원하는 것과 달리, SUMPRODUCT는 곱셈과 덧셈을 자유롭게 섞어 AND와 OR을 원하는 대로 조합할 수 있다는 점이 가장 큰 차이다.
SUMPRODUCT vs SUMIFS, 언제 SUMPRODUCT를 써야 할까
조건이 모두 AND로만 연결되는 단순한 상황이라면 SUMIFS가 더 짧고 읽기 쉬우므로 그대로 쓰는 것이 낫다. 반면 OR 조건이 섞여 있거나, 다른 시트의 값을 계산에 함께 곱해야 하거나, 텍스트 길이·특정 문자 포함 여부처럼 SUMIFS의 조건 문법으로는 표현하기 까다로운 복잡한 조건이 필요할 때 SUMPRODUCT가 진가를 발휘한다.
주의할 점
SUMPRODUCT에 넣는 배열들은 반드시 크기(행·열 개수)가 서로 같아야 한다. 크기가 다르면 #VALUE! 오류가 발생한다. 또한 조건 배열 안에 텍스트가 섞여 있는 열 전체(예: B:B처럼 끝을 열지 않은 참조)를 그대로 넣으면 연산 속도가 느려질 수 있어, 실제 데이터가 있는 범위로 한정해서 참조하는 것이 좋다.
Q. SUMPRODUCT는 배열수식이라 Ctrl+Shift+Enter를 눌러야 하나요?
아니다. SUMPRODUCT는 함수 자체가 배열 연산을 내장하고 있어서 일반 Enter만으로도 정상 작동한다. 이 점이 SUMPRODUCT가 널리 쓰이는 이유 중 하나다.
Q. 조건을 세 개, 네 개로 늘려도 되나요?
가능하다. 곱하는 조건 배열의 개수에는 실질적인 제한이 없으므로 필요한 만큼 곱해서 연결하면 된다.
Q. SUMPRODUCT로 개수만 세는 것도 가능한가요?
가능하다. 금액 배열을 곱하지 않고 조건 배열들만 곱해서 더하면, 조건을 모두 만족하는 행의 개수를 세는 용도로도 쓸 수 있다.
마무리
SUMPRODUCT의 핵심은 결국 "조건을 0과 1의 숫자로 바꾸면 곱셈과 덧셈만으로 AND와 OR을 표현할 수 있다"는 발상이다. 다음 글에서는 이 0/1 배열이라는 개념을 한 단계 더 확장해서, 하나의 셀 안에 여러 계산이 동시에 담기는 배열수식 자체의 원리를 다뤄본다.