월별 매출, 거래처별 합계, 아직 못 받은 금액을 구할 때마다 필터를 걸어 선택 합계를 보고 있다면 SUMIFS와 COUNTIFS로 한 번에 바꿀 수 있습니다. 다만 조건을 짝으로 쓰는 순서, 날짜 조건을 따옴표와 &로 잇는 방법, 부정 조건이 빈 행까지 세는 문제에서 많이 막힙니다. 거래내역 한 장을 월별·서비스별과 거래처별로 집계하는 엑셀 SUMIFS COUNTIFS 집계표로 정리했고, 빠진 거래를 알려 주는 검산 줄도 넣었습니다.
엑셀 SUMIFS COUNTIFS 집계표 파일 다운로드
파일은 xlsx 하나이고 시트가 다섯 개입니다. 거래내역에 거래를 입력하면 집계가 자동으로 계산되며 매크로는 쓰지 않았습니다. 파일 안의 거래 72건, 거래처, 금액은 모두 설명용 예시이므로 본인 데이터로 바꿔서 사용하세요.

파일은 이렇게 구성되어 있습니다
- 사용법: 입력 칸과 검산 줄 보는 법을 정리한 안내 시트
- 거래내역: 날짜, 거래처, 서비스, 금액, 입금여부를 한 줄씩 넣는 원본 표. 200건까지 읽습니다.
- 월별집계: 집계 연도를 넣으면 월별·서비스별 매출, 미수금, 건수, 평균 단가가 계산되는 시트
- 항목별집계: 기간을 넣으면 거래처별(12곳까지)·서비스별 매출, 건수, 평균 단가, 미수금, 비중을 구하는 시트
- 조건연습: 조건을 바꿔 가며 쓰는 법을 확인하는 시트
SUMIFS 수식은 이렇게 읽습니다
SUMIFS는 조건을 모두 만족하는 줄의 값만 더하는 함수입니다. 마이크로소프트 도움말의 예시처럼 더할 숫자가 있는 범위를 맨 앞에 쓰고, 조건을 걸 범위와 조건을 짝으로 이어 붙입니다. 구글시트 도움말도 같은 순서이며, 한 줄이 모든 조건을 만족해야 합계에 들어간다고 안내합니다.
10월 개인수업 매출을 구하는 월별집계 D16과 김민수 회원의 기간 합계를 구하는 항목별집계 C8의 수식입니다. 금액, 날짜, 거래처, 서비스는 거래내역 해당 열의 7~206행을 줄여 쓴 이름이고, 파일의 수식은 칸이 비었을 때를 위해 IF로 한 번 감쌌습니다.
월별집계 D16
=SUMIFS(금액,날짜,">="&$C16,날짜,"<"&EDATE($C16,1),서비스,D$6)
항목별집계 C8
=SUMIFS(금액,거래처,$B8,날짜,">="&$C$3,날짜,"<"&($C$4+1))
D16의 조건은 세 짝입니다. 날짜가 시작일인 10월 1일 이상이고, 다음 달 1일보다 앞서고, 서비스가 머리글 칸의 개인수업인 줄만 금액을 더합니다. 10월에는 김민수 450,000원과 이서연 600,000원이 모두 걸려 1,050,000원이 됩니다.

