Excel SUMPRODUCT 함수

Excel에서 SUMPRODUCT 함수는 두 개 이상의 열 또는 배열을 곱한 후 곱한 결과의 합계를 구하는 데 사용할 수 있습니다. 사실, SUMPRODUCT는 COUNTIFS 또는 SUMIFS 함수처럼 여러 조건에 따라 셀 값을 계산하거나 합산하는 데 유용한 기능입니다. 이 문서에서는 SUMPRODUCT 함수의 구문과 몇 가지 예제를 소개합니다.

• 예제 1: SUMPRODUCT 함수의 기본 사용법
• 예제 2: SUMPRODUCT 함수로 여러 조건에 따라 셀 값 합산하기
• 예제 3: SUMPRODUCT 함수로 여러 조건에 따라 셀 개수 세기
• 예제 4: SUMPRODUCT 함수로 가중 평균 계산하기

 구문:

Excel에서 SUMPRODUCT 함수의 구문은 다음과 같습니다:

 인수:

• array1: 필수. 곱하고 더하려는 첫 번째 배열 또는 셀 범위입니다.
• array2: 선택 사항. 곱하고 더하려는 두 번째 배열 또는 셀 범위입니다.

참고:

• 1. 범위에 숫자가 아닌 값이 포함된 경우, SUMPRODUCT는 이를 0으로 처리합니다.
• 2. 배열이나 범위의 행과 열 수가 다른 경우, SUMPRODUCT 함수는 #VALUE! 오류를 반환합니다.
• 3. 배열에 논리 테스트가 포함된 경우, TRUE와 FALSE 값을 생성합니다. 대부분의 경우, 이중 단항 연산자(--)를 사용하여 이를 1과 0으로 변환해야 합니다.
• 4. Excel 2007 및 이후 버전에서는 SUMPRODUCT 함수가 최대 255개의 인수를 처리할 수 있지만, 이전 Excel 버전에서는 30개의 인수만 처리할 수 있습니다.
• 5. SUMPRODUCT는 와일드카드 문자를 지원하지 않습니다.

 반환값:

곱하고 더한 배열의 결과를 반환합니다.

 예제:

예제 1: SUMPRODUCT 함수의 기본 사용법

SUMPRODUCT의 기본적인 사용법은 두 열을 곱한 다음 합산하는 것입니다. 예를 들어, 아래 스크린샷 데이터가 있다고 가정해 보겠습니다. 이제 주문량과 단가를 곱한 후 모든 곱셈 결과를 합산하여 총 가격을 구하려고 합니다.

이 작업을 처리하려면 아래 공식을 적용하십시오:

설명: 이 공식 SUMPRODUCT(B2:B7,C2:C7) = B2*C2+B3*C3+B4*C4+B5*C5+B6*C6+B7*C7.

그런 다음 Enter 키를 누르면, B열의 셀은 C열의 해당 셀과 같은 행에서 곱해지고, 결과는 합산됩니다. 스크린샷 참조:

예제 2: SUMPRODUCT 함수로 여러 조건에 따라 셀 값 합산하기

여러 조건에 따라 셀 값을 합산하기 위해 SUMIFS 함수 외에도 SUMPRODUCT 함수도 이 작업을 신속하고 쉽게 해결할 수 있습니다.

사례 1: 다른 열에 특정 텍스트가 포함된 경우 셀 값 합산하기

아래 스크린샷과 같이 데이터 표가 있다고 가정합니다. 이제 제품이 KTE이고 이름이 David인 총 가격을 계산하려고 합니다. Excel에서 SUMPRODUCT 공식을 사용하여 어떻게 할 수 있을까요?

1. 다음 공식 중 하나를 빈 셀에 입력하거나 복사하십시오:

• 팁: 위 공식에서:
• A2:A12="KTE": 합계를 구하기 위한 첫 번째 조건 범위 및 조건입니다.
• B2:B12="David": 합계를 구하기 위한 두 번째 조건 범위 및 조건입니다.
• D2:D12: 합산하려는 셀 값의 범위입니다.

2. 그런 다음 Enter 키를 눌러 필요한 결과를 얻으십시오:

