엑셀 Sumifs함수 다중조건 합계 – 다중 텍스트 조건을 만족하는 합계.

엑셀 함수로 Sumifs, Countifs와 같은 함수는 다중 조건을 만족하는 합계, 다중 조건을 만족하는 개수를 구하는 함수로 굉장히 보편적으로 활용되는 함수라고 할 수 있습니다.

이번 글에서는 Sumifs함수로 다중 조건의 합계를 계산하는 수식을 작성해 볼텐데 특히 한 셀안에 다중 텍스트, 예를 들면 “품명1+품명2″와 같이 입력되어 있을때 각각의 품명에 해당하는 합계를 계산하는 수식을 작성해 보고자합니다.

Sumifs함수 기본 수식 구문.

Sumifs함수는 다중 조건 합계를 계산하는 수식을 작성할 수 있는 함수로 Sumif는 단일 조건의 합계를, Sum함수는 참조범위의 합계 계산하는 함수로 여러 조건을 만족하는 합계를 계산할 수 있다는점에서 합계와 관련한 문서 서식을 다루는 사용자라면 반듯이 기억해야할 함수라고도 할 수 있습니다.

Sumifs함수 수식 구문은 다음과 같습니다.

Sumifs(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, …) sum_range – 합계를 계산할 참조 범위. criteria_range1 – criteria1(조건1)을 찾을 참조 범위. criteria1 – criteria_range1(조건1을 찾을 참조범위)에서 찾을 조건. → Sumifs(합계를 계산할 참조 범위, 조건1을 찾을 참조 범위, 조건1…) ※ 참조범위의 행 단위는 모든 인수가 동일해야 함. Ex). sumifs(A1:A10, B2:B11, C1) → X sumifs(A1:A10, B1:B10, C1) → ○

참조 예제 이미지에서 보는것과 같이 A열에 상품명, B열에는 개수를 입력한 데이터가 있을때 D열에 찾을 조건, E열에 개수 합계를 계산하는 수식을 작성하면 합계를 계산할 참조 범위는 B열, D2셀의 “상품1″을 찾을 참조 범위 A열, 찾을 조건 D2셀로 수식으로 작성하면 다음과 같이 작성 할 수 있습니다.

=SUMIFS($B:$B,$A:$A,$D2)

위와 같이 수식을 작성할 경우 결과값은 A2셀 상품1의 개수 5를 반환하게 됩니다.

그러나 A5 ~ A7셀에는 “상품@+상품@”와 같이 여러 상품명이 동시에 입력되어 있고, A5, A6셀에도 “상품1″의 개수가 포함된 개수이기 때문에 “상품1″의 개수 합은 A2, A5, A6 개수의 합계 5+3+1=9가 되어야 합니다.

Sumifs함수 다중 텍스트 조건을 만족하는 계산 수식.

A5셀 입력 데이터 “상품1+상품2″의 개수가 3이라는것은 상품1의 개수가 3개, 상품2의 개수도 3개라는것으로 각각의 상품명 개수에 포함을 시켜서 합계를 계산해야 합니다.

이렇게 여러 텍스트로 데이터를 입력했을때 각각의 개수로 합계를 계산할때 필요한것으로 “와일드카드”를 수식에 작성해줌으로써 원하는 결과값을 반환할 수 있습니다.

※ 와일드카드. 와일드 카드란 *, ?, ~과 같은 기호들을 와일드 카드라고 합니다. ▼ *를 와일드 카드로 적용했을때. A* = A로 시작하는 모든 문자열. *A = A로 끝나는 모든 문자열. *A* = A를 포함하는 모든 문자열. ▼ ?를 와일드 카드로 적용했을때. A? = 첫 문자가 A인 두글자 문자열. A?? = 첫 문자가 A인 세글자 문자열. ?A = 마지막 문자가 A인 두글자 문자열. ??A = 마지막 문자가 A인 세글자 문자열. ?A? = 두번째 문자가 A인 세글자 문자열. ?A?? = 두번째 문자가 A인 네글자 문자열. ??A? = 세번째 문자가 A인 네글자 문자열.

예제에서 찾고자 하는 문자 조건은 상품1, 상품2, 상품3이기 때문에 사용할 수 있는 와일드카드는 *A*과 같이 와일드 카드를 적용할 수 있습니다.

따라서 E2셀에 상품1의 개수 합계를 5+3+1로 계산한 결과값을 반환하기 위한 수식은 다음과 같이 작성할 수 있습니다.

=SUMIFS($B:$B,$A:$A,”*”&D2&”*”)

찾을조건 상품1은 D2셀의 데이터값을 참조하면 되고, 상품1이란 데이터가 입력된 모든 문자열을 찾을것이기 때문에 *상품1*과 같이 와일드카드를 적용할 수 있습니다.

결국 상품1이란 문자의 조건을 포함하고 있는 모든 개수 합계를 계산 하기 위해서는 *D2*으로 작성할 수 있지만 찾을 조건 데이터값이 텍스트와 수식으로 연결되어야 하기 때문에 &(앰퍼스트) 기호로 “*”&D2&”*”과 같이 찾을 조건을 작성할 수 있습니다.

글을 마치며…[행아아빠 생각]

Sumifs함수 자체만으로도 여러 조건들을 만족하는 합계를 계산 할 수 있기 때문에 활용도가 굉장히 높다고 생각되고 필자 역시도 수식으로 자동 계산 문서 서식을 작성하던 초기 즐겨 사용하던 함수입니다.

그러나 계산식이 복잡해 지고 참조해야할 데이터들의 연관 관계가 복잡해짐에 따라 활용도가 점차 낮아질 수 있겠지만 기본 수식 구문의 인수들을 조금만 활용할 수 있다면 연관 관계가 복잡한 데이터를 참조하더라도 원하는 결과값을 반환하는 수식을 작성할 수 있으리라 생각됩니다.

엑셀 함수와 수식이 엄청 대단한것을 코딩하는 언어가 아니고 계산식을 독해 하는 능력과 응용력으로 자신이 활용할 수 있는 함수를 최대한 활용한다면 생각보다 여러분들의 업무 효율은 증대될것이라 생각합니다.