월별 합계는 날짜 범위로 묶습니다
월별집계의 C열 시작일은 집계 연도와 월 번호로 계산한 각 달 1일입니다. 한 달은 시작일 이상이고 다음 달 1일보다 앞이라는 두 조건으로 묶으며, 비교 기호는 큰따옴표로 묶고 &로 날짜 칸을 이어 붙입니다. 마이크로소프트 SUMIF 도움말도 비교 기호가 든 조건은 큰따옴표로 묶으라고 하고, 칸은 ">" & C2처럼 잇는 예를 보여 줍니다.
말일 대신 다음 달 1일보다 앞이라고 쓴 이유는 달마다 마지막 날이 달라서입니다. EDATE($C16,1)이 한 달 뒤 같은 날짜를 돌려주므로 12월 행도 그대로 쓰고, 시간이 섞인 날짜가 들어와도 말일 거래가 빠지지 않습니다. EDATE를 비롯한 날짜 계산은 엑셀 날짜 계산 템플릿 글에서 다뤘습니다.
예시에서 10월 합계는 3,250,000원이고 연도 전체는 28,210,000원입니다. 맨 오른쪽 미수금 열은 서비스와 관계없이 미입금인 금액을 월별로 더한 것으로 11월이 2,080,000원, 합계는 3,850,000원입니다.
COUNTIFS로 건수와 평균 단가 구하기
월별 표 아래 ②에서는 같은 조건으로 건수를 셉니다. 10월 개인수업 칸은 =COUNTIFS(날짜,">="&$C33,날짜,"<"&EDATE($C33,1),서비스,D$23)이며, COUNTIFS는 더할 범위 없이 조건 짝만 이어 씁니다. 마이크로소프트 도움말에 따르면 조건 범위는 행과 열 수가 같아야 하지만 이웃해 있을 필요는 없습니다.
평균 단가 열은 같은 달의 매출 합계를 건수로 나눈 값이고, 건수가 0인 달은 IF로 비워 0으로 나누지 않게 했습니다. 예시의 거래는 모두 72건, 평균 단가는 391,806원이며 서비스별로는 개인수업 20건, 그룹수업 24건, 온라인코칭 19건, 외부강의 9건입니다.
거래처별·서비스별 집계와 검산
항목별집계는 시작일과 종료일 사이의 거래만 대상으로 하며 예시 기간은 2026-07-01부터 2026-11-12까지입니다. 거래처 칸에는 거래내역의 이름을 그대로 적고, 조건은 거래처가 같고 날짜가 시작일 이상이며 종료일 다음 날보다 앞이라는 세 짝이라 종료일 당일 거래까지 들어갑니다.
이 기간의 합계는 14,530,000원, 36건, 평균 단가 403,611원이고 못 받은 금액은 3,850,000원입니다. 거래처 중에는 이서연 회원이 3,000,000원(20.6%)으로 가장 크고, 서비스 중에는 개인수업이 5,550,000원으로 가장 많지만 건당 평균 단가는 외부강의가 725,000원으로 가장 높습니다.

표 아래 검산 줄은 기간 안의 거래내역 합계에서 거래처별 합계와 서비스별 합계를 뺀 값입니다. 거래처 이름에 오타가 있거나 빠뜨린 거래처가 있으면 차이가 생기고 판정 칸이 확인 필요로 바뀝니다. 집계표는 틀려도 오류 없이 다른 숫자를 보여 주므로 이런 줄이 있어야 알아챌 수 있습니다.
조건 쓰는 방법: 기호, 와일드카드, 또는
조건연습 시트에는 자주 쓰는 조건 일곱 가지가 결과와 함께 들어 있습니다. 1번은 앞에서 본 기간 합계로, 3,250,000원은 월별집계의 10월 합계와 같습니다.
2번은 와일드카드입니다. SUMIF 도움말에 따르면 별표는 임의의 글자 수, 물음표는 한 글자와 맞고 실제 별표나 물음표는 앞에 물결표를 붙여 찾습니다. 키워드 칸의 스튜디오를 "*"&C5&"*"로 감싸면 스튜디오 온과 라온 스튜디오 두 거래처의 매출 4,500,000원이 한 번에 더해집니다.
3번은 기준 금액 이상인 거래 건수로 18건입니다. 4번은 개인수업 또는 그룹수업처럼 또는 조건이 필요한 경우입니다. SUMIFS는 조건을 모두 만족해야 하는 방식이라 SUMIFS를 두 번 쓰고 더하며, 10,170,000원과 7,700,000원을 더한 17,870,000원이 나옵니다.
5~7번은 입금이 아닌 건수를 세는 방법 셋입니다. =COUNTIFS(입금여부,"<>입금")은 빈 칸도 입금과 같지 않다고 보아 입력하지 않은 128줄까지 세므로 137건이 나옵니다. 금액이 0보다 크다는 조건을 하나 더 붙인 6번과 미입금을 직접 지정한 7번은 9건입니다. 137건은 LibreOffice로 다시 계산한 값입니다.

