제품과 단가를 한번에 계산할 수 있는 SUMPRODUCT 함수

제품과 단가를 한번에 계산할 수 있는 SUMPRODUCT 함수

두 개 이상의 범위의 각 행별로 곱하여 합산하는 함수 SUMPRODUCT
각 제품마다 단가를 곱하여 총 재고금액이 얼마인지 사용하는 등 유용하게 사용됩니다

해당 포스트를 읽고 나면

SUMPRODUCT  함수의 일반적인 내용을 이해하실 수 있습니다.
응용 등은 다른 포스트에서 다룹니다.

제품과 단가를 한번에 계산할 수 있는 SUMPRODUCT 함수

포스트목차

구글스프레드 시트라는 클라우드 소프트웨어로 진행합니다.
엑셀과 유사하나 엑셀만 사용하신 분들은 불편하실 수 있습니다.

다만, 아래와 같은 장점이 있습니다.

  • 엑셀프로그램 무설치
  • 로그인 필요없음
  • 다른 pc/기기에도 같은 문서로 편집가능
  • 링크 하나로 공유가능

n줄 요약
  • 서로 곱하고자 하는 카테고리를 열값으로 만듭니다.
  • 카테고리 아래 데이터를 같은 길이의 범위를 두 개이상 선택합니다.
  • 범위를 하나만 선택한 경우 각 행의 값의 합계가 나옵니다(=SUM).

함수안내

SUMPRODUCT( 범위1, 범위2)

두 개 이상의 범위의 각 행별로 곱하여 합산하는 함수 SUMPRODUCT
각 제품마다 단가를 곱하여 총 재고금액이 얼마인지 사용하는 등 유용하게 사용됩니다

숫자가 아니면 안됩니다.

숫자가 아니라면 값은 0 으로 처리됩니다.

행의 갯수는 동일해야합니다.

범위1, 2, 3… 모두 같은 행의 크기(길이) 이여야 합니다.

다른 함수와 응용하여 조건부 합계가 가능합니다.

SEARCH, ISNUMBER 함수 등 같이 응용사용 시 더 효과적입니다. 


제품과 단가를 한번에 계산할 수 있는 SUMPRODUCT 함수

예제 다루기

제품과 단가를 한번에 계산할 수 있는 SUMPRODUCT 함수

예제 설명입니다.

입고현황을 기재하여 사용하는 것을 예시로 작성했습니다.

감자 테이블에 있는 입고단가 범위를 복사합니다.

감자 테이블에 있는 입고수량을 두번째 범위를 넣으면 각 행마다 입고수량과 단가를 곱한 값을 합산하여 나타냅니다.

SUMPRODUCT진짜메인

위와 같은 발주서 등이 있다고 할 때 
각 품목에 수량과 단가들에 합계액을 보고 싶을 경우 SUMPRODUCT함수를 이용하지 않을시 극단적으로 (수량*단가)+(수량*단가)….등의 노가다성 수식작업이 필요한데요,
이런 경우 시간도 시간이지만 데이터가 추가될 경우에도 더욱 귀찮게 됩니다.

SUMPRODUCT함수는 배열함수로써 이러한 작업들을 간단하게 만들어주는데요,위 예제에서 사용된 수식을 보도록 하겠습니다.

=SUMPRODUCT(E5:E12,F5:F12)/ SUMPRODUCT 함수를 사용할 경우 위 수식이 끝이 나게 됩니다. 

그렇다면 실제로 SUMPRODUCT함수로 계산한 값이 SUMPRODUCT함수를 사용하지 않은 다른 수식과 값이 같은지 확인해보겠습니다.

D14셀 “사용x합계”를 보면 SUMPRODUCT함수의 결과값과 동일하신 걸 볼 수 있습니다.

각 범위에 대응되는 값들을 곱한 후 각 결과값들을 모두 합하는것이 SUMPRODUCT의 기초 사용법이고 다음은 응용하는 방법을 알아보도록 하겠습니다.

작업11

다음 응용 방법으로 SUMPRODUCT함수를 활용하여 부분텍스트값이 일치하는 값만 합계액을 구해보도록 하곘습니다.

수식은 다음과 같습니다.

=SUMPRODUCT(NOT(ISERROR(SEARCH(K4,D5:D12))),E5:E12,F5:F12)

위 수식은 제품명이 있는 범위에서 SEARCH함수로 조건에 일치하는 값만 찾아 TRUE값(1)을받아 각 범위를 곱하여 합계액을 구하는 수식입니다.

ISERROR함수와 NOT함수가 등장하게 되는데요,
SEARCH함수는 찾는 검색값이 없으면 오류를 반환합니다. ISERROR함수는 오류일 경우 TRUE값을 반환합니다. NOT함수는 TRUE값인 경우 FALSE를 반환, FALSE값인 경우 TRUE를 반환합니다. SEARCH함수에서 조건에 만족하는 값이 없는 경우 그 값은 오류값을 반환하여 ISEERROR함수로 TRUE값을 반환하게 되므로 이를 반대로 바꿔줄 NOT함수를 사용하여 정확히 조건에 일치한 값의 TRUE값을 구해 조건에 일치하는 범위를 구합니다. 나머지는 인수에 각 범위를 넣어 마무리합니다.

작업33

다음 응용 방법으로 SUMPRODUCT함수를 활용하여 홀수행값만 합계액을 구해보도록 하겠습니다.

사용된 수식은 다음과 같은데요.

=SUMPRODUCT(ISODD(ROW(B5:B12)),E5:E12,F5:F12)

행을 반환하는 ROW함수에 범위를 인수로 넣은 후 해당 값이 홀수면 TRUE를 반환하는 ISODD함수를 사용하여 TRUE행만 곱한 후 합한 금액을 확인 할 수 있습니다.

실제로 정확히 값이 구해졌는지 확인해보도록 하곘습니다.

(1*1000)+(3*2000)+(3*3000)+(5*3000)

=1000+6000+9000+15000 = 31,000이 반환된 것을 확인할 수 있습니다.

 

이상으로 SUMPRODUCT함수에 대해 알아보았습니다.

SUMPRODUCT 함수는 보통 재고관련하여 계산할 때 사용 됩니다. 하지만 재고현황에는 예제처럼 필터기준이 상품이 아닌 입고날짜 기준일 가능성이 높습니다.
또한 상품명은 다르지만 같은 카테고리(예: 전자기기/식품/의류 등) 을 계산할 때도 종종 있습니다. 그럴 경우 SUMIFS 함수 사용하시거나 본 함수 SUMPRODUCT 안에 범위인수를 조건부로 다뤄야 합니다. 이 부분은 응용에서 다루겠습니다.

구글시트 필수 함수 IF함수

IF함수는 구글스프레드시트에서 가장 많이 사용되는 함수입니다.조건에 참과 거짓을 나누어 반환할 수 있습니다. IF문을 두번이상 사용하여 조건을 계속해서 걸 수 있습니다.

Read More »