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

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

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

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

 

사용예

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

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

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

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

총 판매실적이 구해진다.

 

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

=SUMPRODUCT(C5:C7,D5:D7)

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

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개의 항목을 곱해서 합계를 구할 수도 있다.

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

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

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

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

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판매수량) 합계를 구해보자.

 

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함수에 다음과 같이 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원이 구해진다.

 

[ 수학 및 삼각 함수 ]

• 엑셀에서 SUMIFS 함수로 월별 합계 구하기
• 엑셀함수 SUBTOTAL - 목록이나 데이터베이스의 부분합(집계결과) 구하기
• 엑셀함수 EVEN - 가장 가까운 짝수로 올림,내림하기
• 엑셀함수 ODD - 가장 가까운 홀수로 올림,내림하기
• 엑셀함수 GCD - 최대공약수 구하기
• 엑셀함수 LCM - 최소공배수 구하기
• 엑셀함수 SEQUENCE - 연속된 숫자목록 만들기
• 엑셀함수 SIGN - 수의 부호 구하기
• 엑셀함수 MMULT - 두 배열의 행렬 곱 구하기
• 엑셀함수 MINVERSE - 정방행렬의 역행렬 구하기
• 엑셀함수 MDETERM - 배열의 행렬식 구하기
• 엑셀함수 TRUNC - 숫자에서 지정된 자릿수까지만 남기기(절사하기)
• 엑셀함수 POWER – 숫자의 거듭제곱 구하기
• 엑셀함수 PI(파이) - 원주율 구하기
• 엑셀함수 FACT - 팩토리얼(계승값) 구하기
• 엑셀함수 INT - 숫자를 가장 가까운 정수로 내림하기
• 엑셀함수 SQRT - 양의 제곱근 구하기
• 엑셀함수 LOG,LOG10,LN - 로그값 구하기
• 엑셀함수 EXP - 상수 e를 지수만큼 거듭제곱하기
• 엑셀함수 BASE - 10진수를 다른 진수로 바꾸기
• 엑셀함수 MROUND - 배수값으로 숫자 반올림하기
• 엑셀함수 CEILING, FLOOR - 배수값으로 숫자 올림,내림하기
• 엑셀에서 난수 구하기 - RAND, RANDBETWEEN 함수
• 엑셀에서 몫과 나머지 구하기 - QUOTIENT, MOD 함수
• 엑셀에서 숫자 반올림,올림,내림하기 - ROUND, ROUNDUP, ROUNDDOWN
• 엑셀함수 ABS - 절대값 구하기
• 엑셀함수 SUMPRODUCT - 숫자를 모두 곱하고 합계 구하기
• 엑셀함수 PRODUCT - 숫자를 모두 곱하기
• 엑셀함수 SUMIFS - 여러 조건을 만족하는 범위의 합계구하기
• 엑셀함수 SUMIF - 조건을 만족하는 범위의 합계구하기
• 엑셀함수 SUM - 합계구하기