SUMIF 함수 구분열 없이 월별 합계 구하기
SUMIF 함수 구분열 없이 합계 계산 목차 바로가기
사용된 공식
SUMPRODUCT 함수를 사용한 공식
SUMPRODUCT 함수를 사용한 공식은 더 다양한 상황에서 응용할 수 있지만, 데이터가 많을 시 처리속도가 느려지는 단점이 있습니다.
EOMONTH 함수를 사용한 공식
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 함수를 통해 반환되는 배열은 아래와 같습니다.
= {1, 1, 2, 3, 3, 4, 6}

3. 논리값의 계산
"--" 기호는 논리값인 참(TRUE) 또는 거짓(FALSE)을 숫자로 강제변환하는 기호입니다. 엑셀은 기본적으로 참은 1로, 거짓은 0으로 계산하지만 상황에 따라 숫자로 계산이 되지 않아 옳지않은 값을 출력할 수 있으므로 항상 논리값 계산 앞에는 '--' 기호를 추가하는 것을 습관화하는 것이 좋습니다. "--" 기호에 대한 더욱 자세한 설명은 아래 링크를 참고하세요.
날짜범위에서 ‘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. 월 구분열 없이 월별 합계 계산
- 예제파일을 실행한 뒤, 아래 그림과 같이 월별 매출현황을 출력하기 위한 표를 시트 비어있는 곳에 생성합니다.
월별 합계를 구하기 위한 표를 추가합니다. - SUMPRODUCT 함수를 응용한 아래 수식을 표의 첫번째 셀에 입력합니다.
=SUMPRODUCT(--(MONTH(날짜범위)=H20)*--(매장범위=I19)*매출액범위)
SUMPRODUCT 수식을 입력합니다. - 자동채우기를 편하게 할 수 있도록 셀 참조방식을 변경합니다.
월을 참조하는 셀은 F4키를 3번 눌러 알파벳앞에만 $ 기호를 추가합니다. 지점명을 참조하는 셀은 F4키를 2번 눌러 숫자 앞에만 $ 기호를 추가합니다.=SUMPRODUCT(--(MONTH(날짜범위)=$H20)*--(매장범위=I$19)*매출액범위)엑셀의 셀 참조방식에 대한 자세한 설명은 아래 영상강의를 참고하세요.
- 수식을 자동채우기 하여 월별 합계 계산을 마무리합니다.
SUMIF 함수 구분열 없기 합계 계산이 완료되었습니다.
5. 여러 개 매장의 월별 매출 합계 계산
여러 개 매장의 월별 매출 합계를 한번에 구할 경우, 아래와 같이 조건을 '덧셈' 처리하여 간단히 해결할 수 있습니다. 아래 상황을 가정하겠습니다.
A, B, C, D 배열 중 A와 C가 있는 곳의 값만 출력하고 싶을 경우
= (배열=”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월달 매출합계를 구하려면, 아래와 같이 수식을 작성합니다.