엑셀에서 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 )
7월1일보다 크거나 같고 ( criteria1, ">="&DATE(B19,C19,1) ) - 판매실적의 판매일이 ( criteria_range2, $B$5:$B$14 ) )
7월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 )
7월1일보다 크거나 같고 ( criteria1, ">="&DATE(B19,C19,1) ) - 판매실적의 판매일이 ( criteria_range2, $B$5:$B$14 ) )
7월31일보다 작거나 같고 ( criteria2, "<="&EOMONTH(DATE(B19,C19,1),0)) - 분류가 ( criteria_range3, $C$5:$C$14 )
"노트류" ( criteria3, D27 ) 인 것의 - 판매수량 합계 135를 판매수량 ( sum_range, $E$5:$E$14 ) 에서 가져온다.
[ 수학 및 삼각 함수 ]
- 엑셀에서 SUMIFS 함수로 월별 합계 구하기
- 엑셀함수 SUBTOTAL - 목록이나 데이터베이스의 부분합(집계결과) 구하기
- 엑셀함수 EVEN - 가장 가까운 짝수로 올림,내림하기
- 엑셀함수 ODD - 가장 가까운 홀수로 올림,내림하기
- 엑셀함수 GCD - 최대공약수 구하기
- 엑셀함수 LCM - 최소공배수 구하기
- 엑셀함수 SIGN - 수의 부호 구하기
- 엑셀함수 MMULT - 두 배열의 행렬 곱 구하기
- 엑셀함수 MINVERSE - 정방행렬의 역행렬 구하기
- 엑셀함수 MDETERM - 배열의 행렬식 구하기
- 엑셀함수 TRUNC - 숫자에서 지정된 자릿수까지만 남기기(절사하기)
- 엑셀함수 POWER – 숫자의 거듭제곱 구하기
- 엑셀함수 PI(파이) - 원주율 구하기
- 엑셀함수 FACT - 팩토리얼(계승값) 구하기
- 엑셀함수 INT - 숫자를 가장 가까운 정수로 내림하기
- 엑셀함수 SQRT - 양의 제곱근 구하기
- 엑셀함수 LOG,LOG10,LN - 로그값 구하기
- 엑셀함수 EXP - 상수 e를 지수만큼 거듭제곱하기
- 엑셀함수 BASE - 10진수를 다른 진수로 바꾸기
- 엑셀함수 MROUND - 배수값으로 숫자 반올림하기
- 엑셀함수 CEILING, FLOOR - 배수값으로 숫자 올림,내림하기
- 엑셀에서 난수 구하기 - RAND, RANDBETWEEN 함수
- 엑셀에서 몫과 나머지 구하기 - QUOTIENT, MOD 함수
- 엑셀에서 숫자 반올림,올림,내림하기 - ROUND, ROUNDUP, ROUNDDOWN
- 엑셀함수 ABS - 절대값 구하기
- 엑셀함수 SUMPRODUCT - 숫자를 모두 곱하고 합계 구하기
- 엑셀함수 PRODUCT - 숫자를 모두 곱하기
- 엑셀함수 SUMIFS - 여러 조건을 만족하는 범위의 합계구하기
- 엑셀함수 SUMIF - 조건을 만족하는 범위의 합계구하기
- 엑셀함수 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)