Home > 엑셀 함수 > 수학 및 삼각 함수 > 엑셀에서 SUMIFS 함수로 월별 합계 구하기

엑셀에서 SUMIFS 함수로 월별 합계 구하기

엑셀에서 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))

SUMIFS함수로 월별 합계 구하기

수식이 정상적으로 입력되었으면 D19셀에 2019년 12월의 판매수량 합계 135가 구해진다.

D19셀의 수식을 복사해서 D20셀부터 D22셀까지 붙여 넣으면 최종적으로 다음과 같이 결과가 표시된다.

SUMIFS함수로 월별 합계 구하기

 

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

SUMIFS함수로 월별 합계 구하기

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

  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함수 사용법

 

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)

SUMIFS함수로 월별 합계 구하기

수식이 정상적으로 입력되었으면 E27셀에 2019년 12월의 노트류 판매수량 합계 135가 구해진다.

E27셀의 수식을 복사해서 E28셀부터 E30셀까지 붙여 넣으면 최종적으로 다음과 같이 결과가 표시된다.

SUMIFS함수로 월별 합계 구하기

수식을 살펴보면 첫 번째 예 "월별 판매수량 합계 구하기"와 수식이 동일하고 마지막에 "분류" 조건만 추가되었다.

=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 ) 에서 가져온다.

 

[ 수학 및 삼각 함수 ]

“엑셀에서 SUMIFS 함수로 월별 합계 구하기”의 4개의 댓글

  1. 판매실적, 판매일이 1월1일보다 크거가 1월31일보다 작은 경우에는 수식이 똑같나요? 일자 기준 함수수식이 따로있는건지요?

    1. 안녕하세요. 아래 수식에서 등호(=)를 빼면 됩니다.
      판매실적의 판매일이 ( 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)) 혹시 수식이 작동안되면 쌍따옴를 키보드에 있는 것으로 입력해보세요. 감사합니다.

  2. 안녕하세요~
    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))

    1. 수식의 형태는 맞는데 날짜값이 일치하지 않아서 안됩니다. DATE(,월,) 이렇게 하면 1900년의 월로 인식합니다. 그리고 월 타이틀에 어떻게 입력하셨는지 모르겠지만 "1월"이라고 입력하면 월값으로 인식되지 않습니다.
      타이틀의 월값은 2020-2-1의 형태로 월을 정확히 입력하시고
      A4에 시작월(2020-3-1의 형태로), A5에 종료월을 입력한 후
      수식을 다음과 같이 해 보세요.
      =SUMIFS(B2:M2,B1:M1,">="&A4,B1:M1,"<="&A5)

답글 남기기

이메일 주소를 발행하지 않을 것입니다.