엑셀에서 SUMIFS 함수를 이용하면 여러 개의 조건을 만족하는 합계를 구할 수 있는데,
오늘은 약간 응용하여 조건을 만족하는 월별 합계를 구하는 방법을 알아보겠습니다.
※ SUMIFS 함수 기본 사용법
[ 엑셀함수 SUMIFS – 여러 조건을 만족하는 범위의 합계구하기 ]
다음과 같이 판매실적 자료가 있다고 할 때 월별 판매실적을 구해보겠습니다.
1) 월별 판매수량 합계 구하기
[D19] 셀에 다음 수식을 입력합니다.=SUMIFS($E$5:$E$14,$B$5:$B$14,">="&DATE(B19,C19,1),$B$5:$B$14,"<="&EOMONTH(DATE(B19,C19,1),0))
수식이 정상적으로 입력되었으면 [D19]셀에 2019년 12월의 판매수량 합계 135가 구해 집니다.
[D19]셀의 수식을 복사해서 [D20]셀부터 [D22]셀까지 붙여 넣으면 최종적으로 다음과 같이 결과가 표시됩니다.
위 수식이 어떻게 작동되었는지 살펴보겠습니다.
정리해 보면 위 수식은 다음의 순서로 실행됩니다.
- 판매실적의 판매일이 ( criteria_range1, $B$5:$B$14)
12월1일보다 크거나 같고 ( criteria1, ">="&DATE(B19,C19,1) ) - 판매실적의 판매일이 ( criteria_range2, $B$5:$B$14)
12월31일보다 작거나 같은 ( criteria2, "<="&EOMONTH(DATE(B19,C19,1),0)) 것의 - 판매수량 합계 135를 판매수량 ( sum_range, $E$5:$E$14 ) 에서 가져옵니다.
월별로 합계를 가져오기 위해서는 "해당 월의 1일보다 크거나 같고 월말보다 작거나 같도록 조건을 설정"해야 하는데 위 수식에서 날짜를 지정하는 방법을 좀 더 살펴보겠습니다.
- ">="&DATE(B19,C19,1) : >=는 크거나 같음을 의미하고 정확한 날짜값이 필요하므로 셀에 입력된 값을 참조하여 DATE 함수로 날짜를 만듭니다. &는 >=과 뒤의 날짜를 연결해서 하나의 문자열로 만들어 주는 역할을 합니다.
- "<="&EOMONTH(DATE(B19,C19,1),0) : <=는 작거나 같음을 의미하고 월말에 해당하는 날짜값이 필요하므로 셀에 입력된 값을 참조하여 DATE 함수로 날짜를 만든 후 EOMONTH 함수로 월말에 해당하는 날짜를 만듭니다.
※ DATE함수, EOMONTH함수 사용법
2) 월별, 분류별 판매실적 합계 구하기
이번에는 월별 조건에 추가하여 분류별 판매실적 합계를 구해보겠습니다.
[E27]셀에 다음 수식을 입력합니다.=SUMIFS($E$5:$E$14,$B$5:$B$14,">="&DATE(B27,C27,1),$B$5:$B$14,"<="&EOMONTH(DATE(B27,C27,1),0),$C$5:$C$14,D27)
수식이 정상적으로 입력되었으면 [E27]셀에 2019년 12월의 노트류 판매수량 합계 135가 구해집니다.
[E27]셀의 수식을 복사해서 [E28]셀부터 [E30]셀까지 붙여 넣으면 최종적으로 다음과 같이 결과가 표시됩니다.
수식을 살펴보면 첫 번째 예 '월별 판매수량 합계 구하기'와 수식이 동일하고 마지막에 '분류' 조건만 추가되었습니다.
=SUMIFS($E$5:$E$14,$B$5:$B$14,">="&DATE(B27,C27,1),$B$5:$B$14,"<="&EOMONTH(DATE(B27,C27,1),0),$C$5:$C$14,D27)
- 판매실적의 판매일이 ( criteria_range1, $B$5:$B$14)
12월1일보다 크거나 같고 ( criteria1, ">="&DATE(B27,C27,1) ) - 판매실적의 판매일이 ( criteria_range2, $B$5:$B$14)
12월31일보다 작거나 같고 ( criteria2, "<="&EOMONTH(DATE(B27,C27,1),0)) - 분류가 ( criteria_range3, $C$5:$C$14 )
"노트류" ( criteria3, D27 ) 인 것의 - 판매수량 합계 135를 판매수량 ( sum_range, $E$5:$E$14 ) 에서 가져옵니다.
관련 글
DSUM 함수 - 데이터베이스에서 조건에 맞는 항목의 합계 구하기 - 엑셀의 데이터베이스(Database)함수는 다양한 조건에 맞는 합계를 구한다던지, 평균, 갯수 등을 쉽게 구할 수 있는 함수이다. 모든 함수는 알파벳 “D”로 시작하며… …
SUMIFS 함수 - 여러 조건을 만족하는 범위의 합계 구하기 - 엑셀에서 SUMIF 함수를 사용하면 한번에 하나의 조건을 만족하는 범위의 합계를 구할 수 있지만 SUMIFS 함수를 사용하면 여러 개의 조건을 만족하는… …
SUMIF 함수 - 조건을 만족하는 범위의 합계 구하기 - SUMIF 함수는 SUM 함수와 IF 함수가 합쳐진 형태입니다. "만약(IF) 어떤 조건을 만족하면 SUM하라"는 의미입니다. 이 함수는 자료에서 특정 조건을 만족하는… …
판매실적, 판매일이 1월1일보다 크거가 1월31일보다 작은 경우에는 수식이 똑같나요? 일자 기준 함수수식이 따로있는건지요?
안녕하세요. 아래 수식에서 등호(=)를 빼면 됩니다.
판매실적의 판매일이 ( criteria_range1, $B$5:$B$14 )
7월1일보다 크거나 같고 ( criteria1, “>=”&DATE(B19,C19,1) ) ▶ ( criteria1, “>”&DATE(B19,C19,1) )
판매실적의 판매일이 ( criteria_range2, $B$5:$B$14 ) )
7월31일보다 작거나 같고 ( criteria2, “<=”&EOMONTH(DATE(B19,C19,1),0)) ▶ ( criteria2, “<”&EOMONTH(DATE(B19,C19,1),0)) 혹시 수식이 작동안되면 쌍따옴를 키보드에 있는 것으로 입력해보세요. 감사합니다.
안녕하세요~
SUMIFS강좌 관련, 아래 실무 예제 문의 드립니다.
1월~12월 판매 실적이 있고, 특정 월까지의 누계 데이터만 합계를 내고 싶은데, 이 경우 SUMIFS 함수로 가능할까요?
예)
월 1월 2월 3월 4월 5월 6월 7월 8월 9월 10월 11월 12월 합계
판매량 1 2 3 4 5 6 7 8 9 10 11 12 78
A1셀에 "3"이라고 치면 1~3월까지의 데이터만 합계에 보여주고자 합니다.
이럴 때 어떻게 수식을 걸면 될까요? (아래와 같이 했는데 안되네요..)
=SUMIFS($C$2:$N$2,$C$1:$N$1,">="&DATE(,$A$2,),$C$1:$N$1,"<="&EOMONTH(DATE(,$A$2,),0))
수식의 형태는 맞는데 날짜값이 일치하지 않아서 안됩니다. DATE(,월,) 이렇게 하면 1900년의 월로 인식합니다. 그리고 월 타이틀에 어떻게 입력하셨는지 모르겠지만 "1월"이라고 입력하면 월값으로 인식되지 않습니다.
타이틀의 월값은 2020-2-1의 형태로 월을 정확히 입력하시고
A4에 시작월(2020-3-1의 형태로), A5에 종료월을 입력한 후
수식을 다음과 같이 해 보세요.
=SUMIFS(B2:M2,B1:M1,">="&A4,B1:M1,"<="&A5)
a b c d e f g H I 금액 J날짜
1 10,174 2021-01-12
2 37,380 2021-01-15
3 16,020 2020-12-31
4 10,219 2020-12-29
5
6
7 H년 I월 J월별합계
8 2020 12 47,554
9 2021 1 26,239
J1 날짜 함수
IF(G76>0,VLOOKUP(A76,부가신고내역!A:N,4,0),"")
다른곳에서 날짜를 끌고와야 해서 함수가 있습니다.
J8 월별합계함수
SUMIFS(I1:I4,J1:J4,">="&DATE(H8,I8,1),J1:J4,"<="&EOMONTH(DATE(H8,I8,1),0))
J8과 J9에 합계액이 나와야 하는데 그냥 0만 나옵니다.
뭐가 틀렷을까요?
입력된 data를 정확히 확인하기가 어렵네요 ㅠ... 파일을 admin@xlworks.net으로 보내주시면 확인하고 답변드리겠습니다.
좋은 자료 덕분에 가운데 위치한 값의 합을 찾을 수 있었고, 조건에 수식(셀 위치)을 넣으려고 했는데 "&"덕분에 가능했습니다.
감사합니다.
"&" 효자네요
제대로 확인해서 넣었느데 값이 0만 나오는 이유가 뭘까요 ㅜㅜ ?
sumif, sumifs 함수에서 결과가 0이 나올 때는 대부분 수식에서 연산자와 값 연결이 잘못된 경우입니다.
예를 들어서
=SUMIF(E5:E12,">"&E16,E5:E12) => 1,700,000 가 나오는 수식이 맞는데
위 수식을 다음과 같이 &(앰퍼샌드)를 사용하지 않고 수식을 입력하면 0이 나옵니다.
=SUMIF(E5:E12,">E16",E5:E12) => 0
자세한 내용은 다음 링크를 참고하세요.
https://xlworks.net/excel-formula-operator/
새해를 맞아 가계부 양식을 조금 바꿔보았는데, 작업에 큰 도움이 되었습니다. 감사합니다~!
시트가 두개에서하는방법은 어떻게 수식을 넣어야할까요?
다음 글을 참고하시면 됩니다.
[엑셀에서 다른 시트 참조하기]
https://xlworks.net/excel-formula-other-sheet-reference/
좋은자료 감사합니다.
위 시트에서 F칸에 국산/수입 추가도 가능한가요??
월별 노트류 수량을 뽑았는데. 거기서 국산/수입 수량을 알고싶습니다.
다음과 같이 하시면 됩니다. 국산, 수입 조건은 직접 입력해도 되고 셀에 입력해서 참조해도 됩니다.
=SUMIFS($E$5:$E$14,$B$5:$B$14,">="&DATE(B34,C34,1),$B$5:$B$14,"<="&EOMONTH(DATE(B34,C34,1),0),$C$5:$C$14,D34,$F$5:$F$14,"국산")
답글 감사합니다.
=SUMIFS($E$5:$E$14,$B$5:$B$14,">="&DATE(B27,C27,1),$B$5:$B$14,"<="&EOMONTH(DATE(B27,C27,1),0),$C$5:$C$14,D27)
요 데이터랑 같은 값이 나오네요....
아!! 확인됐습니다!! 감사합니다.!!! ^^
항상 좋은자료 감사드립니다
특정날짜를 입력했을시 그날짜칸에 색상 기입이 되는 함수는 없는지요???
가로로 1일~30일짜리 칸을 만들고 세로로 아이템 및 출하일자를 입력하려고 합니다.
안녕하세요. 조건부 서식을 사용하시면 됩니다. 다음 글을 참고하세요.
https://xlworks.net/conditional-formatting-with-formulas/
조건값에 날짜를 넣어야 하는데 DATE함수를 사용해서 다음과 같이 입력합니다.
예) 10월 1일이면 DATE(2024,10,1)