엑셀 SUMPRODUCT 함수 사용법에 관해 알아보는 글입니다. 원하는 범위 또는 배열을 한번에 곱한 후 더하기 위해 SUMPRODUCT 함수를 사용할 수 있습니다. 초보자도 쉽게 따라할 수 있습니다.
엑셀 연습 파일을 다운로드 받아서 연습을 해보면 이해가 훨씬 쉽습니다.
SUMPRODUCT 함수 없이 곱해서 더하기
SUMPRODUCT 함수를 사용하는 방법을 알아보기 전에 SUMPRODUCT 함수 없이 값을 곱한 후 합계를 구하는 방법을 알아보겠습니다.
아래 사진을 보시면 제품명, 단가, 판매 개수가 나와 있습니다. 여기서 판매 금액을 구해 볼 건데요. 단가와 판매 개수를 곱하면 판매 금액이 나옵니다. 그리고 판매 금액을 구한 값에서 셀 모서리에 커서를 두고 자동 채우기를 해서 나머지 셀에도 판매 금액을 구할 수 있습니다.
이번에는 판매 금액들의 합계를 구해 볼 것입니다 따라서 D8 셀에 커서를 두고 =SUM을 입력하고 판매금액에 해당되는 D2:D7 전체 셀을 선택 해 줍니다.
그후 엔터키를 누르면 178,500원이라는 총 판매 금액이 나오게 됩니다.
엑셀 SUMPRODUCT 함수 사용법
위에서 SUMPRODUCT 함수 없이 값을 곱한 후 합계를 구하는 방법을 알아 봤습니다. 그런데 위의 방법의 경우 두 번의 과정을 거쳐야 합계를 구할 수 있었습니다. 따라서 이번에는 SUMPRODUCT 함수를 이용해서 한 번에 곱하기를 해서 합계를 구하는 방법을 알아 보겠습니다.
2가지 항목 곱하는법
아래 사진에서 단가와 판매 개수를 곱한 후 곱한 값을 한 번에 모두 더해 보겠습니다.
▶︎입력해야 하는 함수 식: =SUMPRODUCT(B2:B7,C2:C7)
우선 =SUMPRODUCT 를 입력합니다. 그 후 단가에 해당되는 전체 셀인 B2:B7을 선택합니다. 콤마를 입력한 후 판매개수에 해당되는 전체 셀인 C2:C7 셀을 선택합니다. 그 후 엔터키를 누릅니다.
그럼 아래 사진처럼 178,500원이라는 결과 값이 나오게 됩니다. 이 말의 의미를 정리해보겠습니다. 엑셀은 첫 번째로 단가와 판매개수를 곱한 결과 값을 데이터로 저장을 합니다. 그리고 두 번째로 저장한 데이터들을 모두 더합니다. 그럼 아래와 같이 178,500원이라는 결과 값이 나오게 됩니다.
다시 간단히 정리해볼게요. 첫 번째로 곱하기를 합니다. 그리고 두 번째로 더하기를 하는 것입니다. SUMPRODUCT 함수는 이 2가지 과정을 한 번에 수행합니다.
3가지 항목 곱하는법
이번에는 SUMPRODUCT 함수를 이용해서 3가지 항목을 구하는 방법을 알아 보겠습니다.
아래 사진을 보시면 이번에는 인센티브라는 항목이 추가가 되었습니다. 단가, 판매개수, 인센티브를 모두 곱한 후 합계를 구해보겠습니다.
▶︎입력해야 하는 함수 식: =SUMPRODUCT(B2:B7,C2:C7,D2:D7)
우선 =SUMPRODUCT 라고 입력을 합니다. 그리고 첫 번째로 단가 전체 셀을 선택합니다. 콤마를 입력하고 두 번째로 판매개수 전체 셀을 선택합니다. 또 콤마를 입력하고 세 번째로 인센티브 전체 셀을 선택합니다. 그 후 괄호를 닫고 엔터키를 누릅니다.
그럼 7,700원이라는 값이 나오게 됩니다.
7,700원이라는 값의 의미는 각 곱한 값을 모두 더한 것입니다. 아래 사진을 보시면 긴연필에 해당되는 값이 800원, 메모장에 해당되는 값이 2,700원이죠. 이런식으로 첫 번째로 각 제품명에서 단가 × 판매개수 × 인센티브 값을 구합니다. 그리고 두 번째로 각 곱한 값들을 모두 더하면 7,700원이 나오는 것입니다.
특정 조건 만족하는 값의 합계 구하는법
이번에는 SUMPRODUCT 함수를 조금 더 유용하게 활용 할 수 있는 방법을 알아 보겠습니다. 2가지 조건을 우리가 미리 지정을 하고, 그 조건에 맞는 값의 합계를 SUMPRODUCT 함수를 이용해서 구해볼게요.
아래 사진을 보시면 제조사, 제품명, 단가, 판매 개수가 나와있는 데이터가 있습니다. 여기서 2가지 조건을 설정 할 것입니다. 제조사가 ‘메모 나라’이면서 제품명이 ‘메모장’인 값의 판매금액의 합계를 구해보겠습니다.
우선 =SUMPRODCUT 라고 입력을 합니다. 그 후 괄호를 2번 입력합니다. 그리고 첫 번째 조건을 지정하기 위해서 제조사 전체 셀을 선택합니다. =을 입력한 후 큰 따옴표 안에 ‘메모나라’라고 입력합니다. 그리고 괄호를 닫습니다.
▶︎입력해야 하는 함수 식: =SUMPRODUCT((B2:B7=”메모나라”)*(C2:C7=”메모장”),D2:D7,E2:E7)
그 후 곱하기 기호를 입력하고 괄호를 입력합니다. 여기서는 콤마가 아닌 곱하기 기호를 입력해야 한다는 점에 유의해 주세요. 두 번째 조건을 지정하기 위해서 제품명 전체 셀을 선택합니다. =을 입력하고 큰 따옴표 안에 ‘메모장’이라고 입력을 합니다. 괄호를 닫고 콤마를 입력합니다.
이번에는 단가에 해당되는 전체 셀을 선택합니다. 콤마를 입력하고 판매개수에 해당되는 전체 셀을 선택합니다. 괄호를 닫고 엔터키를 누르면 아래 사진처럼 조건에 해당되는 값의 합계가 구해집니다.
SUMPRODUCT 함수를 사용하면 여러 번의 과정 없이 한 번에 결과값을 구할 수 있습니다. 근데 계산 과정이 생략이 되므로 어떻게 해서 99000원이라는 결과값이 나오는 것인지 이해하기 어려울 수 있습니다.
따라서 위에서 구해본 결과값이 어떻게 나오는 것인지 계산 과정을 좀 더 세부적으로 확인해보겠습니다.
우리가 지정한 조건은 2가지였습니다. 첫 번째는 제조사가 ‘메모나라’이고, 두 번째는 제품명이 ‘메모장’인 경우 였습니다. 아래 사진을 보시면 2가지 조건에 모두 해당되는 경우(메모나라이면서 동시에 메모장인 셀) 노란색으로 표시를 해두었습니다.
여기서 계산 과정을 더 자세히 살펴보면 우리가 설정 했던 조건인 ‘메모나라’ 또는 ‘메모장’에 해당되는 경우 아래 사진처럼 TRUE라는 값으로 바뀌게 됩니다. 그리고 조건에 해당되지 않는 경우에는 FALSE 라는 값으로 바뀌게 됩니다.
그리고 엑셀에서 TRUE는 1로 바뀌게 되고, FALSE는 0으로 바뀌게 됩니다. 제조사 × 제품명 × 단가 × 판매개수를 모두 곱하는 경우 아래 사진처럼 0이 하나라도 포함되어 있으면 결과값이 0이 됩니다.
반면 노란색으로 표시된 부분의 경우 곱하기 계산 식에 0이 없습니다. 따라서 곱하기를 하면 0이 아닌 값이 나오게 됩니다.
그리고 아래 사진처럼 곱하기를 해서 나온 결과 값들을 모두 더한 값이 우리가 구하는 최종 값이 되는 것입니다.
이렇게 엑셀 SUMPRODUCT 함수를 이용해서 곱하기 및 합계 계산을 한 번에 하는 방법을 알아보았습니다. 2번씩 수식을 입력하지 않아도 한 번에 계산을 할 수 있어서 유용하게 활용할 수 있는 함수입니다.
<결론>
▶︎엑셀 SUMPRODUCT 함수 사용법
1.SUMPRODUCT(배열1,배열2,배열3…)
2.엑셀 SUMPRODUCT 함수는 값을 곱한 후 모두 더할 때 사용한다
이상 엑셀 SUMPRODUCT 함수 사용법에 관한 글이었습니다.