Home > 엑셀 함수 > 수학 및 삼각 함수 > 엑셀함수 SUMPRODUCT – 숫자를 모두 곱하고 합계 구하기

엑셀함수 SUMPRODUCT - 숫자를 모두 곱하고 합계 구하기

SUMPRODUCT함수는 SUM함수와 PRODUCT함수가 합쳐진 형태이다.
[PRODUCT함수 바로가기]

PRODUCT함수는 주어진 인수를 모두 곱해주고 SUM함수는 인수를 모두 더해 주므로 SUMPRODUCT함수는 주어진 인수를 모두 곱한 후에 합계를 구해준다고 이해할 수 있는데 정확히는 인수로 주어진 배열의 요소를 모두 곱하고 그 곱의 합계를 구하는 것이다.

무슨 말인지 이해가 잘 안될 수 있지만 실습용 파일을 다운로드 받아서 잘 따라해 보면 이해가 될 것이다.

구문(Syntax)

SUMPRODUCT(array1, [array2], [array3], …)

주어진 배열에서 해당 요소를 모두 곱하고 그 곱의 합계를 구한다.

인수 :

– array1 : 계산하려는 배열의 첫 번째 인수

– array2, array3,… :  생략가능. 계산하려는 배열의 인수, 2개에서 255개까지 지정할 수 있다

 

사용예

SUMPRODUCT함수를 보기전에 다음 예를 먼저 살펴보자.

1) 두개의 항목을 곱해서 합계 구하기

아래 그림과 같이 상품의 총 판매실적 합계를 구하려면

엑셀함수 SUMPRODUCT - 숫자를 모두 곱하고 합계 구하기

  • 각 행별로 단가 x 판매수량 = 합계를 구하고(=C5*D5)
  • 이렇게 구해진 합계를 전부 더하면(=SUM(E5:E7))

총 판매실적이 구해진다.

 

그런데 다음과 같이 SUMPRODUCT함수를 이용하면 각 행별 합계를 구하지 않고 한번에 총합계를 구할 수 있다.

=SUMPRODUCT(C5:C7,D5:D7)

엑셀함수 SUMPRODUCT - 숫자를 모두 곱하고 합계 구하기

이 함수는 한번에 총합계를 구해 주지만 내부는 어떤 순서로 작동되는지 다음 그림을 보고 이해해 보자.

엑셀함수 SUMPRODUCT - 숫자를 모두 곱하고 합계 구하기

  1. 단가 2,000 x 판매수량 50 = 100,000원을 구해서 메모리에 저장
  2. 단가 1,000 x 판매수량 35 = 35,000원을 구해서 메모리에 저장
  3. 단가 500 x 판매수량 120 = 60,000원을 구해서 메모리에 저장
  4. 메모리에 저장한 각 행별 합계를 SUM해서 195,000원을 구해준다.

 

2) 세개의 항목을 곱해서 합계 구하기

앞의 예는 곱하는 항목이 2개(단가, 판매수량)였는데

다음 그림과 같이 3개의 항목을 곱해서 합계를 구할 수도 있다.

엑셀함수 SUMPRODUCT - 숫자를 모두 곱하고 합계 구하기

상품별로 인센티브 금액을 구하는 경우

행별로 "단가 x 판매수량 x 인센티브율 = 인센티브금액"을 구해서
각 행의 합계를 더해 주어야 하는데
다음과 같이 SUMPRODUCT함수로 한번에 인센티브 금액합계를 구할 수 있다.

=SUMPRODUCT(C15:C17,D15:D17,E15:E17)

한번에 총합계를 간단히 구해 주지만 함수의 내부는 다음과 같이 작동한다.

엑셀함수 SUMPRODUCT - 숫자를 모두 곱하고 합계 구하기

  1. 단가 2,000 x 판매수량 50 x 인센티브율3% = 3,000원을 구해서 메모리에 저장
  2. 단가 1,000 x 판매수량 35 x 인센티브율5% = 1,750원을 구해서 메모리에 저장
  3. 단가 500 x 판매수량 120 x 인센티브율2% = 1,200원을 구해서 메모리에 저장
  4. 메모리에 저장한 각 행별 합계를 SUM해서 5,950원을 구해준다.

 

3) 조건별로 곱하고 합계구하기

SUMPRODUCT함수를 좀 더 응용해보자. 특정조건에 해당하는 행만 곱하고 더할 수 있는 기능이 있다.

다음 표에서

  • 분류가 "노트류"이고
  • 상품명이 "스프링노트"인

것의 판매금액(단가x판매수량) 합계를 구해보자.

엑셀함수 SUMPRODUCT - 숫자를 모두 곱하고 합계 구하기

 

C35셀에 다음 수식을 입력한다.

=SUMPRODUCT(($B$25:$B$30=$C$32)*($C$25:$C$30=$C$33),$D$25:$D$30,$E$25:$E$30)

이제 $C$32셀에 분류를 입력하고, $C$33셀에 상품명을 입력하면 C35셀에 조건을 만족하는 합계액을 표시해준다.

이 수식은 다음 그림과 같이 조건을 만족하는 행의 판매금액을 더해주는 방식으로 작동한다.

엑셀함수 SUMPRODUCT - 숫자를 모두 곱하고 합계 구하기

이 수식을 자세히 살펴보자.

SUMPRODUCT함수에 다음과 같이 3개의 인수가 들어가 있다.

  • 첫번째 인수 ($B$25:$B$30=$C$32)*($C$25:$C$30=$C$33)
    분류가 "노트류"이고 상품이 "스프링노트" 이면 TRUE를 반환하고 아니면 FALSE를 반환
  • 두번째 인수 $D$25:$D$30 는 단가
  • 세번째 인수 $E$25:$E$30 는 판매수량

SUMPRODUCT함수는 3개의 인수를 각 행별로 곱한 후 합계를 구하게 된다.

예를 들어 25행은

  • 분류가 "노트류"이고 상품이 "스프링노트"이므로 첫번째 인수는 TRUE를 반환하고
  • 단가 2,000원은 두번째 인수이고
  • 판매수량 35가 세번째 인수이므로

첫번째 인수 TRUE(1로 인식, FALSE는 0으로 인식) x 두번째 인수 2,000 x 세번째 인수 35 = 70,000이 구해진다.

26행은

  • 분류가 "노트류"이고 상품이 "무지노트"이므로 첫번째 인수는 FALSE를 반환하고
  • 단가 1,500원은 두번째 인수이고
  • 판매수량 100이 세번째 인수이므로

첫번째 인수 FALSE(0로 인식, TRUE는 1로 인식) x 두번째 인수 1,500 x 세번째 인수 100 = 0이 구해진다. 즉 분류는 "노트류"이지만 상품명이 "스프링노트"가 아니라 "무지노트"여서 조건을 만족하지 못해 금액이 0원으로 구해진 것이다.

이렇게 행별로 구해진 값을 더해서 다음 그림과 같이 전체 합계 358,000원이 구해진다.

엑셀함수 SUMPRODUCT - 숫자를 모두 곱하고 합계 구하기

 

[ 수학 및 삼각 함수 ]

 

“엑셀함수 SUMPRODUCT - 숫자를 모두 곱하고 합계 구하기”의 1개의 댓글

  1. 핑백: 엑셀 배열수식 이해하기 - XLWorks

답글 남기기

이메일 주소를 발행하지 않을 것입니다.