SUMIF 함수 구분열 없이 월별 합계 바로 구하는 방법 | SUMPRODUCT 고급

SUMIF 함수 월별 합계를 구분열 없이 바로 구하기 위한 SUMPRODUCT 공식의 동작원리와 실전 예제를 살펴봅니다.

홈페이지 » SUMIF 함수 구분열 없이 월별 합계 바로 구하는 방법 | SUMPRODUCT 고급

SUMIF 함수 구분열 없이 월별 합계 구하기

SUMIF 함수 구분열 없이 합계 계산 목차 바로가기
사용된 공식

SUMPRODUCT 함수를 사용한 공식

=SUMPRODUCT( --(MONTH(날짜범위)=날짜)*--(매장범위=매장)*합계범위 )

SUMPRODUCT 함수를 사용한 공식은 더 다양한 상황에서 응용할 수 있지만, 데이터가 많을 시 처리속도가 느려지는 단점이 있습니다.

EOMONTH 함수를 사용한 공식

=SUMIFS(합계범위,날짜범위,">="&DATE(YEAR(날짜),MONTH(날짜),1),날짜범위,"<="&EOMONTH(날짜,0))

EOMONTH 함수를 사용한 공식은 사용하는데 다소 제한적이지만, 데이터가 많을 시 더욱 빠르게 처리합니다.

영상강의

예제파일 다운로드

이번 강의에서 다룰 내용 알아보기

  • 이번 강의에서는 SUMIF 함수로 월별합계 계산 시, 구분열을 추가하지 않고 한번에 계산하는 방법을 알아봅니다.
  • SUMPRODUCT 함수를 응용하여 SUMIF 함수와 COUNIF 함수를 효율적으로 대체하고 응용하는 방법을 알아봅니다.
  • SUMIF 함수는 인수로 '범위'를 받아오는 반면, SUMPRODUCT 함수는 인수로 '배열'을 받아옵니다. 따라서 SUMPRODUCT 함수는 인수로 LEN함수, MONTH 함수 등을 사용할 수 있다는 장점이 있습니다.
  • 단, 조건 서식을 작성하는데 익숙하지 않을 경우, 기존 SUMIF 함수 대비 수식을 작성하는데 다소 어려울 수 있으므로 여러번의 연습이 필요합니다.

SUMIF 함수 구분열 없이 합계를 구하는 공식 알아보기

1. 동적범위 설정

좀 더 효율적으로 수식을 작성하기 위해 동적범위를 추가합니다. 동적범위에 대한 자세한 설명은 아래 관련 포스트를 참고하세요. (동적범위를 설정하지 않고 각 범위를 수식에 직접 입력해도 괜찮습니다.)

이름 참조대상
날짜범위 =OFFSET(완성시트!$A$2,,,COUNTA(완성시트!$A:$A)-1)
매장범위 =OFFSET(완성시트!$B$2,,,COUNTA(완성시트!$B:$B)-1)
합계범위 =OFFSET(완성시트!$C$2,,,COUNTA(완성시트!$C:$C)-1)
2. MONTH 함수로 각 날짜의 '월'을 배열로 반환

각 날짜가 MONTH 함수를 통해 반환되는 배열은 아래와 같습니다.

= MONTH ( {1월 24일, 1월 30일, 2월 3일, 3월 1일, 3월 3일, 4월 5일, 6월 2일} )
= {1, 1, 2, 3, 3, 4, 6}
엑셀 MONTH 함수 사용
날짜범위에서 MONTH함수로 '월'을 추출합니다.
3. 논리값의 계산

"--" 기호는 논리값인 참(TRUE) 또는 거짓(FALSE)을 숫자로 강제변환하는 기호입니다. 엑셀은 기본적으로 참은 1로, 거짓은 0으로 계산하지만 상황에 따라 숫자로 계산이 되지 않아 옳지않은 값을 출력할 수 있으므로 항상 논리값 계산 앞에는 '--' 기호를 추가하는 것을 습관화하는 것이 좋습니다.  "--" 기호에 대한 더욱 자세한 설명은 아래 링크를 참고하세요.

날짜범위에서 ‘3월’에 해당하는 값을 계산한다고 가정하겠습니다

= SUMPRODUCT (--( MONTH(날짜범위)=3 ))
= SUMPRODUCT (--( {1, 1, 2, 3, 3, 4, 6} = 3 ))
= SUMPRODUCT (--( {False, False, False, True, True, False, False} ))
= SUMPRODUCT ({0, 0, 0, 1, 1, 0, 0})
= 2
4. 월 구분열 없이 월별 합계 계산
  1. 예제파일을 실행한 뒤, 아래 그림과 같이 월별 매출현황을 출력하기 위한 표를 시트 비어있는 곳에 생성합니다.

    sumif 합계 구분열 없이 구하기 위한 범위
    월별 합계를 구하기 위한 표를 추가합니다.
  2. SUMPRODUCT 함수를 응용한 아래 수식을 표의 첫번째 셀에 입력합니다.
    =SUMPRODUCT(--(MONTH(날짜범위)=H20)*--(매장범위=I19)*매출액범위)

    SUMPRODUCT 함수 수식 입력
    SUMPRODUCT 수식을 입력합니다.
  3. 자동채우기를 편하게 할 수 있도록 셀 참조방식을 변경합니다.
    월을 참조하는 셀은 F4키를 3번 눌러 알파벳앞에만 $ 기호를 추가합니다. 지점명을 참조하는 셀은 F4키를 2번 눌러 숫자 앞에만 $ 기호를 추가합니다.

    =SUMPRODUCT(--(MONTH(날짜범위)=$H20)*--(매장범위=I$19)*매출액범위)

    엑셀의 셀 참조방식에 대한 자세한 설명은 아래 영상강의를 참고하세요.

  4. 수식을 자동채우기 하여 월별 합계 계산을 마무리합니다.

    SUMIF 구분열 없이 합계 계산 완료
    SUMIF 함수 구분열 없기 합계 계산이 완료되었습니다.
5. 여러 개 매장의 월별 매출 합계 계산

여러 개 매장의 월별 매출 합계를 한번에 구할 경우, 아래와 같이 조건을 '덧셈' 처리하여 간단히 해결할 수 있습니다. 아래 상황을 가정하겠습니다.

A, B, C, D 배열 중 A와 C가 있는 곳의 값만 출력하고 싶을 경우

■ 배열 = {A, C, B, C, A, D, D, A, C, D, B}
= (배열=”A”) + (배열=”C”)
= {1, 0, 0, 0, 1, 0, 0, 1, 0, 0, 0} + {0, 1, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0}
= {1, 1, 0, 1, 1, 0, 0, 1, 1, 0, 0}

따라서, “강남점”과 “동대문점”의 1월달 매출합계를 구하려면, 아래와 같이 수식을 작성합니다.

=SUMPRODUCT(--(MONTH(날짜범위)=1)*--((매장범위="강남점")+(매장범위="동대문점"))*매출액범위)
5 7 투표
게시글평점