안녕하세요. 행아아빠입니다.
이번 엑셀 내용에서는 자주 사용하는 함수 Sumif함수와 Sumifs함수에 대해서 간단하게 확인해 보고자합니다.
Sumif함수와 Sumifs함수는 합계를 계산하는 함수로 합계를 계산하기 위해서 조건이 있을 경우 활용할 수 있는 함수입니다. 그중에서도 조건이 1개로 단일 조건의 합계를 계산 하는 경우에는 Sumif함수를 사용하고, 조건이 2개 이상으로 다중 조건의 합계를 계산 하는 경우에는 Sumifs함수를 활용해서 수식을 작성하고 결과값을 표기 할 수 있습니다.
■ 행아아빠의 Youtube 채널.
● 주요 컨텐츠 주제 - 내일은로또왕 / 내일은엑셀왕
● Youtube 채널 주소 - https://www.youtube.com/@economic-freedom
■ 행아아빠 후원 안내.
● Youtube 채널 맴버십 후원 - https://www.youtube.com/channel/UCPmC4OZu99Rqguwz5QEINjA/join
● Youtube 댓글 후원 - Youtube 영상 댓글에서 "Super Thanks" 구매 후 댓글 작성.
● 내일은엑셀왕 대표 Youtube 영상 - https://youtu.be/oYVZHgv_sKM (2023년 급여 자동 계산 서식)
1. Sumif함수와 Sumifs함수의 수식 구문.
1-1. 단일 조건 합계 Sumif함수 수식 구문.
=Sumif(range, criteria, [sum_range])
● range : 조건을 찾을 데이터가 포함되어 있는 범위,
● criteria : 찾을 조건.
● [sum_range] : 결과값 데이터가 포함되어 있는 범위.
1-2. 다중 조건 합계 Sumifs함수 수식 구문.
=Sumifs(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
● sum_range : 결과값 데이터가 포함되어 있는 범위.
● criteria_range1 : 첫번째 찾을 조건 데이터가 포함되어 있는 범위.
● criteria1 : 첫번째 찾을 조건.
단일조건 합계 Sumif함수와 다중조건 합계 Sumifs함수의 수식 구문의 인수 배열이 비슷하지만 서로 다른 형태의 모습을 보여주는것으로 결과값이 포함되어 있는 범위를 마지막 인수로 사용하는지, 첫번째 인수로 사용하는지에 따라서 달라질 수 있습니다.
단일조건 개수 Countif함수와 다중조건 개수 Countifs함수의 인수 배열과는 약간 차이가 나는 부분이기 때문에 헷갈릴수 있는 요소라는것을 기억하시면 좋을것 같습니다.
단일조건합계는 다중조건합계 Sumifs함수로도 결과값을 계산해서 표기 할 수 있기 때문에 인수를 작성하는 위치가 틀려지는것에 대해 헷갈릴 소지가 있다면 다중조건합계를 계산 하는 Sumifs함수 1가지만이라도 기억하시면 여러분들이 엑셀 데이터를 함수와 수식으로 다루는데 큰 무리가 없겠습니다.
2. 단일조건 합계 Sumif함수와 다중조건 합계 Sumifs함수 수식 작성 예제.
1개의 조건을 만족하는 합계와 2개 이상의 조건을 만족하는 합계, 그리고 동일한 참조열에서 조건을 만족하는 모든 합계를 계산하는 수식을 작성해 보도록 하겠습니다.
A열에는 메뉴, B열에는 동, C열에는 판매수량을 입력했을때 ① 치킨의 판매수량 합계, 햄버거의 판매수량 합계, ② 치킨을 1동에 판매한 수량 합계와 햄버거를 2동에 판매한 수량 합계, 그리고 떡볶이를 3동에 판매한 수량 합계, ③ 마지막으로 치킨과 햄버거의 판매수량 합계를 계산 하는 수식을 작성하는것으로 단일조건 합계와 다중조건합계를 계산하는 수식을 다양한 형태로 작성해 보고자합니다.
① 치킨과, 햄버거의 판매수량 합계를 계산 하는 수식을 작성하면 다음과 같이 수식을 작성 할 수 있습니다.
| 1. H2셀 치킨의 판매 수량 합계 계산 수식. =SUMIF($A$2:$A$9,$G2,$C$2:$C$9) 2. H3셀 햄버거의 판매 수량 합계 계산 수식. =SUMIFS($C$2:$C$9,$A$2:$A$9,$G3) |
단일조건인 치킨과 햄버거 각각의 메뉴 판매수량 합계를 계산하는 수식을 Sumif함수와 Sumifs함수로 각각 작성을 하더라도 수식 구문의 인수를 작성하는 위치만 달라질뿐 결과값은 조건과 일치 하는 결과값을 계산해서 표기할 수 있습니다.
수식을 살펴 보면 H2셀에 치킨의 판매수량 합계를 계산 하기 위해서 단일 조건 합계를 계산 하는 Sumif함수로 수식을 작성하게 되면 조건:치킨을 찾을 범위 A열을 먼저 작성하고 A열에서 찾고자 하는 조건 치킨이 입력된 셀, 그리고 A열에 치킨이 입력된 합계 데이터가 입력되어 있는 C열을 입력해 주게 되면 치킨이라는 메뉴의 모든 판매 수량 합계를 계산 할 수 있습니다.
H3셀의 수식은 다중조건합계 Sumifs함수로 수식을 작성해 봤는데, 햄버거라는 단일조건을 다중조건으로 수식을 작성하더라도 원하는 결과값을 계산하여 표기할 수 있습니다.
Sumifs의 수식에 작성된 인수를 살펴 보면 합계데이터의 범위, G3셀의 데이터를 찾을 범위, G셀의 데이터가 입력되어 있는 A열에서 찾고자 하는 데이터가 입력된 G3셀로 수식을 작성할 수 있습니다.
조건이 있는 합계를 계산하는데 조건이 1개인 단일조건인지, 조건이 2개이상의 다중조건인지에 따라서 Sumif함수, Sumifs함수로 수식을 작성할 수 있지만 다중조건의 합계를 계산하는 Sumifs함수로 수식을 작성하더라도 단일조건의 합계를 계산하여 결과값을 표기 할 수 있습니다.
② 치킨을 1동에 판매한 수량 합계, 햄버거를 2동에 판매한 수량 합계, 떡볶이를 3동에 판매한 수량 합계 수식을 작성하면 다음과 같이 작성 할 수 있습니다.
| 1. H6셀 치킨을 1동에 판매한 판매수량 합계 수식. =SUMIFS($C$2:$C$9,$A$2:$A$9,$F6,$B$2:$B$9,$G6) 2. H7셀 햄버거를 2동에 판매한 판매수량 합계 수식. =SUMIFS($C$2:$C$9,$A$2:$A$9,$F7,$B$2:$B$9,$G7) 3. H8셀 떡볶이를 3동에 판매한 판매수량 합계 수식. =SUMIFS($C$2:$C$9,$A$2:$A$9,$F8,$B$2:$B$9,$G8) |
수식은 H6셀에만 작성한 후에 수식을 H8셀까지 복사해도 동일한 결과값을 계산할수 있습니다.
조건은 A열의 메뉴와 B열의 판매동수로 2개의 조건과 일치하는 C열의 판매수량 합계를 계산하고자 하는것이기 때문에 단일조건 합계 Sumif함수로는 2개의 조건에 일치하는 판매수량 합계를 계산할 수 없습니다.
따라서 다중조건의 합계를 계산하는 함수 Sumifs함수로만 그 결과값을 계산할 수 있는 수식을 작성해야 하고, 판매수량합계를 계산하고자 하는 C열, 메뉴를 찾아야할 범위 A열, A열에서 찾고자 하는 데이터값 조건, 판매 동수를 찾아야 할 범위 B열, B열에서 찾고자 하는 데이터값 조건으로 인수를 작성해서 결과값을 계산 할 수 있습니다.
③ 치킨과 햄버거의 판매수량 합계를 계산 하는 수식은 다음과 같이 3가지 방법으로 수식을 작성할 수 있습니다.
| 1. H11셀 각각의 단일조건 합계 Sumif함수 수식 결과값의 합계로 결과값을 계산하는 수식. =SUM(SUMIF($A$2:$A$9,$E11,$C$2:$C$9),SUMIF($A$2:$A$9,$F11,$C$2:$C$9)) 2. H12셀 각각의 다중조건 합계 Sumifs함수 수식 결과값의 합계로 결과값을 계산하는 수식. =SUM(SUMIFS($C$2:$C$9,$A$2:$A$9,$E12),SUMIFS($C$2:$C$9,$A$2:$A$9,$F12)) 3. H13셀 각각의 다중조건 합계 Sumifs함수의 찾을 조건을 and조건과 같은 형태로 작성해서 계산 하는 수식. =SUM(SUMIFS($C$2:$C$9,$A$2:$A$9,{"치킨","햄버거"})) |
메뉴의 판매수량 합계를 계산하는 수식으로 찾아야할 메뉴는 2가지인데, 찾아야할 범위가 동일한 경우에 조건합계 수식을 어떻게 작성할 수 있는지 알 수 있는 수식입니다.
②번의 수식에서는 조건이 2개이지만 각각의 조건을 찾기 위한 범위가 서로 다른 범위인데 비해서, 치킨과 햄버거의 조건2개를 찾는 범위가 동일한 A열에서 찾아야 한다는점에서 동일한 수식을 적용할 수 없다는것이 중요한 질문이라고 할 수 있습니다.
우리가 기본적으로 다중조건의 합계를 계산 하는 Sumifs함수 수식으로 치킨+햄버거의 판매수량 합계를 계산하는 수식을 작성하면 =SUMIFS($C$2:$C$9,$A$2:$A$9,E11,$A$2:$A$9,F11)와 같이 작성할 수 있지만 결과값은 "0"으로 계산하게 됩니다.
여기서 알 수 있는것은 합계를 계산하는데 다중조건일지라도 참조하는범위가 동일한 범위에서 2개이상의 다중조건을 찾아서 계산하는 합계는 다중조건합계로 계산할수 없다는것을 알 수 있습니다.
따라서 (치킨의 판매수량 합계 + 햄버거의 판매수량 합계)로 계산을 해야 원하는 결과값을 얻을 수 있기 때문에 각각의 단일조건의 합계 결과값을 서로 더해주게 되면 결과값을 얻을수 있기 때문에 1번수식 =SUM(SUMIF($A$2:$A$9,$E11,$C$2:$C$9),SUMIF($A$2:$A$9,$F11,$C$2:$C$9))과 같이 수식을 작성할 수 있고, 단일조건 합계는 다중조건합계 함수로도 작성할 수 있기 때문에 =SUM(SUMIFS($C$2:$C$9,$A$2:$A$9,$E12),SUMIFS($C$2:$C$9,$A$2:$A$9,$F12))와 같은 함수로도 작성할 수 있는것입니다.
마지막으로 3번에 작성된 수식은 찾고자하는 조건을 and조건과 같은 형태로 작성한 수식으로 찾고자 하는 조건 인수를 배열 형태로 작성하는것으로 A열에서 치킨과 햄버거의 판매수량을 Sum함수로 합계를 계산하도록 하는것입니다.
여기서 찾을 조건의 인수를 작성하는 형태를 {"조건1","조건2","조건3", ...}와 같이 작성해 줘야 하는것을 기억해준다면 다중 조건, 누적합계등의 수식으로 여러분들이 원하는 결과값을 다양한 형태의 데이터에 적용하실 수 있을것입니다.
④ 치킨+햄버거와 같이 입력된 데이터에서 치킨의 판매수량 합계, 햄버거의 판매수량 합계에 각각 합산해 주는 수식 작성 방법.
조건과 일치 하는 합계를 계산하는데 예제와 같이 셋트메뉴를 판매 하는 데이터입력을 "치킨+햄버거"와 같은 형태로 데이터를 입력했을때 치킨의 판매량 합계, 햄버거의 판매량 합계를 계산하기 위한 수식은 어떻게 작성할 수 있는지 확인해 보도록 합니다.
①번부터 ③번까지의 수식은 참조범위영역에서 찾고자 하는 조건과 정확히 일치하는 판매수량을 찾아서 그 합계를 계산한다면 이번에는 셋트메뉴와 같은형태로 치킨+햄버거로 치킨1개, 햄버거1개를 셋트로 해서 판매했을때 치킨의 판매수량에도 셋트 메뉴에서 판매한 수량합계를 합산하고, 햄버거의 판매수량에도 셋트 메뉴에서 판매한 수량 합계까지 포함해서 판매수량 합계를 계산하는 수식은 다음과 같이 작성할 수 있습니다.
| 1. H2셀 셋트메뉴에서 판매한 수량을 각각의 메뉴 판매 수량 합계에 합산해서 총 판매 수량을 계산 하는 수식. =SUMIFS($C$2:$C$11,$A$2:$A$11,"*"&$G2&"*") |
"치킨+햄버거"의 판매수량 2개는 치킨 총 판매수량에도 2개를 더해줘야 하고, 햄버거의 총 판매수량에도 셋트 메뉴에서 판매한 수량 2개를 더해줘야합니다.
하지만 데이터의 입력이 치킨, 햄버거와 같이 단일 조건과 일치하는 형태가 아닌 "치킨+햄버거"와 같이 조건과 조건 사이에 "+"라는 기호가 입력된 데이터이기 때문에 사실상 "치킨+햄버거"의 데이터를 찾아서 판매수량을 계산한 후에 각 메뉴의 판매수량 합계와 합산하는 수식을 단순화 하는 수식이라고 할 수 있습니다.
방법은 텍스트나누기, "치킨+햄버거"의 텍스트를 Left함수, Right함수, Mid함수등과 같이 텍스트를 메뉴 조건별로 분류한 뒤에 합산해 주는 방법등으로 시도해볼수도 있지만, 여러함수를 사용하는 경우에 원본 데이터를 편집해야 한다는 과정이 있고, 데이터가 많은 경우, 그리고 계속해서 입력되는 데이터가 늘어날경우 그때마다 원본 입력 데이터를 편집해야 하는 과정이 생긴다는것입니다.
따라서 찾고자 하는 조건을 "*"&찾는 텍스트 조건을 참조하는 셀 또는 찾고자 하는 텍스트 조건&"*"과 같이 찾는 조건을 작성해 주면 각각의 메뉴 판매 수량 합계를 계산할 수 있습니다.
오늘은 단일조건, 다중조건의 합계 함수 Sumif함수와 Sumifs함수 수식을 간단하지만 여러 형태로 작성된 데이터의 합계를 계산하는 수식으로 살펴봤습니다.
여러분들의 실제 엑셀 데이터에서도 활용해 보시길 바라겠습니다. 감사합니다.
Blog : https://hang-a-fafa.tistory.com
Youtube : https://www.youtube.com/@economic-freedom
Naver 까페 : https://cafe.naver.com/hatime
'엑셀' 카테고리의 다른 글
| 엑셀 급여 계산 사용자 조건에 맞는 급여 계산 서식 작성 방법. (0) | 2023.02.27 |
|---|---|
| 엑셀 자주 사용하는 함수 - index함수, match함수. (0) | 2023.02.24 |
| 엑셀 자주 사용하는 함수 - Countif함수, Countifs함수. (0) | 2023.02.20 |
| 엑셀 자주 사용 하는 함수 - Vlookup함수, Lookup함수 (0) | 2023.02.19 |
| 엑셀 자주 사용하는 함수 - 조건을 판별하는 if함수. (0) | 2023.02.17 |
댓글