안녕하세요. 일상에서 얻는 정보를 모두 기억할 수 없기 때문에 일상에서 일어나는 에피소드들의 정보들을 기록하여 기억하고자 하는 시점에 기록된 정보를 활용할 수 있도록 글을 쓰고자 하는 이작가입니다.
저 역시 지나간 모든 과거의 경험들을 전부 기억할 수 없기 때문에 경험을 통해 얻은 지식과 정보를 기록함으로써 필요한 시점에 과거의 경험의 정보를 활용해 보기 위해 이 글을 작성합니다.
기록된 정보들이 많은 분들에게 도움이 될 수 있도록 전달될 수 있길 바라겠습니다.
목차
엑셀(excel) 데이터 작업을 하면서 합계 계산은 기본적으로 많이 사용하는 계산으로 합계를 계산 하는 함수는 굉장히 많지만 일반적으로 사용하는 합계 함수는 sum, sumif, sumifs, sumproduct함수 정도로 볼 수 있습니다.
그중에서도 이번 엑셀(excel) 시간에서 다룰 함수는 sumifs함수로 여러 조건과 일치 하는 값의 데이터 합계를 계산 하는것으로 다중 조건 합계를 계산 하는 수식 작성이라고 할 수 있을것 같습니다.
실제로 다중 조건 합계의 경우에는 굉장히 자주 사용하고 활용도가 높다고 할 수 있는 함수로 날짜별 합계, 주차별 합계, 시간별 합계, 기간별 합계, 주간 합계등과 같이 합계를 계산 하지만 조건과 일치 하는 합계만을 계산 하고자 할때 사용하는 함수입니다.
그러나 조건은 단일 조건 뿐만 아니라 여러가지 조건과 일치하는 데이터의 값 합계를 계산 하고자 하는 경우가 더 많을 텐데 날짜와 이름이 일치 하는 합계, 기간과 시간이 일치하는 합계등과 같은 여러 조건을 동시에 만족하는 데이터 값 합계를 계산 할때 sumifs함수를 사용하면 되는것이고, 단일 조건의 합계는 sumif함수를 사용해서 조건 합계를 계산 할 수 있습니다.
앞서 다중 조건 합계 sumifs함수를 응용해서 작성 할 수 있는 수식을 작성해 보았는데 오늘 내용과 함께 확인해 보시면서 오늘 내용도 도움이 될 수 있는 엑셀(excel) 시간이 되기를 바라겠습니다.
① 엑셀(Excel) sumifs or and 다중조건 합계의 일부 조건이 or함수 또는 And함수로 작성 해야 할때 수식 작성.
1. 조건 합계 sumif, sumifs 함수.
조건 합계를 계산 수식을 작성해보기 위해 샘플 예제 서식을 작성 했는데 A열부터 E열까지 각각 날짜, 이름, 종류, 수익/지출, 금액 순으로 무작위 데이터를 입력하고, G열부터 J열까지는 조건 합계를 계산 하기 위한 날짜, 이름, 종류, 수익/지출의 조건들을 입력하여 K열에 단일 조건 합계 sumif함수와 다중 조건 합계 sumifs함수로 수식을 각각 작성해서 결과값을 확인해 보기로 합니다.
1-1. 단일 조건 합계.
먼저 단일 조건의 합계를 계산 하는 수식을 작성해 보기 위해서 몇가지 단일 조건을 만들어 보겠습니다.
① 수익 총 합계.
② 지출 총 합계.
③ 2022-11-01 총 지출 합계.
④ 주유비 총 합계.
⑤ 이작가 지출 총 합계.
위 단일 조건의 합계 수식을 작성해 보면 K2셀에 다음과 같이 작성 할 수 있습니다.
| ① 수익 총 금액 합계. → =SUMIF($D$2:$D$24,"수익",$E$2:$E$24) ② 지출 총 금액 합계. → =SUMIF($D$2:$D$24,"지출",$E$2:$E$24) ③ 2022-11-01 총 금액 합계. → =SUMIF($A$2:$A$24,"2022-11-01",$E$2:$E$24) ④ 주유 총 금액 합계. → =SUMIF($C$2:$C$24,"주유",$E$2:$E$24) ⑤ 이작가 총 금액 합계. → =SUMIF($B$2:$B$24,"이작가",$E$2:$E$24) |
수식을 확인해 보면 우선 모두 금액의 합계를 계산 하는것이기 때문에 금액 데이터가 입력된 E열을 참조 하는 범위는 동일한 인수로 작성을 하고, 조건에 따라 A, B, C, D열의 참조 영역에서 각각의 조건과 일치 하는 데이터 값 ①번부터 "수익", "지출", "2022-11-01", "주유", "이작가"의 조건 데이터를 작성해 주는것으로 원하는 결과값을 반환하여 표기 할 수 있습니다.
작성된 수식의 인수를 보게 되면 sumif(조건을 찾을 데이터가 작성된 참조 범위 영역, 찾을 조건 데이터 값, 합계 금액의 참조 범위 영역)으로 작성 하여 단일 조건과 일치 하는 합계를 계산 하여 결과값으로 가져 올 수 있습니다.
만약 직접 입력해준 조건을 특정 셀에 입력 했다면 "수익", "지출", "2022-11-01", "주유", "이작가"로 입력한 찾을 조건 값을 특정 조건을 찾을 데이터 값이 입력된 셀주소로 작성해 주면 되는데 만약 G2셀에 2022-11-01의 값을 입력 했다면 ③ 2022-11-01 총 지출 합계를 계산 하기 위한 수식 =SUMIF($A$2:$A$24,"2022-11-01",$E$2:$E$24)은 =SUMIF($A$2:$A$24,$G2,$E$2:$E$24)와 같이 "2022-11-01"의 직접 입력값을 G2셀로 참조 할 셀 주소를 입력해 주는것으로 대신 할 수 있습니다.
1-2. 다중 조건 합계.
다중 조건의 합계는 1개 이상의 조건과 일치 하는 합계를 결과값으로 표기 하여 반환 하는 것으로 조건이 1개 이상일때 sumifs함수로 수식을 작성하여 다중 조건에 일치 하는 합계를 계산 할 수 있습니다.
다중 조건 합계를 샘플 예제 서식에서 작성해 보고 결과값을 확인해 보기 위해서 1개 이상의 단일, 다중 조건을 다음과 같이 5가지로 만들어 봤습니다.
① 비상약 금액 합계.
② 이작가 지출 합계.
③ 글쓰는 주유비 합계.
④ 2022-11-01날짜에 이작가 지출 합계.
각각의 수식을 작성해 보면 다음과 같이 작성 할 수 있습니다.
| ① 비상약 금액 합계. → =SUMIFS($E$2:$E$24,$C$2:$C$24,"비상약") ② 이작가 지출 합계. → =SUMIFS($E$2:$E$24,$B$2:$B$24,"이작가",$D$2:$D$24,"지출") ③ 글쓰는 주유비 합계. → =SUMIFS($E$2:$E$24,$B$2:$B$24,"글쓰는",$C$2:$C$24,"주유") ④ 2022-11-01날짜에 이작가 지출 합계. → =SUMIFS($E$2:$E$24,$A$2:$A$24,"2022-11-01",$B$2:$B$24,"이작가",$D$2:$D$24,"지출") |
다중 조건 합계를 계산 하는 sumifs함수로 각 조건들과 일치 하는 금액 합계를 계산하는 수식으로 단일 조건부터 다중 조건까지 모두 계산 할 수 있는 수식으로 sumif함수의 단일 조건을 포함해서 사용할 수 있다는 점에서 좀 더 실용적인 함수라고 할 수 있습니다.
또한 sumif함수 수식 구문의 인수 작성 순서가 변경되는데 sumif함수는 SUMIF(조건 참조 영역, 조건, 합계 참조 영역)으로 작성되는것과 다르게 sumifs함수 수식 구문 인수 작성 순서는 SUMIFS(합계 참조 영역, 조건 참조 영역, 조건, 조건 참조 영역, 조건, 조건 참조 영역, 조건...)으로 작성한다는 점에서 수식 구문의 인수 작성 순서에 차이점을 발견할 수 있습니다.
또한 샘플 예제 서식에서 수식 작성은 찾을 조건을 직접 입력하여 "비상약"과 같이 직접 작성해 주었는데 찾을 조건 데이터 값이 입력된 셀을 참조 하고자 한다면 "비상약"과 같이 직접 입력한 조건 값을 찾을 조건 데이터 값이 입력된 셀 주소를 입력해 주어 수식을 작성할 수 있습니다.
위 참조 이미지에서 보는것과 같이 각각 조건과 일치 하는 단일 조건, 다중 조건의 합계 수식을 작성 하기 위한 조건들을 G열부터 J열까지 조건을 입력해준 셀 주소를 "비상약"과 같은 직접 입력 값 대신 작성해 주면 다음과 같은 수식을 작성 할 수 있고 동일한 결과값을 반환하여 가져올 수 있습니다.
| ① 비상약 금액 합계. → =SUMIFS($E$2:$E$24,$C$2:$C$24,$I2) ② 이작가 지출 합계. → =SUMIFS($E$2:$E$24,$B$2:$B$24,$H3,$D$2:$D$24,$J3) ③ 글쓰는 주유비 합계. → =SUMIFS($E$2:$E$24,$B$2:$B$24,$H4,$C$2:$C$24,$I4) ④ 2022-11-01날짜에 이작가 지출 합계. → =SUMIFS($E$2:$E$24,$A$2:$A$24,$G5,$B$2:$B$24,$H5,$D$2:$D$24,$J5) |
간단하지만 유용하게 활용 할 수 있는 합계함수, 단일 조건, 다중 조건의 합계를 계산 하는 sum, sumif, sumifs함수의 활용 방법에 도움이 될 수 있기를 바라겠습니다.
이상으로 "글쓰는이작가의 엑셀(Excel) 시간"은 마무리 하고,
"합계 함수 단일 조건 합계 다중 조건 합계 수식 작성" 예제 샘플을 하단에 첨부하였으니 필요한곳에서 활용할 수 있는 수식으로 적용해 보시길 바라겠습니다.
긴 글 읽어주신 여러분들께 감사 말씀 드리며 많은 도움이 될 수 있기를 바라겠습니다.
댓글