엑셀 AVERAGEIFS 함수로 조건별 평균값 깔끔하게 계산하기

보고서를 만들다 보면 “서울 온라인 채널의 평균 매출이 얼마인지”, “1월 한 달 동안 매출이 0이 아닌 주문만 평균을 보고 싶다” 같은 요구가 자주 나옵니다. 이럴 때 가장 잘 맞는 함수가 바로 엑셀 AVERAGEIFS 함수입니다.

이 글에서는 AVERAGEIFS를 이용해 여러 조건을 동시에 만족하는 데이터의 평균값을 구하는 방법을 기초부터 실무 예제, 구버전 대체 공식, 트러블슈팅까지 한 번에 정리합니다.

빠른 해결(Quick Fix) – 자주 쓰는 패턴 3분 요약

1단계: 데이터 구조와 기본 패턴

예시 판매 데이터 구조는 다음과 같습니다.

AVERAGEIFS 함수의 기본 구문은 다음과 같습니다.

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

2단계: 한 가지 조건 평균 (지역별 평균 매출)

서울 지역의 평균 매출금액을 구하려면 다음처럼 입력합니다.

=AVERAGEIFS(E2:E100, C2:C100, "서울")

3단계: 여러 조건 평균 (제품+지역+날짜 범위)

제품이 셔츠이면서, 지역이 서울이고, 지정한 기간 안에 속하는 주문의 평균 매출을 구하는 예제입니다.

=AVERAGEIFS($E$2:$E$100,
            $B$2:$B$100, $G$4,
            $C$2:$C$100, "서울",
            $A$2:$A$100, ">="&$G$2,
            $A$2:$A$100, "<="&$G$3)

AVERAGEIFS 개념 정리 – AVERAGE / AVERAGEIF와의 차이

AVERAGE, AVERAGEIF, AVERAGEIFS 역할 비교

AVERAGE는 범위 전체의 평균을, AVERAGEIF는 조건 1개까지의 조건부 평균을 구합니다. 엑셀 AVERAGEIFS 함수는 여러 조건을 동시에 걸 수 있어, 제품·지역·채널·날짜 등 복수 조건에 맞는 값만 골라 평균을 계산할 수 있습니다.

AVERAGEIFS 함수 구문과 인수 설명

공식 구문은 다음과 같습니다.

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

• average_range: 평균을 구할 숫자 범위
• criteria_range1, criteria1: 첫 번째 조건 범위와 조건 값
• [criteria_range2, criteria2]: 추가 조건 범위와 값 (여러 개 가능)

지원 버전과 사용 시 주의사항

AVERAGEIFS 함수는 Excel 2007 이후 대부분의 버전과 Microsoft 365에서 사용할 수 있습니다. average_range와 각 criteria_range의 크기(행·열 수)가 모두 같아야 하며, 그렇지 않으면 오류나 잘못된 결과가 나올 수 있습니다.

실무 예제 1 – 제품·지역·채널별 조건부 평균 매출

샘플 판매 데이터 만들기

다음과 같은 데이터를 A1:E11 영역에 입력했다고 가정합니다.

조건 1개로 평균 구하기

서울 지역 전체의 평균 매출금액을 구하는 공식입니다.

=AVERAGEIFS(E2:E11, C2:C11, "서울")

조건 여러 개로 평균 구하기

제품이 셔츠, 지역이 서울, 채널이 온라인인 주문의 평균 매출금액을 구하는 예제입니다.

=AVERAGEIFS(E2:E11,
            B2:B11, "셔츠",
            C2:C11, "서울",
            D2:D11, "온라인")

실무 예제 2 – 날짜 범위와 목표 금액 조건을 함께 쓰기

특정 기간 + 특정 제품 평균 매출

G2 셀에 시작일, G3 셀에 종료일, G4 셀에 제품명을 입력한 뒤 다음 공식을 사용합니다.