사례 2: OR 논리로 여러 조건에 따라 셀 값 합산하기

OR 논리를 사용하여 여러 조건에 따라 셀 값을 합산하려면, SUMPRODUCT 함수 내에서 여러 조건을 결합하기 위해 더하기 기호(+)를 사용해야 합니다.

예를 들어, 아래 스크린샷과 같이 A열에서 제품 KTE와 KTO의 총 가격을 합산하려고 합니다:

다음 공식을 빈 셀에 적용하십시오:

그런 다음 Enter 키를 누르면, 제품 KTE와 KTO의 총 가격이 계산됩니다. 스크린샷 참조:

사례 3: OR 및 AND 논리로 여러 조건에 따라 셀 값 합산하기

어떤 상황에서는 OR 및 AND 논리를 동시에 사용하여 셀 값을 합산해야 할 수도 있습니다. SUMPRODUCT 함수는 이러한 작업도 쉽게 해결할 수 있습니다.

별표(*)는 AND 연산자로 사용됩니다.

더하기 기호(+)는 OR 연산자로 사용됩니다.

예를 들어, 판매량이 200보다 큰 경우 제품 KTE와 KTO의 총 가격을 합산해 보겠습니다.

다음 공식을 복사하거나 입력하십시오:

그런 다음 Enter 키를 눌러 필요한 결과를 얻으십시오:

예제 3: SUMPRODUCT 함수로 여러 조건에 따라 셀 개수 세기

일반적으로 이 SUMPRODUCT 함수는 Excel에서 여러 조건에 따라 셀 개수를 세는 데에도 도움이 됩니다. 합계 값을 계산하는 것과 마찬가지로, 공식에서 sum_range 인수를 제거하기만 하면 됩니다.

사례 1: AND 논리로 여러 조건에 따라 셀 개수 세기

여기서는 제품이 KTE이고 판매량이 200보다 큰 셀을 세려고 합니다. 따라서 다음 공식 중 하나를 사용하십시오:

• 팁: 위 공식에서:
• A2:A12="KTE": 세기 위한 첫 번째 조건 범위 및 조건입니다.
• B2:B12>200: 세기 위한 두 번째 조건 범위 및 조건입니다.

그런 다음 Enter 키를 눌러 결과 개수를 얻으십시오:

사례 2: OR 논리로 여러 조건에 따라 셀 개수 세기

A열에서 제품 KTE와 KTO의 개수를 세려면, 다음 공식을 적용하십시오:

그런 다음 Enter 키를 눌러 제품 KTE와 KTO의 개수를 얻습니다. 스크린샷 참조:

사례 3: OR 및 AND 논리로 여러 조건에 따라 셀 개수 세기

판매량이 200보다 큰 제품 KTE와 KTO의 개수를 세려면, 다음 공식을 적용하십시오:

그런 다음 Enter 키를 눌러 필요한 결과 개수를 얻으십시오:

예제 4: SUMPRODUCT 함수로 가중 평균 계산하기

일반적으로 우리는 셀 범위의 평균을 빠르고 쉽게 계산할 수 있습니다. 그러나 다양한 관련성을 가진 값 목록의 평균을 계산하려면 가중 평균을 계산해야 합니다. Excel에는 이를 직접적으로 계산하는 함수가 없습니다. 하지만 SUMPRODUCT 함수가 이를 처리하는 데 도움이 될 수 있습니다.

예를 들어, 아래 스크린샷과 같이 학생의 성적 통계를 나타내는 표가 있습니다. 각 과제는 서로 다른 가중치를 가지고 있습니다.

가중 평균을 계산하려면 다음 공식을 사용하십시오:

설명: 이 공식: SUMPRODUCT(B2:B6, C2:C6) / SUM(C2:C6) = (B2*C2+B3*C3+B4*C4+B5*C5+B6*C6)/(C2+C3+C4+C5+C6)

그런 다음 Enter 키를 누르면 가중 평균이 즉시 계산됩니다. 스크린샷 참조:

 SUMPRODUCT 함수를 사용한 추가 기사: