Sumifs함수는 다중 조건을 만족하는 합계를 계산하는 함수로 많은 사용자들이 주로 사용하는 함수로 활용도가 굉장히 높습니다.
이전 글에서는 이러한 Sumifs함수의 인수에 와일드카드(*,?,~)를 사용함으로써 찾고자 하는 조건의 텍스트를 포함하여 일치 하는 합계를 계산하는 수식을 작성해 봤습니다.
이번 글에서는 Sumifs함수의 찾을 조건 인수가 복수조건일때 수식을 어떻게 작성해야 원하는 결과값을 가져올 수 있는지 확인해 보도록 하겠습니다.
Sumifs함수 동일 참조 범위내 복수 조건 계산 수식.
예제와 같이 A열에는 품명, B열은 색상, C열에는 수량을 입력한 데이터에서 E열 품명, F열 색상을 만족하는 수량 합계를 결과값을 Sumifs함수를 사용해서 반환하고자 합니다.
사실 엑셀 함수를 다룰줄 알고 있다면 예제와같은 경우 Sumifs함수보다는 Sumproduct함수로 수식을 작성할것입니다.
Sumproduct함수로 결과값을 반환하는 수식.
Sumproduct함수로 수식을 작성해 보면 다음과 같이 작성할 수 있습니다.
|
찾을조건을 “그레이”와 “블랙”으로 작성해 주면 G2셀의 결과값은 나오지만 수식을 복사하는경우 G3, G4셀은 원하는 값을 반환하지 않습니다.
원하는 값을 반환하기 위해서는 G3셀의 찾을 조건은 “그레이” → “화이트”, “블랙” → “핑크”로 수정을 해야 하고 G4셀에서도 찾고자 하는 색상으로 수식의 일부분을 수정해줘야 하는 번거로움이 생기게 됩니다.
수식을 작성해서 결과값을 반환하는 과정은 수작업으로 찾고 계산하는 과정을 생략함으로써 업무 효율을 증대 시키기 위한것인데, 이렇게 작성한 수식의 조건을 하나하나 수정해준다면 그 의미가 없습니다.
따라서 찾을 조건 “그레이” → MID(F2,1,3)으로 수정하고, “블랙” → MID(F2,5,2)로 수정해 주면 찾을 조건의 텍스트를 일일이 수정해주는 번거로움을 줄일 수 있습니다.
다만 G4셀의 경우에는 찾을 조건의 데이터 입력을 블랙+화이트로 2글자와 3글자로 입력되어 있기 때문에 찾을 조건의 입력 데이터값을 화이트+블랙으로 수정해 주면 수식을 수정할필요는 없습니다.
만약 블랙+화이트로 입력된 데이터값으로 결과값을 반환하고자 한다면 MID($F4,1,2)와 MID($F4,4,3)으로 각각의 찾을조건의 수식을 수정할 수 있습니다.
Sumifs함수로 결과값을 반환하는 수식.
Sumproduct함수로 예제에서 반환하고자 하는 결과값의 수식을 작성할 수 있음에도 Sumifs함수로 예제의 결과값을 계산할 수 있는 수식을 작성해 보는 이유는 수식을 원활하게 다루는 사용자의 경우에는 Sumproduct함수로 수식구문을 작성하는것이 크게 어렵지 않겠지만 그렇지 않은 경우에는 Sumifs함수로 수식을 작성하는것이 더 수월하게 수식을 작성할 수 있기 때문입니다.
기본적인 Sumifs함수의 다중조건 합계 수식은 “엑셀 Sumifs함수 다중조건 합계 – 다중 텍스트 조건을 만족하는 합계.” 이전 글에서도 확인할 수 있듯이 품명이 티셔츠이고 색상이 그레이, 블랙인 수량의 합계는 =SUMIFS($C$2:$C$9,$A$2:$A$9,E2,$B$2:$B$9,”그레이”,$B$2:$B$9,”블랙”)과 같이 작성할 수 있습니다.
하지만 이렇게 작성한 수식의 결과값은 0 혹은 원하는 값으로 계산되지 않을텐데, 찾을 조건의 참조 범위가 B열로 동일하고 B열에서 찾을 조건이 2가지로 동일한 참조 범위에서 서로 다른 조건의 합계를 계산 하는 수식이 작동하지 않습니다.
원하는 결과값이 계산되도록 수식을 수정해 보면 =SUMIFS($C$2:$C$9,$A$2:$A$9,E2,$B$2:$B$9,”그레이”)+SUMIFS($C$2:$C$9,$A$2:$A$9,E2,$B$2:$B$9,”블랙”) 과 같이 각각의 조건을 찾아 합산해주는 형태로 수식을 작성해야 재대로 된 결과값을 반환할 수 있습니다.
위와 같은 수식은 2가지 수식을 합산하는 연산형태의 수식으로 1가지 수식으로 결과값을 반환하는 수식을 작성할수는 없을까?
이번 글을 작성하는 진짜 목적으로 찾고자 하는 인수를 And 조건으로 작성한다면 여러 수식을 합산하지 않고도 1개의 수식으로 작성할 수 있을것 같기도 합니다.
예를 들어 =if(A1=B1,1,0)은 A1셀과 B1셀이 같다면 1, 같지 않다면 0의 결과값을 반환하는 수식과 =if(and(A1=B1,C1=D1),1,0)수식으로 A1셀과 B1셀이 같아야 하고 C1셀과 D1셀도 같아야만 True값 1을 반환하게 됩니다.
따라서 sumifs함수의 찾을 조건을 And함수 수식으로 작성하면 결과값이 나올까?라고 생각할 수 있지만 막상 수식을 작성하다 보면 찾을 조건을 And수식으로 어떻게 작성할 수 있는지 막히게 됩니다.
Sumifs함수 1개의 수식안에서 동일한 참조 범위에서 2가지 이상의 찾을 조건을 만족하는 결과값이 나오도록 수식을 작성하는 방법은 찾을 조건의 인수를 배열로 작성하여 Sum함수로 합산해주게 되면 동일한 참조 범위 내에서 2가지 이상 조건을 만족하는 합계를 계산할 수 있습니다.
수식은 다음과 같이 작성할 수 있습니다.
| =SUM(SUMIFS(C:C,A:A,E2,B:B,{“그레이”,”블랙”})) |
위 수식에서 볼 수 있듯이 Sumifs함수 수식을 Sum함수의 인수로 작성했을때 찾을 조건 “그레이”, “블랙” 각각의 수량을 합산한 결과값으로 반한하게 되는것입니다.
글을 마치며…[행아아빠 생각]
사실 예제와 같은 결과값을 찾기 위해서는 본문에서도 언급했듯이 Sumproduct함수를 사용하여 수식을 작성하는것이 효율적일 수 있겠지만 배열 개념이 다소 서툴다면 수식 구문을 작성 했더라도 재대로 된 결과값이 반환되지 않을때 어느 부분을 수정해야 할지 모를 수 있습니다.
이에 비해 Sumifs함수 수식 구문은 간결하게 작동하기 때문에 많은 분들이 어렵지 않게 사용할 수 있는 함수라 생각됩니다.
다만, sumifs함수의 인수를 배열로 작성하는 경우에는 배열의 인수를 조건에 맞도록 일일이 수정해야 하므로 효율이 떨어질 수도 있기 때문에 실제로 업무에 적용하는 수식으로 활용하기 위해서는 배열로 작성해야 하는 조건들을 별도의 데이터표로 정의하거나 와일드카드를 사용하여 수식을 작성하는 방법도 생각해 볼 수 있겠습니다.
