SUMPRODUCT와 배열수식으로 복잡한 로직을 세 줄로 끝내는 비법

엑셀을 사용하다 보면 복잡한 조건부 계산이나 필터링을 필요한 상황이 자주 발생합니다. 특히 여러 조건을 기반으로 데이터를 처리해야 할 때 일반적인 함수만으로는 구현이 어려워지고, VBA와 같은 고급 기능을 사용해야 할 것 같다는 생각이 들곤 합니다. 

하지만 SUMPRODUCT 함수와 배열수식만 잘 활용해도 대부분의 복잡한 로직들을 단 세 줄로 해결할 수 있습니다.

배열수식의 개념과 함께 SUMPRODUCT 함수의 내면을 파헤치며, 실무에서 응용할 수 있는 다양한 사례를 소개하겠습니다.

배열수식이란?

배열(array)의 기본 개념

엑셀에서 "배열"이란 셀의 집합 또는 셀들 간의 값들을 집합 개념으로 처리하는 것을 말합니다. 아래와 같은 예를 살펴보겠습니다.

A열	B열
1	10
2	20
3	30

행마다 A열과 B열을 곱한 값을 구하고, 이를 모두 더하고 싶다면 보통 아래와 같은 절차를 따릅니다.

1. C열에 A열과 B열의 곱을 구하는 수식 입력
2. C열을 모두 더함

하지만 배열수식을 사용하면 단 한 줄로 이렇게 계산할 수 있습니다:

 

=SUM(A1:A3 * B1:B3)

 

이 수식을 입력한 뒤 Ctrl+Shift+Enter를 눌러야 배열수식으로 입력됩니다. 이렇게 하면 셀 범위 간의 연산을 간결하게 수행할 수 있습니다.

 

배열수식 vs 일반 수식

배열수식과 일반 수식의 차이는 다음과 같습니다:

구분	일반 수식	배열 수식
처리 방법	단일 값	다수의 값을 동시에 처리
입력 방법	Enter	Ctrl + Shift + Enter (구버전 엑셀)
성능	일반적으로 빠름	큰 배열일수록 느려짐
장점	단순 계산에 적합	복잡한 계산에 매우 강력

즉, 배열수식은 복잡한 조건 검사 및 다중 연산을 한 줄로 구현할 수 있는 강력한 도구입니다.

SUMPRODUCT 함수란?

SUMPRODUCT 함수는 배열을 간단하게 연산 후 그 합을 구하는 함수입니다. 기본 사용법은 다음과 같습니다.

 

=SUMPRODUCT(array1, array2, ...)

 

각 배열의 동일 위치에 있는 항목들의 곱을 구하고 그 합계를 반환합니다. 다음과 같은 표가 있을 때:

제품명	수량	단가
A상품	3	1000
B상품	5	2000
C상품	2	1500

아래 수식으로 총 판매 금액을 구할 수 있습니다:

 

=SUMPRODUCT(B2:B4, C2:C4)

 

복잡한 조건문이 없어도 간단히 구할 수 있는 것, 이것이 SUMPRODUCT의 매력입니다.

 

SUMPRODUCT로 조건부 집계 구현하기

엑셀에서 COUNTIFS나 SUMIFS 같은 조건 함수도 있지만, 복잡하고 다양한 조건이 요구되면 SUMPRODUCT가 더욱 유용하게 사용됩니다.

예제: 특정 지역, 특정 카테고리 매출 총합 구하기

지역	카테고리	매출
서울	식료품	1000
부산	의류	1500
서울	의류	2000
대전	식료품	800

서울 지역의 "의류" 상품 매출을 구하고 싶다면 다음과 같이 SUMPRODUCT를 사용할 수 있습니다:

 

=SUMPRODUCT((A2:A5="서울")*(B2:B5="의류")*(C2:C5))

 

이 수식은 조건이 모두 참인 행에 대해서만 매출값을 더해줍니다.

 

세 줄로 복잡한 로직 구현하기

실무에서 자주 마주치는 복잡한 계산들도 SUMPRODUCT로 아래와 같이 간단하고 명확하게 표현할 수 있습니다.

아래는 응용 사례입니다.

사례 1: 다중 조건 평균 구하기

=SUMPRODUCT((조건1)*(조건2)*값) / SUMPRODUCT((조건1)*(조건2))

 

사례 2: 특정 문자 포함된 항목의 합 구하기

=SUMPRODUCT(--ISNUMBER(SEARCH("키워드", A2:A10)), B2:B10)

 

사례 3: 날짜 기준 범위 내 값 계산

=SUMPRODUCT((A2:A100>=DATE(2024,1,1))*(A2:A100<=DATE(2024,1,31))*(B2:B100))

SUMPRODUCT와 배열수식을 활용하면 다양한 응용이 가능하여, 결과적으로 수식의 가독성과 관리 효율성이 매우 높아집니다.

 

배열수식과 SUMPRODUCT의 한계 및 개선 팁

배열수식과 SUMPRODUCT는 분명 강력하지만 몇 가지 주의사항이 있습니다.

• 수식이 길어질 경우 가독성이 저하될 수 있습니다
• 대용량 데이터에는 처리 속도가 저하될 수 있습니다
• 조건이 많아질수록 디버깅이 어려워집니다

이를 보완하려면:

1. Named Range(이름 정의)를 적극 활용
2. 중간 계산 열을 사용하여 수식을 단순화
3. 필요 시 FILTER, LET 함수와 함께 조합

SUMPRODUCT와 배열수식은 엑셀에서 뛰어난 로직 구현력을 제공하는 도구입니다. 

복잡한 필터링, 조건에 따른 집계, 다중 계산도 단 2~3줄의 수식으로 간결하게 처리할 수 있습니다.

이제 여러분은 SUMPRODUCT를 단순한 배열 합산 함수가 아닌, 논리적 로직 구현의 핵심 함수로 활용할 수 있을 것입니다. 

 

 

 

엑셀 데이터 분석 UNIQUE, SORT, FILTER 함수 조합

 

엑셀 순환 참조 발생 해결할까?

 

엑셀 문자 ↔ 숫자 변환하는 방법 ISTEXT,ISNUMBER,이중마이너스,VALUE