엑셀 곱한값끼리 더해주는 다중조건 수식 SUMPRODUCT 함수 활용 방법!

엑셀은 숫자와의 전쟁이다!

더하기, 곱하기, 빼기, 나누기...!!

실무에서 많이 활용하는 함수 중 곱한값끼리 더해주는 다중조건 수식인 SUMPRODUCT 함수에 대해서 알아보겠다.

곱하기 쭈욱 계산하고 그 다음에 한 번에 전체 합계 계산하고 나눠서 할 수도 있지만,

엑셀은 가장 빠르고 효율적인 방법으로 해야 멋진 것 아니겠는가!

엑셀만 잘해도 일 잘한다는 소리 쉽게 들을 수 있기 때문에!

SUMPRODUCT 함수 사용 방법에 대해서 알아보자.

 

[목차여기]

 

엑셀 SUMPRODUCT 함수   

SUMPRODUCT 함수는 합계의 SUM과 곱셈의 PRODUCT를 합한 것과 같다.

PRODUCT가 왜 곱셈이지? 하는 분들을 위해 간단히 설명하자면!

PRODUCT는 상품, 제품을 의미하는 영어단어가 맞지만,

곱셈의 요인이 되는 수들을 Factor라고 부르고, 그 결과의 값이 되는 수를 곱, Product라고 부른다.

흔히 곱셈은 영어로 multiplication이라고 알고있긴 하지만 product도 곱셈을 의미한다는 것!

엑셀에서 PRODUCT 함수도 곱셈을 뜻한다.

 

=SUMPRODUCT(배열1, [배열2], [배열3], ...)
=SUMPRODUCT(array1, [array2], [array3], ...) 

• 배열, array : 계산하려는 배열로 2개에서 255개까지 지정 가능하다. 모든 배열은 같은 차원이어야 함 

 

설정한 배열에서 서로 곱하기를 한 뒤에 그 곱셈한 값들을 모두 합하는 것이 SUMPRODUCT의 기능이다.

기본적으로 두 개, 또는 그 이상의 항목들을 곱해서 더해준다.

최대 255개까지 배열을 지정할 수 있는데 조건은 모든 배열은 같은 차원이어야 한다.

만약 차원이 서로 다르다면 어떤건 배열 간 곱셈이 가능한데 어떤건 붕 떠버리는 값들이 생기기 때문에,

#VALUE! 라는 오류를 반환하게 된다.  

 

쉬운 예제로 이해하기 

쉬운 예제로 SUMPRODUCT 함수를 이해해보자!

엑셀 SUMPRODUCT 함수 이해하기

 

나는 카페 사장이다.

메뉴별로 단가가 있고 월별 판매량을 통해 판매단가를 계산해보고 싶다.

이 데이터는 추후 내 카페의 매출과 계산하여 순수익을 뽑기 위한 참고 데이터로 활용될 예정이다.

 

메뉴별로 단가가 쭉 있고 월별 판매량을 엑셀로 정리해두었다.

이제 월별 판매량에 따라 판매단가를 쭉 계산해주어야 하는데,

쉽게 드는 생각은 단가와 판매량을 PRODUCT 함수로 쭉 곱해주고 그 값들을 마지막에 SUM 하는 것이다.

그런데 이렇게 한다면 월별 판매량 컬럼수 만큼 월별 판매단가 컬럼을 또 만들어내야 한다.

이 작업을 추가 컬럼 생성 없이 한 번에 해주는 함수가 바로 SUMPRODUCT 함수이다.  

 

엑셀 SUMPRODUCT 함수 예제

 

첫 번째 배열은 단가이다.

단가를 기준으로 월별 판매량을 각각 곱해야 하기 때문!

단가는 고정이고 월별 판매량은 수식 붙여넣기를 통해 컬럼별로 다른 값들이 들어가기 때문에,

단가는 F4 키를 통해 절대값을 먹였다.

 

=SUMPRODUCT($B$3:$B$14

 

그 다음은 단가와 매칭하여 각 셀끼리 곱셈을 해줄 월별 판매량이다.

1월 판매량의 배열은 C3부터 C14까지이다.

 

=SUMPRODUCT($B$3:$B$14, C3:C14)  

 

이렇게 하면 첫 번째 배열인 단가와 두 번째 배열인 1월 판매량의 각 값끼리 곱셈을 하고,

곱셈한 값끼리 전체 합계를 한 결과가 최종적으로 반환된다.

결과값은 515,300 으로 PRODUCT 함수로 각 셀끼리 곱셈하고 마지막에 전체 SUM으로 합계한 것과 동일한 값이 반환된다.

 

그 뒤로는 수식 붙여넣기를 쭉~ 해주면 바로 끝!

수식 붙여넣기 하면 완성!

 

기준 값인 단가를 절대값으로 했기 때문에 그대로 수식 붙여넣기 하면

첫 번째 배열은 계속해서 단가 컬럼인 B 컬럼으로 들어가게 된다.

그리고 바뀌는 컬럼마다 C 컬럼, D 컬럼, E 컬럼 적용되면서 붙여넣기가 바로 됨!

 

수식 붙여넣기 단축기 알아보기

수식 붙여넣기의 단축기도 알아두면 유용하게 활용할 수 있다.

     수식 붙여넣기 단축기
1. alt > e > s > f (동시가 아니라 순서대로 누르기)
2. ctrl + alt + v 누른 뒤 f 누르기

 

1번 또는 2번 모두 같은 결과이다.

둘 중 편한 방식의 단축기를 사용하면 됨!

마지막 F 버튼이 수식으로 붙여넣기를 선택하는 것인데,

참고로 V를 누르면 값 붙여넣기로 수식 없이 반환된 값만 붙여넣기를 할 수 있다.

수식까지 가져오면서 참조값 오류 이런게 있을 수 있어 값만 가져오길 원하는 경우에 쓸 수 있다.

개인적으로는 수식 붙여넣기, 값 붙여넣기 이 두 가지를 가장 많이 활용하는 것 같다.     

 

 

엑셀 SUMPRODUCT 함수가 어떤 함수인지, 어떻게 활용하는지에 대해 예제와 함께 알아보았다.

배열끼리 각각 곱한 다음에 곱한값들끼리 모두 더해주는 함수!

실무에서 요긴하게 많이 활용되는 함수 중 하나이기 때문에 잘 알아두도록 하자!

 

함께하면 좋은 글

엑셀 필터링하여 화면에 보이는 데이터만 부분합 계산하는 SUBTOTAL 함수 사용 방법

엑셀 vlookup 함수 사용방법과 자주 발생하는 오류 해결방법