엑셀 SUMPRODUCT 함수 사용하기(SUMIFS, COUNTIFS 대체 함수)

엑셀 SUMPRODUCT 함수는 SUMIFS와 COUNTIFS를 대체할 수 있는 SUM과 PRODUCT의 조합의 함수입니다. 엑셀 함수 중 속도가 빠른 함수에 속하며, 대량의 데이터를 여러 항목의 값들을 조건으로 사용하여 분석할 때 사용되며, 해당 분석 시 사칙연산을 해당 엑셀 SUMPRODUCT 함수에서 계산할 수 있는 응용 범위가 넓은 함수입니다. 

SUMPRODUCT 함수

SUMPRODUCT 함수는 열 범위 내에서 두 개 이상의 배열을 곱하고 그 결과를 합산하는 엑셀 함수로 알려져 있습니다. 

하지만, 두개 이상의 배열을 곱할 수도 있고, 나눌 수도 있으며, 더할 수도 있고, 뺄 수도 있는 "사칙연산이 모두 가능한 함수"입니다. 

또한, 몇가지의 조건을 설정할 수 있는 IF함수의 기능을 가진 함수입니다.  

 

SUMPRODUCT 정의

SUMPRODUCT 조건 설정이 가능한 두 개 이상의 배열을 사칙 연산하여 결과를 합산하여 값을 보여주는 함수입니다.  

(곱하기 *, 나누기 /, 더하기 +, 빼기 - 모두 가능)

SUMPRODUCT 구문

 

기본 구문은 아래와 같습니다. 

= SUMPRODUCT(열1, 열2, 열3 ....)

예를 들어, sumproduct(A:A, B:B)라고 한다면, A1*B1 + A2*B2 + A3*B3 ... = 총합을 보여줍니다. 

이런 기능만이 가능하다면, sumproduct는 사용할 필요가 없을 것입니다. 단순한 사칙연산 수식으로도 충분히 알 수 있는 값이기 때문입니다. 

 

SUMPRODUCT 함수 사용하기(SUMIFS, COUNTIFS 대체 함수)

아래와 같은 테이블이 있습니다. 

A 열	B 열	C 열	D 열
부문	상품	가격	거래량
10	금	1000	30
20	철	500	40
10	금	1000	50
20	수은	300	60
10	아연	200	70

SUMIFS의 대체

A열의 부문과 B열의 상품을 선택하여 가격과 거래량의 총합을 구하고 싶을 때 여러분은 SUMIFS를 사용할 것입니다. 

1) C열과 D열의 값을 곱하여 각 행에 값을 넣을 수 있도록 열을 추가하여 곱하기 수식을 넣습니다. 

2) 추가한 열(E열)을 기준으로 A열과, B열을 변수로 활용하여 원하는 값을 구합니다.

=SUMIFS(E:E, A:A,10, B:B,"금")

E:E는 더하고자 하는 값의 열의 범위이며, 그 뒤에 있는 구문은 부문의 변수, 상품의 변수입니다. 변수의 추가가 가능합니다.

변수의 추가 시 "변수가 포함된 열" 그리고 "쉼표" 그리고 원하는 변수를 넣어 줍니다. 

(숫자는 숫자 그대로 변수에 입력하고, 텍스트는 큰따옴표를 사용합니다.)

 

아래는 엑셀 SUMIFS 함수에 대한 링크입니다. 

 

엑셀 SUMIFS 함수 정의 및 구문, 활용 방법 (엑셀 더하기 함수)

 

SUMIFS 함수는 데이터 분석에서 많이 사용되는 함수이기 때문에 널리 사용되지만, 빅데이터 분석 시에는 사용을 안 하는 편입니다. 

가장 큰 이유는 엑셀 파일 자체가 느려집니다. 이를 대체하기 위해 가장 좋은 방법은 피벗의 슬라이서(pivot slicer)를 사용하는 것이며, 두 번째 좋은 방법이 바로 SUMPRODUCT 함수입니다. 

=SUMPRODUCT((A:A=10)*(B:B="금")*((C:C)*(D:D)))

곱하기를 위한 추가 열을 만들 필요가 없습니다. 변수 역시 IF함수의 기능 그대로 사용 가능합니다. SUMIFS보다 월등히 빠른 계산 속도를 보여줍니다. 구문을 설명하자면

 

=SUMPRODUCT 이후 함수를 시작하는 괄호 ( 를 넣어 줍니다. 이후 변수가 되는 또 다른 괄호(로 시작하여 넣어주며 변수가 되는 값을 =으로 연결하여 숫자는 숫자 그대로 문자는 큰 따옴표를 사용하여 완성 후 각각 괄호)를 닫아 줍니다. 변수를 연결할 때는 여러 가지 방법이 많지만 곱하기 기호 * 를 사용하는 것으로 합니다. 그 이후 곱하고자 하는 열을 수식에 넣기 전, 곱하기 기호*로 연결 후 곱하고자 하는 괄호로 감싸진 열을 전체적으로 다시 한번 괄호로 감싸 줍니다. 반드시 필요한 포인트는 아니지만 이후 곱하기뿐만 아니라 사칙연산을 모두 사용하기를 원한다면 해당 괄호를 사용하시는 것을 추천드립니다. 

 

위의 설명이 어렵게 느껴진다면, 예로 보여드린 구문을 그대로 사용해 보신 후 응용해 보세요

 

COUNTIFS의 대체

어떤 조건들에 해당하는 값이 몇 개가 있는지 그 카운팅 된 숫자를 구할 때, 우리는 countifs를 사용합니다. 

이 역시 더욱더 빠른 속도를 갖고 있는 SUMPRODUCT로 구현이 가능합니다. 

=SUMPRODUCT((A:A=10)*(B:B="금")*((C:C)/(C:C)))

부문이 10이며, 상품은 금인 값의 개수를 카운팅 해주는 수식이며 배열의 곱하기가 아닌 나누기를 사용하는 게 핵심입니다. 

 

엑셀 COUNTIF 함수를 대체하는 또 다른 방법에 대해 아래에 링크로 확인하실 수 있습니다.

 

엑셀 SUM 함수 다중조건 (COUNTIFS 대체, 엑셀 범위비교 조건)

 

결론

SUMPRODUCT의 사용의 시작은 빅데이터 분석의 시작이라고 생각합니다. 

IF함수, SUMIFS함수, COUNTIFS함수의 사용을 SUMPRODUCT함수로 변경 시 놀라운 속도를 경험하실 수 있을 것입니다.