엑셀(EXCEL)의 더 강력한 조건부 함수 SUMIFS 함수, SUMPRODUCT(배열함수 사용법), 조건합계를 계산

SUM함수에 대해서 지난번에 집어 봤습니다. 간단한 상황이라면 SUMIF로 대부분 해결됩니다.
https://toast-story.tistory.com/13

엑셀(EXCEL)의 합을 계산하는 SUM 함수와 조건부 합계함수 SUMIF, SUMIFS

 
컴퓨터에게 연산을 시킬 때는 최대한 단순한게 좋습니다.
그러나 상황에 따라서 2개 이상의 제약조건을 걸어 복잡해지져야 합니다.
그럴 때 사용하는 SUMIFS 함수입니다.
 

 

1. SUMIFS 함수

 
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) 
 : 조건들을 입력하여 덧셈을 구합니다. 조건은 127개를 입력할 수 있어서 매우 긴 함수가 만들어질 수 있습니다.
최소한의 1개의 범위가 필요한데 어지간하면 많아도 3개 정도면 충분한데 그래도 함수가 많이 깁니다. 오류를 배제하기 위해 신중하게 만들어야 합니다.

• sum_range : 합계를 구할 셀의 범위입니다.
• criteria_range1 : 조건을 검사할 범위입니다. 범위에서 항목이 발견되면 sum_range에 있는 해당 값을 더합니다.
                            sum_range와 행 또는 열을 공유하여 오류가 나지 않도록 합시다.
• criteria1 : 조건입니다. 그냥 단일한 값이면 "=" 연산으로 계산하지만 다른 논리연산자도 입력할 수 있습니다.
• criteria_range2, criteria2, : 추가적인 조건입니다. criteria_range와 criteria의 순서로 연속적으로 입력하여야 합니다.

 
오른쪽의 표에 여러 부분에 조건을 거는 예시를 보여드리겠습니다.
 
① range에는 먼저 덧셈을 수행할 E4:E29 범위를 지정합니다.
② 조건 첫번째로 등급 D4:D29에서 A를 추출합니다. 텍스트를 직접 입력할 때는 쌍따움표 ""를 사용해서 입력합니다.
③ 이름인 C4:C29 안에서 "사과"를 셀번호 i4로 검색하도록 합니다.
④ 날짜항목에서는 "<="연산자를 &와 조건을 묶어서 "2월 8일 이전에"라는 조건식을 작성합니다.
⑤ 목록에서 "2월 8일 이전에 A 등급, 사과"의 수를 합하는 함수가 만들어졌습니다.
설명하자니 정말 길어지네요. 아래를 보겠습니다.
 

 

320x100

 
 

2. SUMPRODUCT 함수

 
이 함수는 본래 사용법과 배열함수로서 사용법이 있습니다.
배열함수로 사용하면 오늘의 주제이 조건합계를 구할 수 있습니다.
원래 사용법부터 알아보겠습니다. 기능은 "곱해서 더한다"입니다.
 
SUMPRODUCT(array1, [array2], [array3], ...) : 범위의 숫자들을 첫번째부터 곱해서 더합니다.
 
array1은 {1,2,3,4}이고 array2는 {5,6,7,8}입니다.
SUMPRODUCT로 이 두 배열을 연산하면 1*5 + 2*6 + 3*7 + 4*8이 됩니다.
단위변환 같은걸 할때 편리하게 사용할 수 있습니다.

 
배열함수로 사용하는 방법을 보겠습니다.
위에서 본 "2월 8일 이전에 A 등급, 사과"의 수를 합하는 함수를 SUMPRODUCT로 만들어 보겠습니다.
생각보다 간단한데요. SUMPRODUCT((조건1) * (조건2) *더할범위) 배열함수라서 Ctrl+Shift+Enter로 입력해야 합니다.
조건은 ()안에서 작성되어야 하고 열과 행이 맞아야 합니다. 그리고 조건 안에 오류가 하나도 없어야 합니다.
 

 
이 배열 함수는 하나씩 연산합니다. 
리스트를 하나씩 보면 "이름 = 사과" = trure(1), "등급 = A" = true(1)가 되니까 연산해서 1 x 1 x 20(갯수) = 20이 됩니다.
조건이 하나라도 틀리면 "이름 = 망고" = False(0)가 되서 0이 되기 때문에 더해지지 않습니다.
그럼 조건이 만족하는 수는 남게되고 결과적으로 조건합을 얻을 수 있습니다.
이런 원리로 SUMIFS와 같은 결과를 얻을 수 있습니다.
 

SUMPRODUCT 배열함수 원리

 

---

 
예전 버전의 엑셀에서는 SUMIF가 없었습니다.
SUMPRODUCT의 배열함수를 통해서 사용했었죠. 엑셀이 보다 대중화 되면서 SUMIFS가 업데이트 되었습니다.
따라서 지금은  SUMPRODUCT는 사용할 필요가 없습니다.
그래도 아직도 사용하시는 분들이 많아 한번 설명해 보았습니다.
새로 배우시는 분들이라면 SUNIF와 SUNIFS에 익숙해지는걸 추천합니다.
개인적으로는 좋아하거든요. 전혀다른 기능 두개가 합쳐서서 새로운 가능성이 보이는 것이 좋죠.