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

강의 목차

SUMIF 함수 구분열 없이 월별 합계 바로 구하는 방법 :: 오빠두 5분 엑셀 19-02

예제파일	완성파일	무료 E-BOOK

첨부파일에 이상이 생겼을 경우, 1:1 문의하기로 연락주시면 신속히 해결해드리겠습니다.

1. 이번 강의에서는..

SUMIF 함수 사용시, 날짜 열에서 월을 나타내는 구분열을 추가하지 않고 날짜열에서 바로 월별 합계를 구하는 방법에 대해 알아봅니다.
SUMPRODUCT 함수를 사용하여 SUMIF 와 COUNTIF 함수를 효율적으로 대체하는 방법을 소개합니다.
SUMPRODUCT 함수는 그 인수로 '배열'을 받을 수 있습니다. 따라서 다른 함수를 수식안에 섞어서 같이 사용할 수 없는 SUMIF 함수와 달리, SUMPRODUCT함수에 입력되는 '범위'는 MONTH함수 또는 LEN함수 등등의 다른 함수와 같이 섞어서 사용할 수 있습니다.
논리값 처리 또는 배열에 익숙하지 않을 경우, 기존에 사용하던 SUMIF 함수보다는 다소 복잡할 수 있습니다. 하지만 SUMPRODUCT 함수를 잘 응용하면 사무자동화를 하는데 큰 도움이 됩니다.

2. 사용된 공식

[월과 업체명이 일치하는 매출액의 합계 계산] :: 날짜열에서 월을 나타내는 구분열 없이 바로 합계를 계산합니다.

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

3. 함수의 동작 원리

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 함수를 통해 출력되는 날짜범위의 배열은 아래와 같습니다.

= MONTH ( {1월 24일, 1월 30일, 2월 3일, 3월 1일, 3월 3일, 4월 5일, 6월 2일} )
= {1, 1, 2, 3, 3, 4, 6}

3. 논리값의 계산

"–" 기호는 논리값으로 출력되는 참(TRUE) 또는 거짓(FALSE)의 값을 숫자로 강제변환해주는 기호입니다. 엑셀에서는 기본적으로 참은 '1'로, 거짓은 '0'으로 계산되지만 엑셀 버전에 따라 숫자로 반환되지 않아 올바르지 않은 값을 출력할 수 있으므로 '–' 기호는 반드시 넣어주도록 합니다.

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

= SUMPRODUCT (–( MONTH(날짜범위)=3 ))
= SUMPRODUCT (–( {1, 1, 2, 3, 3, 4, 6} = 3 ))
= SUMPRODUCT (–( {거짓, 거짓, 거짓, 참, 참, 거짓 거짓} ))
= SUMPRODUCT ({0, 0, 0, 1, 1, 0, 0})
= 2

4. 월 구분열 없이 월별 합계 계산

예제파일에서 아래와 같이 월별 매출현황을 출력하기 위한 표를 만든 뒤, SUMPRODUCT 함수를 이용하여 아래 수식을 입력합니다. 이후 자동채우기를 편리하게 사용하기 위하여, 월을 참조하는 셀은 F4키를 3번눌러 '열이동고정'(예: $A1)을, 지점명을 참조하는 셀은 F4키를 2번눌러 '행이동고정'(예: A$1)을 시켜줍니다.