Excel SUMIFS·COUNTIFS: 거래 9행에서 부서 합계와 3월 합계 구분하기
월별 매출을 맞출 때는 합계와 건수가 같은 것을 뜻하지 않는다는 점부터 확인해야 합니다. 아래 가상 거래 9행에서 영업1팀의 전체 매출은 5,200,000원, 거래 건수는 5건입니다. 가상 입력의 집계 B4 셀 결과는 같은 부서의 3월 매출 4,700,000원으로 계산됩니다. 매출이 0이거나 수량이 비어 있는 행도 조건에 맞으면 건수에는 포함됩니다.
이번 파일은 그 차이를 원본 행까지 거슬러 확인하는 실습입니다. 실제 거래나 업무 성과 자료가 아니며, 금액은 모두 가상입니다.
1. 먼저 원본 9행과 조건 셀을 고정하기
첨부 practice-82.xlsx의 원본 시트 A1:F10과 집계 시트를 사용합니다. 새 통합문서에 직접 만들 경우 아래 표를 A1:E10에 입력하고 날짜는 실제 날짜 값, 수량과 매출은 숫자로 넣습니다. 표의 ‘빈칸’이라는 글자는 입력하지 않습니다.
| 일자 | 부서 | 품목 | 수량 | 매출 |
|---|---|---|---|---|
| 2026-03-05 | 영업1팀 | 노트북 | 2 | 2400000 |
| 2026-03-12 | 영업1팀 | 모니터 | 5 | 1500000 |
| 2026-03-18 | 영업2팀 | 노트북 | 1 | 1200000 |
| 2026-04-02 | 영업1팀 | 키보드 | 10 | 500000 |
| 2026-03-08 | 영업2팀 | 노트북 | 3 | 3600000 |
| 2026-03-22 | 영업2팀 | 모니터 | 2 | 600000 |
| 2026-04-15 | 영업2팀 | 노트북 | 1 | 1200000 |
| 2026-03-01 | 영업1팀 | 빈칸 | 1 | 0 |
| 2026-03-25 | 영업1팀 | 모니터 | 빈칸 | 800000 |
가상 입력의 집계 A2 셀에는 영업1팀, A3에는 2026-03-01, A4에는 2026-03-31을 입력합니다. 시작일과 종료일은 조회 조건이고, B열은 결과입니다. D2:D6의 고정 기대값은 최초 입력에만 해당하므로 조건을 바꾼 뒤 그 숫자를 무조건 정답으로 삼지 않습니다.
2. B2의 합계와 B3의 5건을 행 번호로 확인하기
SUMIFS는 지정한 여러 조건을 충족하는 값들을 합산합니다. 집계!B2에는 합산 범위를 먼저, 부서 조건 범위를 다음에 씁니다.
=SUMIFS('원본'!$E$2:$E$10,'원본'!$B$2:$B$10,$A$2)영업1팀인 원본 2·3·5·9·10행의 매출을 더하면 2400000+1500000+500000+0+800000=5200000입니다. B3에는 같은 부서 조건의 행 수를 구합니다.
=COUNTIFS('원본'!$B$2:$B$10,$A$2)COUNTIFS는 지정한 조건들을 모두 충족하는 횟수를 셉니다. 여기서는 부서만 조건이므로 결과는 5입니다. 9행의 빈 품목과 10행의 빈 수량을 자동으로 빼는 수식이 아닙니다. ‘수량이 입력된 거래만’이라는 별도 기준이 필요하면 원래 5건과 목적이 달라졌음을 먼저 기록해야 합니다.
3. 날짜 조건을 더해 3월의 470만 원 구하기
집계!B4는 부서와 시작일 이상·종료일 이하를 함께 검사합니다.
=SUMIFS('원본'!$E$2:$E$10,'원본'!$B$2:$B$10,$A$2,'원본'!$A$2:$A$10,">="&$A$3,'원본'!$A$2:$A$10,"<="&$A$4)집계 B4 셀의 최초 수식 결과는 4월 2일의 가상 입력 500,000원을 제외한 5200000-500000=4700000으로 검산합니다. 합계가 바뀌지 않으면 B4가 아니라 전체 합계 B2를 보고 있는지, A3:A4가 문자 날짜인지부터 확인합니다. =ISNUMBER(A3)와 =ISNUMBER(A4)로 숫자 날짜인지 점검하되 실제 달력 날짜도 함께 봅니다.
이 예제의 날짜에는 시간이 없습니다. 종료일 셀은 그 날의 자정이므로 원본에 시각이 붙은 데이터라면 <=A4로 종료일 전체를 포함한다고 가정하면 안 됩니다. 그런 자료를 적용할 때는 시간대와 마감 정책을 정하고, 다음 날 미만 조건 등으로 별도 검산해야 합니다.
4. ‘노트’ 접두어를 두 방식으로 비교하기
원본 F2에 =LEFT(C2,2)를 넣고 F10까지 채웁니다. B5는 이 보조열, B6은 원래 품목과 와일드카드를 사용합니다.
=SUMIFS('원본'!$E$2:$E$10,'원본'!$B$2:$B$10,$A$2,'원본'!$F$2:$F$10,"노트")
=SUMIFS('원본'!$E$2:$E$10,'원본'!$B$2:$B$10,$A$2,'원본'!$C$2:$C$10,"노트*")SUMIFS 조건의 별표는 임의의 문자열과 대응합니다. 이 가상 입력에서는 두 결과 모두 영업1팀 노트북 한 행의 2,400,000원입니다. B5:B6에는 날짜 조건을 넣지 않았습니다. B4와 같은 월별 집계라고 착각하지 않는 것이 이 비교의 핵심입니다. ‘노트북’만 정확히 원한다면 접두어 대신 정확한 품목을 조건으로 사용해야 합니다.
5. 조건을 한 번씩 바꾸고 최초 값으로 돌아오기
A2만 영업2팀으로 바꾸면 B2:B6은 6600000, 4, 5400000, 6000000, 6000000입니다. 3월 영업2팀 매출은 1200000+3600000+600000으로 독립 검산합니다. A2를 영업1팀으로 되돌리고 A3:A4만 4월 1일~30일로 바꾸면 B4만 500,000원이 되며 B2·B3·B5·B6은 그대로입니다.
다시 최초 부서와 3월 조건을 넣어 D2:D6과 비교합니다. 금액을 맞추려고 원본을 덮어쓰는 대신, 다른 이름으로 저장한 사본에서 조건 셀만 변경하면 복구할 지점이 분명해집니다. 거래가 추가됐을 때 이 고정 범위가 자동으로 늘어나는 것은 아닙니다. 11행을 추가하면 모든 관련 범위의 끝 행을 함께 검토해야 합니다.
6. 0과 범위 오류를 나눠 진단하기
SUMIFS의 합산 범위와 조건 범위는 행과 열 개수가 같아야 합니다. 결과가 0이면 먼저 부서의 후행 공백, 조건 셀, 날짜의 실제 값을 확인합니다. 오류가 나오면 E2:E10과 B2:B9처럼 끝 행이 어긋나지 않았는지 확인합니다. E2:E10과 B2:B9의 불일치 오류라면 조건 범위를 B2:B10으로 다시 지정해 같은 행 수로 복구합니다. 무조건 IFERROR로 0을 덮으면 ‘일치 행 없음’과 ‘수식이 잘못됨’을 구분할 수 없습니다.
첨부는 이 9행과 명시한 조건의 산술·수식 저장값을 Python 참조 계산과 LibreOffice 재계산으로 비교했습니다. Microsoft Excel 앱에서의 입력·메뉴·자동 확장 동작을 실행한 기록은 아닙니다. 실제 매출 보고서에 연결하기 전에는 날짜·숫자·중복 거래의 업무 정의를 따로 확인하세요.
본문과 같은 입력으로 연습하기
실습 XLSX에는 본문에서 지정한 셀과 수식이 들어 있습니다. 검산 자료 ZIP에는 같은 XLSX와 입력·수식 CSV, 실습 설명, README 및 오프라인 Python 검산 코드가 있습니다. 원본 업무 파일을 덮어쓰지 말고 사본으로 연습하세요. Microsoft Excel 앱의 실제 실행 결과로 제공하는 파일은 아닙니다.
공식 자료
SUMIFS function | Microsoft Support
COUNTIFS function | Microsoft Support
출처 원문 수집일: S1 2026-09-24; S2 2026-09-24.
작성자 소개