=AVERAGEIFS($E$2:$E$11,
            $B$2:$B$11, $G$4,
            $C$2:$C$11, "서울",
            $A$2:$A$11, ">="&$G$2,
            $A$2:$A$11, "<="&$G$3)

“목표 이상”만 포함해서 평균 구하기

목표 매출을 J2 셀에 입력해 두고, 아래 공식으로 목표 이상 주문만 평균을 계산합니다.

=AVERAGEIFS($E$2:$E$11,
            $B$2:$B$11, "셔츠",
            $C$2:$C$11, "서울",
            $E$2:$E$11, ">="&$J$2)

실무 예제 3 – 0값·빈 셀 제외, 이상치 제거 평균

0 제외 조건부 평균

서울 지역의 매출 중 0이 아닌 값만 평균을 계산하는 공식입니다.

=AVERAGEIFS(E2:E100,
            C2:C100, "서울",
            E2:E100, "<>0")

상·하위 10% 이상치 제거 평균 아이디어

사전에 상·하위 기준값을 계산해 두고, 그 사이에 있는 값만 대상으로 평균을 계산하는 방식입니다.

=AVERAGEIFS(E2:E100,
            E2:E100, ">="&$K$3,
            E2:E100, "<="&$K$2)

AVERAGEIFS가 없는 버전에서의 대체 방법

AVERAGE(IF()) 배열 수식으로 구현

제품이 셔츠이고 지역이 서울인 행의 평균 매출을 구하는 배열 수식 예제입니다.

=AVERAGE(IF((B2:B11="셔츠")*(C2:C11="서울"), E2:E11))

SUMIFS / COUNTIFS 조합으로 평균 구하기

SUMIFS와 COUNTIFS를 사용해 조건부 평균을 직접 만드는 방법입니다.

=SUMIFS(E2:E11, B2:B11, "셔츠", C2:C11, "서울")
 / 
 COUNTIFS(B2:B11, "셔츠", C2:C11, "서울")

자주 발생하는 실수와 트러블슈팅

결과가 #DIV/0! 또는 빈 셀로 나오는 경우

• 조건을 만족하는 값이 하나도 없거나
• average_range와 criteria_range의 크기가 다를 때 자주 발생합니다.

조건이 먹지 않거나 이상한 값이 나오는 경우

• 텍스트 조건에 공백이 포함되어 있거나
• 날짜가 텍스트로 입력되어 있는 경우가 많습니다.

트러블슈팅 표

다른 함수와 조합하는 실무 패턴

SUMIFS·COUNTIFS·MAXIFS MINIFS와의 조합

SUMIFS로 조건부 합계, COUNTIFS로 조건부 개수, AVERAGEIFS로 조건부 평균, MAXIFS/MINIFS로 조건부 최대·최소값을 함께 사용하면 제품·지역·채널별 요약 리포트를 손쉽게 만들 수 있습니다.

요약 리포트 템플릿으로 확장하기

요약표를 표로 만든 뒤 위 수식을 행 참조를 이용해 작성하면, 아래로 채우기만으로 전체 조합에 대한 통계를 자동으로 계산할 수 있습니다.

마무리 – 조건부 평균으로 보고서 자동화까지

엑셀 AVERAGEIFS 함수를 이해하면, 단순 평균에서 한 단계 더 나아가 조건별로 나뉜 “진짜 평균”을 계산해 볼 수 있습니다. SUMIFS, COUNTIFS, MAXIFS MINIFS와 조합해 회사의 정기 리포트와 분석 템플릿에 적용해 보세요.

함께 보면 좋은 글:

• 엑셀 SUMIFS 함수로 조건부 합계 완전 정복
• 엑셀 SUBTOTAL 함수로 필터된 데이터만 합계 내기
• 엑셀 VLOOKUP 여러 조건으로 값 찾기 실전 가이드
• 엑셀 MAXIFS MINIFS 함수로 조건별 최대·최소값 완전 정리