목차
SUMIFS가 있는데 굳이 이걸 왜 쓰나요?
반갑습니다. 실무 엑셀의 깊이를 더해드리는 직장인 엑셀 꿀팁 저장소입니다!
지난 시간에는 유효성 검사와 INDIRECT 함수로 이중 드롭다운을 만드는 법을 통해 데이터 입력의 정확도를 높였습니다. 데이터가 쌓였다면 이제 분석을 해야겠죠?
보통 조건부 합계를 구할 때 SUMIF나 SUMIFS 함수를 가장 먼저 떠올립니다. 물론 훌륭한 함수입니다. 하지만 ‘단가 × 수량’의 총합계를 구해야 한다면 어떨까요? 보통은 D열에 ‘금액’이라는 보조 열을 만들어서 곱셈을 한 뒤, 다시 그 열을 SUM으로 더하는 번거로운 과정을 거칩니다.
하지만 엑셀 SUMPRODUCT 함수를 알면 보조 열 따위는 필요 없습니다. “이 범위와 저 범위를 곱해서 싹 다 더해!”라고 명령 한 줄이면 끝나기 때문이죠. 게다가 SUMIFS로는 불가능한 ‘배열 가공’ 계산까지 가능한, 그야말로 다중 조건 합계의 끝판왕입니다.
기본 원리: 곱하고(PRODUCT) 더한다(SUM)
함수 이름이 모든 것을 설명합니다. SUM(합계) + PRODUCT(곱하기)입니다.
- 구조:
=SUMPRODUCT(배열1, 배열2, 배열3...) - 원리: 같은 위치에 있는 셀끼리 먼저 곱합니다. 그리고 그 곱한 값들을 모두 더해 최종 결과를 내놓습니다.
예를 들어 A열에 [2, 3], B열에 [4, 5]가 있다면,
=SUMPRODUCT(A:A, B:B)의 결과는
(2 × 4) + (3 × 5) = 8 + 15 = 23이 됩니다. 아주 직관적이죠?
더 수학적인 정의가 궁금하시다면 마이크로소프트의 SUMPRODUCT 함수 공식 문서를 확인해 보세요.
핵심 비법: 논리값(True/False)을 숫자로 바꾸기
여기서부터가 진짜 실무입니다. SUMPRODUCT를 조건부 합계(SUMIFS 대용)로 쓸 때는 곱하기 기호(*)를 사용하는 것이 핵심입니다.
엑셀에서 조건식(예: A1=”사과”)은 참(True) 아니면 거짓(False)을 반환합니다. 컴퓨터에게 True는 1이고 False는 0입니다.
(조건범위="사과") * (판매량범위)
이 수식은 어떻게 작동할까요?
– 사과인 셀: True(1) × 판매량 = 판매량
– 사과가 아닌 셀: False(0) × 판매량 = 0
결국 사과인 행의 데이터만 살아남아 더해지게 됩니다. 이것이 SUMPRODUCT가 다중 조건을 처리하는 마법의 원리입니다.
실전: 보조 열 없이 ‘총 판매액’ 구하기
쇼핑몰 매출 데이터를 예로 들어봅시다. C열에 단가, D열에 수량이 있습니다. 전체 총매출을 구하고 싶습니다.
[하수]
E열에 =C2*D2를 입력하고 아래로 쭉 복사한 뒤, 맨 밑에 =SUM(E:E)을 쓴다.
[고수]
아무 셀에나 다음과 같이 쓴다.
=SUMPRODUCT(C2:C100, D2:D100)
단 1초 만에, 시트를 지저분하게 만들지 않고도 정확한 가중 평균 합계를 구해냅니다. 데이터가 늘어나도 수식을 수정할 필요가 없죠.
심화: SUMIFS도 못 하는 ‘날짜 연도별’ 합계
“그냥 SUMIFS 쓰면 되는 거 아닌가요?”라고 반문하실 수 있습니다. 하지만 SUMIFS는 범위(Range)를 가공할 수 없다는 치명적인 단점이 있습니다.
예를 들어, 날짜가 2024-01-05처럼 들어있는데 “2024년도의 합계”만 구하고 싶다면 어떨까요?
- SUMIFS 방식: 시작일(2024-01-01)과 종료일(2024-12-31) 조건을 따로 걸어야 합니다. 복잡하죠.
=SUMIFS(합계범위, 날짜범위, ">=2024-01-01", 날짜범위, "<=2024-12-31") - SUMPRODUCT 방식: 날짜에서 연도만 쏙 빼서(YEAR 함수) 2024랑 같은지 물어보면 끝입니다.
=SUMPRODUCT((YEAR(날짜범위)=2024) * 합계범위)
보이시나요? 범위 자체에 함수(YEAR)를 씌워서 조건으로 쓸 수 있다는 점, 이것이 바로 SUMPRODUCT가 ‘끝판왕’이라 불리는 이유입니다.
마무리하며
오늘은 엑셀 함수 중에서도 가장 활용도가 높고 강력한 SUMPRODUCT에 대해 알아봤습니다. 단순 곱합(Weighted Sum)부터 복잡한 배열 조건 계산까지, 이 함수 하나면 퇴근 시간이 획기적으로 빨라집니다. 단, 데이터가 10만 행이 넘어갈 때는 속도가 조금 느려질 수 있다는 점만 기억해 주세요.
다음 시간에는 데이터를 요약할 때 숨겨진 셀은 기가 막히게 피해 계산해 주는 SUBTOTAL과 AGGREGATE 함수로 찾아오겠습니다. 여러분의 스마트한 엑셀 라이프를 응원합니다!