1. 정의
product는 '곱하기'란 의미이고, sum이므로 곱한 것의 합계가 됩니다. 그러나, 곱하기뿐만 아니라 더하기, 빼기, 나누기한 후 합계를 구할 수도 있습니다.
2. 구문
=SUMPRODUCT(array1, [array2], [array3], ...)
- array1만 필수이고, array2부터는 선택입니다.
- array1에 배열 수식처럼 조건 여러 개를 *나 +로 연결해서 입력할 수 있습니다.
- Sum+If 배열수식, Sum 또는 SumIfs 함수로 같은 결과를 얻을 수 있는 경우도 있고, 피벗 테이블이 더 효율적일 때도 있습니다.
3. 예제
가. 수량 * 단가로 총매출 구하기
SumProduct 함수의 전형적인 예입니다.
위 표와 같은 경우 수량 * 단가의 합을 구하려면 일반적으로는 D열에 =수량* 단가 수식을 입력한 후 D6셀에서 합계를 구하게 되는데,
SumProduct 함수를 이용하면 한 줄로 수량 * 단가의 합을 구할 수 있습니다.
수식은 =SUMPRODUCT(B2:B5,C2:C5)로서, array1은 B2:B5이고, array2는 C2:C5입니다.
배열끼리 곱하는데, 한 줄씩 열끼리 곱합니다. 다시 말해 2행에서는 B2와 C2를 곱하고, 3행에서는 B3와 C3를 곱하는 식입니다.
(Sum 함수를 이용하는 경우)
SumProduct 함수의 경우는 array1, array2로 ,(콤마)를 이용해 인수를 입력하는데,
Sum함수의 경우는 수식이 =SUM(B2:B5*C2:C5)로서, array1*array2로 *(곱하기)를 이용해 인수를 입력합니다.
(Sum + If 배열 수식을 이용하는 경우)
=SUM(IF(TRUE,B2:B5*C2:C5))라고 입력하고 CSE(Ctrl + Shift + Enter) 키를 눌러서 입력하면 같은 결과가 나오기는 하는데, 수식이 길고 CSE키를 눌러야 하기 때문에 불편합니다. 물론 Microsoft 365의 경우에는 CSE가 아니라 엔터키만 눌러도 되기는 합니다.
(SumIfs 함수로는 안됨)
=sumifs(b2:b5*c2:c5,b2:b5,">0")라고 입력하려고 했더니, 수식에 문제가 있다고 하면서 안됩니다. sum_range여야 하는데 b2:b5*c2:c5로 배열이라 그런 듯합니다.
나. 조건에 일치하는 수익 구하기
(1) 총수익 구하기
지점별 수익과 지출이 아래와 같을 경우 총수익을 구하려면 이번에는 곱하기가 아니라 빼기를 해야 합니다. 다시 말해 지점별로 수익 - 지출한 값을 더해야 합니다.
이 때는 =SUMPRODUCT(H2:H5-I2:I5)라고 입력해서 구합니다.
(2) 해당 지점의 손익 구하기
강남 지점의 수익만 알고 싶으면
=H2-i2라고 해도 되지만
G8셀의 값이 바뀔 때마다 해당하는 수익을 구하려면 조건식을 줘야 합니다.
따라서, 수식은 =SUMPRODUCT((G2:G5=G8)*(H2:H5-I2:I5))가 됩니다.
(Sum + If 배열 수식을 이용하는 경우)
SumProduct함수의 경우는 조건과 계산식을 곱하기로 연결하는데,
Sum + If 배열 수식의 경우는 If 문을 사용하므로 If(조건식, 참일 때 값, 거짓일 때 값)으로 입력하는데, 거짓일 때 값은 생략가능합니다.
따라서, 수식은 =SUM(IF((G2:G5=G8),H2:H5-I2:I5))이 됩니다.
위에서 언급한 바와 같이 Microsoft 365가 아니라면 CSE 키를 눌러야 합니다.
다. 여러 가지 조건을 만족하는 값 구하기
아래와 같이 지역, 지점, 월별 판매량이 있을 때
서울, 강남 지점의 총판매량을 구하거나, 경기지역의 2월 총판매량을 구할 때 곱하기를 하는 것은 아니지만 And 조건이므로 SumProduct함수를 사용할 수 있습니다.
(서울 강남의 총판매량 구하기)
Q11셀의 수식은 =SUMPRODUCT((N2:N9=N11)*(O2:O9=O11)*(Q2:Q9))입니다. 이 때는 CSE키를 입력할 필요가 없습니다.
N2:N9=N11은 지역이 서울인 경우가 되고, O2:O9=O11는 지점이 강남인 경우인데, *(곱하기) 기호로 연결됐으므로 And조건이므로, 지역이 서울이고 지점이 강남인 경우가 됩니다.
그리고, *(Q2*Q9)은 Q2:Q9중에서 조건에 부합하는 판매량입니다.
수식에서 (N2:N9=N11)*(O2:O9=O11)*(Q2:Q9)까지 범위를 선택한 다음 F9키를 누르면(종전에는 못 본 것 같은데, 수식 입력줄 위에 {1222;0;1874;0;0;0;0;0}이 표시됩니다),
(N2:N9=N11)*(O2:O9=O11)*(Q2:Q9)의 계산 결과가 {1222;0;1874;0;0;0;0;0}라고 표시됩니다. 따라서 1,222+1,874는 3,096이 되는 것입니다.
원래 수식으로 돌리기 위해 ESC키를 누릅니다.
(경기 2월의 총판매량 구하기)
위 수식에서 지점에 해당하는 부분을 월로 수정하면 됩니다.
따라서, 수식은 =SUMPRODUCT(Q2:Q9*(N2:N9=N12)*(P2:P9=O12))가 됩니다.
더할 범위를 먼저 Q2:Q9라고 쓸 수 있습니다. 위에서는 (Q2:Q9)이라고 괄호로 감쌌는데, 이와 같이 괄호로 묶지 않아도 됩니다.
조건은 (N2:N9=N12)*(P2:P9=O12)로 O2:O9가 P2:P9로 바뀌었고, 위치가 한 줄 아래로 내려갔으므로 N11과 O11이 N12와 O12로 바뀌었습니다.
(Sum + If 배열 수식을 이용하는 경우)
조건식이 여러 개 연결되어 있으므로 Sum+If 배열 수식을 사용할 수 있습니다.
서울, 강남의 총판매량을 구하는 수식은 =SUM(IF(($N$2:$N$9=N11)*($O$2:$O$9=O11),$Q$2:$Q$9))입니다. 지역과 지점, 판매량의 범위가 고정이기 때문에 절대참조 형식(예, $N$2:$N$9)으로 범위를 입력했습니다.
경기지역 2월의 총판매량을 구하는 수식은 R11의 수식을 복사한 후 $O$2:$O$9에서 O만 P로 수정하면 됩니다.
수식을 복사할 때 R11셀을 복사한 후 R12셀에 붙여 넣기를 해도 되지만 R11셀의 채우기 핸들을 R12셀까지 끄는 것이 편리합니다.
(SumIfs 함수를 이용하는 경우)
여러 가지 조건을 만족하는 범위의 합계를 구하는 것이므로 SumIfs함수를 사용할 수 있습니다.
S11셀에 입력하면 =SUMIFS($Q$2:$Q$9,$N$2:$N$9,N11,$O$2:$O$9,O11)이 됩니다.
SumIfs 함수의 구문은 (더할 범위, 조건 범위1, 조건1, 조건 범위2, 조건2 ...) 이므로
더할 범위로 Q2:Q9를 절대 참조형식으로 입력하고,
조건 범위1은 N2:N9인데 마찬가지로 고정이므로 절대 참조 형식으로 입력했으며,
조건1은 N11에 있는데, 수식을 아래 줄로 복사할 때 변경될 수 있도록 상대 참조형식으로 입력했습니다.
조건 범위2와 조건 범위2도 마찬가지로 o2:o9는 절대 참조형식, O11은 상대 참조형식으로 입력했습니다.
S12셀의 수식은 S11셀의 채우기 핸들을 아래로 끈 후 $O$2:$O$9에서 O를 P로만 바꾸면 됩니다.
'Excel' 카테고리의 다른 글
| Cell의 속성을 알려주는 Cell 함수 (1) (0) | 2023.04.29 |
|---|---|
| 두 개 이상 조건을 만족하는 값을 찾는 여러가지 방법 (0) | 2023.04.21 |
| 월말을 구해주는 EOMonth 함수 (2) | 2023.04.17 |
| WeekNum과 ISOWeekNum 함수 - 주 번호 반환 (0) | 2023.04.14 |
| DateValue, TimeValue : 텍스트 형식의 날짜, 시간을 정수 또는 소수로 변환 (0) | 2023.04.13 |