엑셀 SUMIFS·AVERAGEIFS 실무 패턴 — 다중조건 집계 한 번에 끝내기
엑셀 SUMIFS AVERAGEIFS로 날짜·지역·카테고리처럼 조건이 여러 개일 때 합계와 평균을 정확하고 빠르게 계산합니다. 표준 템플릿과 함정(숫자-텍스트, 공백, 기간 처리)까지 한 번에 정리했습니다.
Quick Fix(3분)
- 원본을 테이블(Ctrl+T)로 만들기(SalesT).
- 기본 합계:
=SUMIFS(SalesT[Amount], SalesT[Region], "East") - 기간:
=SUMIFS(SalesT[Amount], SalesT[Date], ">="&G2, SalesT[Date], "<="&H2) - 부분 일치:
=SUMIFS(SalesT[Amount], SalesT[Category], "*JEANS*") - 평균:
=AVERAGEIFS(SalesT[Amount], SalesT[Region], "East", SalesT[Category], "TOP")
핵심 개념
- 비교 연산자는 문자열 결합:
">="&셀형태. - 와일드카드:
*0개 이상,?1글자,~이스케이프. - 각 조건은 AND 결합, OR은 여러 식 합산.
실무 예제
| Date | Region | Category | Amount |
|---|---|---|---|
| 2025-01-05 | East | TOP | 120000 |
| 2025-01-20 | West | BTM | 90000 |
| 2025-02-03 | East | TOP | 80000 |
| 2025-02-14 | South | ACC | 45000 |
| 2025-02-25 | East | JEANS | 130000 |
| 2025-03-09 | North | BTM | 150000 |
| 2025-03-20 | East | JEANS-SLIM | 110000 |
기간 합계
=SUMIFS(SalesT[Amount], SalesT[Date], ">="&$G$2, SalesT[Date], "<="&$H$2)월 합계
=SUMIFS(SalesT[Amount],
SalesT[Date], ">="&EOMONTH($A$2,-1)+1,
SalesT[Date], "<="&EOMONTH($A$2,0))부분 일치
=SUMIFS(SalesT[Amount], SalesT[Category], "*JEANS*")OR 조건
=SUMIFS(SalesT[Amount], SalesT[Region], "East")
+SUMIFS(SalesT[Amount], SalesT[Region], "West")평균과 비중
=AVERAGEIFS(SalesT[Amount], SalesT[Region], "East", SalesT[Category], "*JEANS*")
=SUMIFS(SalesT[Amount], SalesT[Region], "East")/SUM(SalesT[Amount])대체·보강
- 테이블과 구조적 참조로 유지보수 용이.
- 이름 정의로 기간/조건 셀 가독성 강화.
- FILTER로 전처리 후
SUM결합. - 피벗테이블은 복잡 축·필터에 유리.
Troubleshooting
| 증상 | 원인 | 해결 |
|---|---|---|
| 결과 0 | 날짜/숫자 텍스트 | VALUE, 텍스트나누기, 서식 통일 |
| 일부만 집계 | 범위 불일치 | 테이블 참조 사용 |
| 와일드카드 실패 | 기호 누락 | * / ? / ~ 규칙 확인 |
| 기간 오류 | 연산자 결합 누락 | “>=”&셀 형식 준수 |
| 공백 문제 | CHAR(160) | SUBSTITUTE→TRIM/CLEAN |
| 느림 | 전체열 참조 | 열 범위 최소화/피벗 활용 |