엑셀 월별 건수 세기, 필터로 세지 말고 COUNTIFS로 담당자별 완료건수 자동 계산하기
엑셀 월별 건수 세기를 할 때 처음에는 필터를 걸고 눈으로 개수를 세는 경우가 많습니다. 특히 “담당자별 7월 완료 건수”처럼 조건이 두세 개 붙으면 COUNTIFS 0 나옴, 날짜 조건 안 먹힘 같은 문제가 자주 생깁니다. 오늘은 초보자도 바로 따라 할 수 있게, 작은 상담 접수표에서 월별 완료건수와 금액합계를 자동으로 구하는 방법을 차근차근 정리해 보겠습니다.
Before: 필터로 세면 빠르고 쉬워 보이지만 자주 틀립니다
예를 들어 상담팀에서 매일 접수된 건을 엑셀에 입력한다고 해보겠습니다. 월말에 담당자별로 “완료된 상담이 몇 건인지”, “처리금액 합계가 얼마인지” 보고해야 하는 상황입니다.
초보자 입장에서는 가장 먼저 데이터 탭의 필터를 켜고, 담당자를 고르고, 처리상태에서 완료만 체크한 뒤, 7월 날짜만 골라서 행 개수를 세게 됩니다. 이 방법은 한두 번은 괜찮지만 담당자가 여러 명이면 매번 필터를 바꿔야 하고, 날짜 하나를 빠뜨리면 결과가 바로 틀어집니다.
| 구분 | 기존 방식 | 개선 후 방식 |
|---|---|---|
| 월별 완료건수 | 필터로 담당자, 날짜, 상태를 직접 선택 | COUNTIFS 수식으로 자동 계산 |
| 처리금액 합계 | 필터 후 보이는 금액을 따로 합산 | SUMIFS 수식으로 자동 합계 |
| 실수 가능성 | 날짜 선택 누락, 필터 해제 실수 | 조건 셀만 바꾸면 같은 기준으로 계산 |
예제 표 위치부터 정확히 잡고 시작합니다
아래처럼 A4:F11 범위에 상담 접수 내역이 있다고 가정하겠습니다. 실제 파일에서는 행 수가 더 많아도 원리는 같습니다. 중요한 것은 날짜, 담당자, 상태, 금액이 어느 열에 있는지 먼저 확인하는 것입니다.
| 셀 위치 | 열 이름 | 예시 값 | 의미 |
|---|---|---|---|
| A4 | 접수일 | 2026-07-01 | 상담이 접수된 날짜 |
| B4 | 담당자 | 김하나 | 상담 처리 담당자 |
| C4 | 고객명 | 가람상사 | 고객 또는 거래처명 |
| D4 | 상담유형 | 교환 | 문의, 교환, 환불 등 |
| E4 | 처리상태 | 완료 | 완료, 진행중, 보류 |
| F4 | 처리금액 | 15000 | 환불·정산 등 금액 |
실제 데이터는 5행부터 11행까지 입력되어 있다고 보겠습니다. 즉, 제목 행은 4행이고 데이터 범위는 A5:F11입니다.
| 접수일 | 담당자 | 고객명 | 상담유형 | 처리상태 | 처리금액 |
|---|---|---|---|---|---|
| 2026-07-01 | 김하나 | 가람상사 | 교환 | 완료 | 15000 |
| 2026-07-03 | 이도윤 | 늘봄몰 | 문의 | 완료 | 0 |
| 2026-07-05 | 김하나 | 세진유통 | 환불 | 진행중 | 25000 |
| 2026-07-09 | 김하나 | 라온마트 | 환불 | 완료 | 32000 |
| 2026-07-18 | 박민재 | 해솔샵 | 교환 | 완료 | 12000 |
| 2026-08-02 | 김하나 | 늘봄몰 | 문의 | 완료 | 0 |
| 2026-08-04 | 이도윤 | 가람상사 | 환불 | 완료 | 18000 |
After: 조건 셀을 만들면 보고서가 훨씬 편해집니다
오른쪽에 작은 결과 영역을 만들어 보겠습니다. H4에는 찾을 담당자명, I4에는 기준월, J4에는 완료건수, K4에는 완료금액 합계를 표시하겠습니다.
| 셀 | 입력할 내용 | 설명 |
|---|---|---|
| H4 | 김하나 | 집계할 담당자명 |
| I4 | 2026-07-01 | 집계할 기준월의 첫날 |
| J4 | 수식 입력 | 7월 완료건수 결과 |
| K4 | 수식 입력 | 7월 완료금액 합계 |
여기서 I4에 “7월”이라고 글자로 입력하지 않는 것이 핵심입니다. 엑셀은 날짜를 내부적으로 숫자로 관리합니다. 그래서 월별 조건을 안정적으로 쓰려면 기준월의 첫날, 예를 들어 2026-07-01처럼 입력하는 편이 좋습니다.
I4가 날짜처럼 보이지 않는다면 I4 셀을 선택한 뒤 홈 탭 > 표시 형식에서 간단한 날짜를 선택해 주세요. 셀에는 2026-07-01을 입력하고, 보기 형식만 날짜로 바꾸는 것입니다.
COUNTIFS로 담당자별 월별 완료건수 구하기
J4 셀에 아래 수식을 입력합니다. 이 수식은 B열에서 담당자를 찾고, A열에서 기준월 날짜 범위를 찾고, E열에서 완료 상태만 세는 구조입니다.
=COUNTIFS($B$5:$B$11,$H$4,$A$5:$A$11,">="&$I$4,$A$5:$A$11,"<"&EOMONTH($I$4,0)+1,$E$5:$E$11,"완료")COUNTIFS는 조건이 여러 개인 개수를 셀 때 쓰는 함수입니다. 이름을 나눠서 보면 COUNT는 “개수를 세다”, IFS는 “조건들이 여러 개다”라고 생각하면 쉽습니다.
| 수식 부분 | 뜻 |
|---|---|
| $B$5:$B$11,$H$4 | B열 담당자가 H4의 김하나와 같은지 확인 |
| $A$5:$A$11,">="&$I$4 | 접수일이 2026-07-01 이상인지 확인 |
| $A$5:$A$11,"<"&EOMONTH($I$4,0)+1 | 접수일이 다음 달 1일보다 이전인지 확인 |
| $E$5:$E$11,"완료" | 처리상태가 완료인 행만 계산 |
예제 기준으로 김하나의 2026년 7월 완료 건은 7월 1일과 7월 9일, 총 2건입니다. 7월 5일 김하나 건은 진행중이므로 제외되고, 8월 2일 김하나 건은 기준월이 다르므로 제외됩니다.
SUMIFS로 같은 조건의 금액 합계까지 같이 구하기
건수만 보면 업무가 끝나지 않는 경우가 많습니다. 완료된 상담 중 환불이나 정산 금액이 있다면 같은 조건으로 금액 합계도 같이 구해두면 보고서가 깔끔해집니다.
K4 셀에는 아래 수식을 입력합니다. SUMIFS는 조건에 맞는 금액을 합계 내는 함수입니다.
=SUMIFS($F$5:$F$11,$B$5:$B$11,$H$4,$A$5:$A$11,">="&$I$4,$A$5:$A$11,"<"&EOMONTH($I$4,0)+1,$E$5:$E$11,"완료")COUNTIFS와 거의 비슷하지만, SUMIFS는 맨 앞에 더할 범위가 먼저 나옵니다. 여기서는 F5:F11이 처리금액이므로 맨 앞에 $F$5:$F$11을 넣었습니다.
이 수식을 입력하면 김하나의 2026년 7월 완료금액은 15,000 + 32,000으로 47,000이 됩니다. H4 담당자명을 이도윤이나 박민재로 바꾸면 같은 기준으로 결과가 즉시 바뀝니다.
초보자가 가장 많이 헷갈리는 지점은 날짜 조건입니다
월별 집계를 할 때 “7월만 찾기”라고 생각하면 조건을 2026-07처럼 쓰고 싶어집니다. 그런데 원본 A열에는 2026-07-01, 2026-07-09처럼 하루 단위 날짜가 들어 있습니다. 그래서 7월 전체를 잡으려면 시작일 이상, 다음 달 1일 미만이라는 조건을 써야 합니다.
수식에서 EOMONTH($I$4,0)은 I4에 입력된 달의 마지막 날을 구합니다. 여기에 1을 더하면 다음 달 1일이 됩니다. 즉, 2026-07-01 이상이면서 2026-08-01보다 작은 날짜만 잡기 때문에 7월 전체가 정확히 들어옵니다.
COUNTIFS가 0으로 나올 때 확인할 것
수식은 맞게 입력한 것 같은데 결과가 0이면 대부분 아래 세 가지 중 하나입니다. 먼저 원본 날짜가 진짜 날짜인지 확인해 보세요. A열 날짜를 클릭했을 때 수식 입력줄에 2026-07-01처럼 보이고, 셀 서식을 일반으로 바꿨을 때 46204 같은 숫자로 바뀌면 날짜로 인식된 것입니다.
| 증상 | 확인할 곳 | 해결 방법 |
|---|---|---|
| 결과가 0 | A열 접수일 | 날짜가 문자로 들어갔는지 확인 |
| 특정 담당자만 안 잡힘 | B열 담당자, H4 | 이름 앞뒤 공백 확인 |
| 완료인데 제외됨 | E열 처리상태 | 완료 뒤에 공백이 있는지 확인 |
| 수식 오류 | 각 조건범위 | B5:B11, A5:A11, E5:E11처럼 행 수를 동일하게 맞춤 |
조건범위라는 말도 처음에는 어렵게 느껴집니다. 쉽게 말해 “검사할 칸 묶음”입니다. 담당자를 검사하려면 B5:B11, 날짜를 검사하려면 A5:A11, 상태를 검사하려면 E5:E11이 조건범위입니다.
COUNTIFS와 SUMIFS에서는 이 조건범위들의 크기가 같아야 합니다. 예를 들어 B5:B11은 7칸인데 A5:A100처럼 갑자기 범위를 다르게 잡으면 실무 파일에서 헷갈릴 수 있습니다. 처음 연습할 때는 같은 시작 행, 같은 끝 행으로 맞추는 습관을 들이면 좋습니다.
담당자별 표로 넓혀 쓰는 간단 응용
H4에 담당자명을 하나만 넣어도 좋지만, 보고서에서는 담당자별로 여러 줄을 만들 때가 많습니다. 예를 들어 H4:H6에 김하나, 이도윤, 박민재를 입력하고, I3에 기준월 2026-07-01을 입력해둘 수 있습니다.
그다음 J4에 아래 수식을 넣고 아래로 복사하면 담당자별 완료건수를 한 번에 볼 수 있습니다.
=COUNTIFS($B$5:$B$11,$H4,$A$5:$A$11,">="&$I$3,$A$5:$A$11,"<"&EOMONTH($I$3,0)+1,$E$5:$E$11,"완료")여기서는 $H4처럼 열 H만 고정하고 행 번호는 고정하지 않았습니다. 아래로 복사했을 때 H5, H6 담당자명을 차례대로 보게 하려는 목적입니다. 반대로 기준월 I3은 모든 행에서 같은 셀을 봐야 하므로 $I$3으로 고정했습니다.
필터 집계에서 수식 집계로 바꾸면 좋은 점
처음에는 필터로 보는 방식이 더 익숙합니다. 하지만 월말 보고, 담당자별 실적, 처리상태별 건수처럼 반복되는 업무라면 조건 셀과 COUNTIFS 조합이 훨씬 안전합니다.
오늘 예제에서 자기 파일에 맞게 바꿀 곳은 네 군데입니다. 접수일 열, 담당자 열, 처리상태 열, 그리고 결과를 표시할 셀입니다. 이 네 곳만 정확히 맞추면 필터를 반복해서 누르지 않아도 월별 완료건수와 금액합계를 바로 확인할 수 있습니다.