구분열없이 SUMIF 합계 구하기

공식 정리

SUMPRODUCT 함수를 사용한 공식

SUMIFS 함수를 사용한 공식

SUMIFS 함수를 사용하면 대량의 데이터를 보다 빠르게 처리할 수 있습니다. SUMIFS 함수의 자세한 사용 방법은 아래 영상 강의에서 단계별로 설명해 드렸습니다.

이번 강의에서 다룰 내용

• 이번 강의에서는 SUMIF 함수로 월별 합계를 계산할 때 구분열을 추가하지 않고 한 번에 결과를 구하는 방법을 알아봅니다.
• SUMPRODUCT 함수를 응용하여 SUMIF 함수와 COUNTIF 함수를 효율적으로 대체하고 활용하는 방법을 함께 살펴봅니다.
• SUMIF 함수는 인수로 '범위'를 받지만, SUMPRODUCT 함수는 인수로 '배열'을 받습니다. 따라서 SUMPRODUCT 함수는 인수에 LEN, MONTH 등 다른 함수를 중첩해 사용할 수 있다는 큰 장점이 있습니다.
• 다만 조건식 작성에 익숙하지 않은 경우 기존 SUMIF 함수에 비해 수식 구조가 다소 복잡하게 느껴질 수 있으므로, 충분한 연습을 통해 익혀 두시는 것이 좋습니다.

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

1. 동적범위 설정

보다 효율적으로 수식을 작성하기 위해 동적범위를 먼저 설정합니다. 동적범위에 대한 자세한 설명은 아래 관련 강의를 참고해 주세요. (동적범위를 설정하지 않고 각 범위를 수식에 직접 입력해도 결과는 동일합니다.)

2. MONTH 함수로 각 날짜의 '월'을 배열로 반환

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

3. 논리값의 계산

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

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

1. 예제파일을 실행한 뒤, 아래 그림과 같이 월별 매출 현황을 출력하기 위한 표를 시트의 빈 영역에 작성합니다.
2. SUMPRODUCT 함수를 응용한 다음 수식을 표의 첫 번째 셀에 입력합니다.
   =SUMPRODUCT(--(MONTH(날짜범위)=H20)*--(매장범위=I19)*매출액범위)

   

3. 자동 채우기를 편리하게 사용할 수 있도록 셀 참조 방식을 변경합니다.
   월을 참조하는 셀은 F4 키를 3번 눌러 알파벳 앞에만 $ 기호를 추가하고, 지점명을 참조하는 셀은 F4 키를 2번 눌러 숫자 앞에만 $ 기호를 추가합니다.
   =SUMPRODUCT(--(MONTH(날짜범위)=$H20)*--(매장범위=I$19)*매출액범위)

   엑셀의 셀 참조 방식에 대한 자세한 설명은 아래 영상 강의를 참고해 주세요.
   
4. 수식을 자동 채우기로 확장해 월별 합계 계산을 마무리합니다.

5. 여러 개 매장의 월별 매출 합계 계산

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

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

따라서 "강남점"과 "동대문점"의 1월 매출 합계를 구하려면 다음과 같이 수식을 작성합니다.