SUMIFS 함수로 월별 합계 구하기

엑셀에서 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]셀까지 붙여 넣으면 최종적으로 다음과 같이 결과가 표시된다.

 

위 수식이 어떻게 작동되었는지 다음 그림을 보고 이해해 보자.

정리해 보면 위 수식은 다음의 순서로 실행된다.

1. 판매실적의 판매일이 ( criteria_range1, $B$5:$B$14)
   7월1일보다 크거나 같고 ( criteria1, ">="&DATE(B19,C19,1) )
2. 판매실적의 판매일이 ( criteria_range2, $B$5:$B$14) 
   7월31일보다 작거나 같은 ( criteria2, "<="&EOMONTH(DATE(B19,C19,1),0)) 것의
3. 판매수량 합계 135를 판매수량 ( sum_range, $E$5:$E$14 ) 에서 가져온다.

 

월별로 합계를 가져오기 위해서는 "해당 월의 1일보다 크거나 같고 월말보다 작거나 같도록 조건을 설정"해야 하는데 위 수식에서 날짜를 지정하는 방법을 좀 더 살펴보자.

• ">="&DATE(B19,C19,1)  : >=는 크거나 같음을 의미하고  정확한 날짜값이 필요하므로 셀에 입력된 값을 참조하여 DATE함수로 날짜를 만든다. &는 >=과 뒤의 날짜를 연결해서 하나의 문자열로 만들어 주는 역할을 한다.
• "<="&EOMONTH(DATE(B19,C19,1),0) : <=는 작거나 같음을 의미하고  월말에 해당하는 날짜값이 필요하므로 셀에 입력된 값을 참조하여 DATE함수로 날짜를 만든 후 EOMONTH함수로 월말에 해당하는 날짜를 만든다.

 

※ 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)

1. 판매실적의 판매일이 ( criteria_range1, $B$5:$B$14)
   7월1일보다 크거나 같고 ( criteria1, ">="&DATE(B19,C19,1) )
2. 판매실적의 판매일이 ( criteria_range2, $B$5:$B$14))
   7월31일보다 작거나 같고 ( criteria2, "<="&EOMONTH(DATE(B19,C19,1),0))
3. 분류가 ( criteria_range3, $C$5:$C$14 )
   "노트류" ( criteria3, D27 ) 인 것의 
4. 판매수량 합계 135를 판매수량 ( sum_range, $E$5:$E$14 ) 에서 가져온다.

 

[ 수학 및 삼각 함수 ]

• RANDARRAY 함수 - 배열 형태로 난수 구하기
• SEQUENCE 함수 - 연속된 숫자목록 만들기
• LCM 함수 - 최소공배수 구하기
• GCD 함수 - 최대공약수 구하기
• ODD 함수 - 가장 가까운 홀수로 올림,내림하기
• EVEN 함수 - 가장 가까운 짝수로 올림,내림하기
• SUBTOTAL 함수 - 목록이나 데이터베이스의 부분합(집계결과) 구하기
• SUMIFS 함수로 월별 합계 구하기
• 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 함수 - 합계 구하기