정산일 전날, 3월 입금 합계가 전월보다 200만 원 적게 나온다. 수식을 열어보면 3월 31일 입금 건이 통째로 누락돼 있다. 입금일 기준 월별 합계는 SUMIF 단독이 아니라 SUMIFS와 날짜 범위 조합으로 구해야 정확하다. SUMIF의 조건은 하나뿐이라 “3월 1일 이상이고 3월 31일 이하”를 동시에 걸 수 없기 때문이다.
SUMIF 단독으로 날짜 범위를 처리할 수 없는 구조적 이유
SUMIF 구문은 =SUMIF(범위, 조건, 합산범위)다. 조건이 하나뿐이다. 월별 합계를 내려면 ‘특정 달의 시작일 이상, 말일 이하’라는 두 가지 날짜 조건이 반드시 필요하다. 이 두 조건을 SUMIF 하나에 넣는 것은 문법적으로 불가능하다.
그래서 많이 쓰는 우회책이 보조 열이다. 날짜 열 옆에 =MONTH(B2)를 넣어 월 번호만 추출한 뒤, 그 열을 기준으로 SUMIF를 건다. 구조는 단순하지만 치명적인 허점이 있다. 2023년 3월과 2024년 3월이 둘 다 숫자 ‘3’으로 묶인다. 데이터가 2년치 이상 쌓이는 순간 합계가 조용히 부풀어 오른다. 오류 메시지 없이 그냥 틀린 숫자가 나오기 때문에 발견하기도 어렵다.
SUMPRODUCT로 월·연도를 동시에 걸러내는 방법
보조 열 없이 수식 한 줄로 처리하고 싶다면 SUMPRODUCT가 대안이다. B열이 입금일, C열이 금액이라 할 때 다음과 같다.
=SUMPRODUCT((MONTH(B2:B100)=3)*(YEAR(B2:B100)=2024)*(C2:C100))MONTH 조건과 YEAR 조건을 곱하면 두 조건을 동시에 만족하는 행만 1로 살아남는다. 나머지는 0이 되어 금액이 더해지지 않는다. 간단하고 직관적이다.
월 번호와 연도를 별도 셀(D2, E2)로 분리하면 재활용이 쉬워진다.
=SUMPRODUCT((MONTH(B2:B100)=E2)*(YEAR(B2:B100)=D2)*(C2:C100))D2에 연도, E2에 월 번호를 입력하면 수식을 건드리지 않고도 원하는 기간을 바꿀 수 있다. 단, 날짜 열에 빈 셀이나 텍스트가 섞여 있으면 오류가 날 수 있으므로 데이터 정합성을 먼저 점검해야 한다.
가장 안정적인 방식: SUMIFS + DATE + EOMONTH
실무 정산 시트에서 가장 신뢰할 수 있는 구조는 SUMIFS로 날짜 범위를 직접 잡는 방식이다. 수식은 아래와 같다.
=SUMIFS(C2:C100, B2:B100, ">="&DATE(D2,E2,1), B2:B100, "<="&EOMONTH(DATE(D2,E2,1),0))- C2:C100: 합산할 금액 열
- B2:B100: 입금일 열 (두 번 등장 — 조건이 두 개이므로)
- DATE(D2,E2,1): D2=연도, E2=월로 해당 월 1일을 날짜값으로 생성
- EOMONTH(DATE(D2,E2,1),0): 해당 월의 마지막 날 자동 계산
EOMONTH의 두 번째 인수 0은 "기준 날짜와 같은 달 말일"을 뜻한다. 2월은 28일(윤년은 29일), 4월은 30일로 달마다 끝나는 날이 다르다. EOMONTH를 쓰면 수식을 전혀 수정하지 않아도 매월 정확한 말일을 잡아준다. 31을 하드코딩하면 2월, 4월, 6월, 9월, 11월에서 오류가 발생하거나 다음 달 1일 입금이 포함되는 문제가 생긴다.
수식이 0 또는 엉뚱한 숫자를 반환할 때 체크 포인트
SUMIFS를 올바르게 썼는데도 결과가 이상하다면 아래 세 가지를 순서대로 짚는다.
1. 날짜 셀이 텍스트로 저장된 경우
셀에 2024-03-15가 보여도 실제로는 텍스트일 수 있다. 클릭했을 때 왼쪽 정렬이면 텍스트, 오른쪽 정렬이면 날짜(숫자)다. 텍스트 날짜는 SUMIFS 날짜 비교에서 완전히 무시된다. 해결 방법은 두 가지다.
- 날짜 열 선택 → 데이터 탭 → 텍스트 나누기 → 마침 (날짜 형식으로 일괄 전환)
- 옆 열에
=DATEVALUE(B2)를 넣어 숫자 날짜를 추출한 후, 그 열을 SUMIFS 기준으로 사용
2. 절대참조($)를 빠뜨린 경우
수식을 아래 행으로 복사할 때 B2:B100이 B3:B101로 밀리면 합산 범위가 달라진다. 금액 열과 날짜 열 범위는 $B$2:$B$100처럼 달러 기호로 고정해야 한다. 조건으로 참조하는 연도·월 셀(D2, E2)은 수식을 어느 방향으로 복사하느냐에 따라 고정 여부를 결정한다.
3. 날짜 열에 오류값이 섞인 경우
SUMPRODUCT는 빈 셀을 0으로 처리해 결과에 영향을 주지 않는다. 그러나 #VALUE!나 #N/A 같은 오류값이 날짜 열에 섞이면 전체 수식이 오류를 반환한다. IFERROR로 각 셀을 감싸거나, 입력 단계에서 데이터 유효성 검사(날짜 형식만 허용)를 걸어두는 것이 근본적인 해결책이다.
SUMIFS vs 피벗 테이블: 상황별 선택 기준
피벗 테이블은 날짜 열을 오른쪽 클릭해 '그룹화 → 월'을 선택하면 1분 안에 월별 합계 표를 만들어준다. 빠르다. 하지만 목적이 다르다.
| 구분 | SUMIFS 수식 | 피벗 테이블 |
|---|---|---|
| 원본 변경 반영 | 즉시 자동 반영 | 새로 고침 필요 |
| 다른 시트 연동 | 자유롭게 참조 가능 | 제한적 |
| 조건 추가 | SUMIFS 인수 추가로 확장 | 필터·슬라이서 활용 |
| 설정 난이도 | 수식 이해 필요 | 드래그 앤 드롭 |
| 적합한 상황 | 자동화 대시보드·정산 시트 | 빠른 현황 파악·탐색 |
매월 자동으로 업데이트되는 대시보드, 계좌나 담당자 조건을 추가해야 하는 정산 시트, 다른 워크시트에서 해당 셀을 참조해야 하는 구조라면 SUMIFS가 맞다. 반면 일회성 현황 파악이나 어떤 패턴이 있는지 탐색적으로 살펴볼 때는 피벗이 압도적으로 빠르다. 두 도구를 상황에 따라 나눠 쓰는 것이 가장 실용적이다.
정리
입금일 기준 월별 합계에서 SUMIF 단독은 조건이 하나뿐이라 날짜 범위를 온전히 처리하지 못한다. 세 가지만 기억하면 된다.
- 다년도 데이터가 있으면 MONTH()만 쓰지 말 것. YEAR()를 반드시 함께 조건에 넣는다.
- 날짜 범위 조건은 SUMIFS + DATE + EOMONTH 조합이 가장 안정적이다.
- 수식이 0을 반환하면 날짜 셀이 텍스트로 저장됐는지 먼저 확인한다.
수식을 한 번 제대로 세팅해두면, D2와 E2에 연도와 월 번호만 바꿔 입력해도 어느 달이든 정확한 합계를 꺼낼 수 있다. 매월 수식을 새로 만들 필요가 없다.
자주 묻는 질문
SUMIF와 SUMIFS 중 입금일 기준 월별 합계에는 어느 것이 더 적합한가요?
SUMIFS가 더 적합합니다. 월별 합계는 '특정 달 시작일 이상, 말일 이하'라는 두 가지 날짜 조건이 필요한데, SUMIF는 조건을 하나밖에 받지 못합니다. SUMIFS는 조건을 여러 개 지정할 수 있어 날짜 범위 처리가 가능합니다.
날짜 셀이 텍스트로 저장된 경우 SUMIFS가 0을 반환하는 이유가 뭔가요?
SUMIFS의 날짜 비교는 셀이 실제 날짜값(숫자)일 때만 작동합니다. 텍스트로 저장된 날짜는 비교 대상으로 인식하지 않아 조건에 맞는 셀이 없다고 판단해 0을 반환합니다. 셀 정렬이 왼쪽이면 텍스트, 오른쪽이면 날짜입니다. DATEVALUE 함수나 텍스트 나누기로 변환해야 합니다.
MONTH() 함수만으로 월별 합계를 구하면 어떤 문제가 생기나요?
2023년 3월과 2024년 3월이 모두 숫자 3으로 처리되어 다른 연도의 같은 달 금액을 합산해버립니다. 데이터가 2년치 이상 쌓이면 합계가 부풀어 오릅니다. YEAR() 조건을 반드시 함께 써야 합니다.
EOMONTH 함수 없이 월 말일을 직접 입력하면 안 되나요?
31을 하드코딩하면 2월(28~29일), 4월·6월·9월·11월(30일)에서 반드시 오류가 납니다. EOMONTH(기준일, 0)은 어느 달이든 해당 월의 마지막 날을 자동으로 계산해주므로 수식을 수정하지 않아도 매월 정확하게 동작합니다.
수식 대신 피벗 테이블로 월별 합계를 구하는 것이 더 낫지 않나요?
일회성 현황 파악이나 탐색적 분석에는 피벗이 훨씬 빠릅니다. 반면 다른 시트에 자동 연동되거나, 원본 데이터 변경 시 즉시 반영되어야 하거나, 계좌·담당자 같은 조건을 추가해야 하는 정산 대시보드라면 SUMIFS 수식이 더 유연합니다.