사용 방법
- 「거래내역」 시트 오른쪽의 서비스 목록 네 칸을 본인 서비스 이름으로 바꾸면 집계표 머리글도 함께 바뀝니다.
- 거래를 한 줄씩 입력합니다. 날짜는 날짜 형식, 금액은 숫자만 넣고 서비스와 입금여부는 목록에서 고릅니다. 예시 72건은 지우세요.
- 「월별집계」 시트에 집계 연도를 넣고 월별 매출, 건수, 미수금을 확인합니다.
- 「항목별집계」 시트에 시작일과 종료일을 넣고 거래처 칸에 거래내역과 같은 이름을 적습니다.
- 두 집계 시트 아래의 검산 줄이 0 또는 일치인지 확인합니다. 아니면 집계에서 빠진 거래가 있다는 뜻입니다.
- 시트 보호(암호 없음)는 검토 → 시트 보호 해제로 풉니다. 구글시트에서는 파일을 열어 합계와 검산 줄을 먼저 확인하세요.
이 템플릿이 맞는 경우와 맞지 않는 경우
프리랜서나 소규모 사업자가 월별 매출, 거래처별 합계, 미수금을 한 파일에서 볼 때 맞고, 아래 경우에는 그대로 쓰기 어렵습니다.
- 수입과 지출을 함께 보는 경우: 이 파일은 매출만 집계합니다. 항목별 수입·지출은 엑셀 가계부 템플릿 글을 참고하세요
- 거래가 200건을 넘는 경우: 거래내역은 7~206행만 읽으므로 시트 보호를 풀고 집계 수식의 범위를 함께 넓혀야 합니다
- 거래처와 서비스가 많은 경우: 칸이 거래처 12곳, 서비스 4개까지입니다. 더 많으면 피벗 테이블이 편합니다
- 견적서와 청구서를 문서 단위로 발행하고 입금 여부까지 관리하는 경우: 이 파일은 거래내역만 집계합니다. 문서 번호별 입금 상태와 미수금·연체 계산은 프리랜서 견적서·청구서 템플릿 글을 참고하세요
자주 막히는 부분
- 합계가 비어 있거나 모자랍니다. 금액이나 날짜가 300,000원처럼 글자로 들어간 경우입니다. 글자로 입력된 값은 합계에서 빠지며, 거래내역의 입력 점검 칸에서 그런 칸 수를 확인할 수 있습니다.
- 거래처 합계가 맞지 않습니다. 앞뒤 공백이나 띄어쓰기가 다르면 다른 거래처로 계산됩니다. 마이크로소프트 COUNTIF 도움말도 앞뒤 공백이 예상 밖의 값을 만든다며 TRIM이나 CLEAN을 권합니다.
- 이름이 엉뚱하게 묶입니다. 조건은 대소문자를 구분하지 않고, 이름에 별표나 물음표가 들어 있으면 와일드카드로 읽힙니다.
- 빈 칸을 조건으로 가리켰더니 결과가 이상합니다. 마이크로소프트 COUNTIFS 도움말은 조건이 빈 칸을 가리키면 0으로 본다고 설명합니다. 그래서 이름 칸이 비면 IF로 먼저 막아 두었습니다.
- 칸이 수정되지 않습니다. 암호 없는 시트 보호가 걸려 있어 노란 칸만 고칠 수 있습니다. 검토에서 시트 보호 해제를 누르세요.
사용 전 확인할 점
모든 입력값은 예시이고 결과는 참고용이므로 세금 신고나 정산에 쓰기 전에는 원본 장부와 대조하세요. 글 속 시트 이미지는 화면 캡처가 아니라 파일의 예시값으로 다시 그린 것이어서, 엑셀이나 구글시트에서 열면 글꼴과 간격이 조금 다를 수 있습니다. 파일의 계산은 같은 입력을 파이썬으로 따로 계산한 값과 대조해 확인했고, 예시 데이터, 빈 표, 입력 조건을 바꾼 경우, 날짜가 비거나 글자인 무작위 거래 7종까지 15가지 상황, 4,320개 항목에서 같은 결과가 나왔습니다. 다만 계산은 LibreOffice에서 확인했고 엑셀과 구글시트에서는 아직 열어 보지 못했으니, 처음 열면 검산 줄이 0인지 확인해 보세요. 수식은 엑셀과 구글시트 공통 함수만 사용했습니다.
참고한 공식 자료는 다음과 같습니다. 마이크로소프트 지원 여러 조건을 기반으로 값의 합계 구하기, SUMIF 함수, COUNTIFS function, Use the COUNTIF function in Microsoft Excel, Google Docs 편집기 고객센터 SUMIFS function, SUMIF.