XLOOKUP SUMPRODUCT 조합으로 복잡한 조건 합계 한 번에 끝내기
엑셀로 매출 분석을 하다 보면 가격표는 따로, 판매내역은 따로, 조건은 여러 개라서 수식이 꼬이기 쉽습니다. 이럴 때 XLOOKUP SUMPRODUCT 조합을 쓰면, 단가 조회와 조건부 합계를 한 번에 처리하면서도 수식을 깔끔하게 유지할 수 있습니다. 이 글에서는 초보자도 따라할 수 있도록, 기본 개념에서 실무 예제, 오류 해결까지 단계별로 정리해 보겠습니다.
Quick Fix: XLOOKUP SUMPRODUCT 조합, 이 수식 하나로 바로 해결
예제 시나리오
시트1: 가격표
| 제품코드(A) | 단가(B) |
|---|---|
| A001 | 10000 |
| A002 | 8000 |
| A003 | 12000 |
시트2: 판매내역
| 일자(A) | 제품코드(B) | 수량(C) | 지역(D) |
|---|---|---|---|
| 2025-01-01 | A001 | 5 | 서울 |
| 2025-01-01 | A002 | 2 | 부산 |
| 2025-01-02 | A001 | 3 | 서울 |
| 2025-01-02 | A003 | 1 | 대구 |
목표: 서울 지역에서 판매한 전체 매출 합계를 한 번에 구하는 것(가격은 가격표 시트, 수량은 판매내역 시트에 존재).
바로 쓸 수 있는 수식
=SUMPRODUCT(
(판매내역!$D$2:$D$100="서울")*
(판매내역!$C$2:$C$100)*
XLOOKUP(
판매내역!$B$2:$B$100,
가격표!$A$2:$A$100,
가격표!$B$2:$B$100
)
)
이 수식 하나로 “서울 조건 필터링 + 제품별 단가 조회 + 수량×단가 매출 계산 + 전체 합계”가 동시에 처리됩니다.
XLOOKUP와 SUMPRODUCT 기본 개념 정리
XLOOKUP 핵심 옵션과 배열 반환
XLOOKUP 기본 형식은 다음과 같습니다.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
lookup_value: 찾을 값 또는 범위lookup_array: 찾을 범위return_array: 결과를 돌려줄 범위[if_not_found]: 값을 찾지 못했을 때 반환할 값[match_mode]: 정확히 일치, 근사값, 와일드카드 등[search_mode]: 검색 방향(위에서 아래, 아래에서 위 등)
lookup_value에 단일 값 대신 B2:B100 같은 범위를 넣으면 XLOOKUP은 각 값에 대해 단가를 배열로 한 번에 반환합니다. 이 배열을 그대로 SUMPRODUCT에 넘길 수 있습니다.
SUMPRODUCT의 원리와 조건부 합계/개수
SUMPRODUCT 기본 형식은 다음과 같습니다.
=SUMPRODUCT(array1, [array2], [array3], ...)
각 배열의 같은 위치끼리 곱한 뒤, 그 결과를 모두 더한 값을 반환합니다. TRUE/FALSE를 포함한 논리 배열을 곱하면 TRUE는 1, FALSE는 0으로 처리되므로 조건 필터링에 활용할 수 있습니다.
실무 예제 1: 가격표와 판매내역을 한 번에 곱해서 매출 계산
샘플 데이터 구조
가격표 시트와 판매내역 시트는 다음과 같이 구성합니다.
- 가격표: A열 제품코드, B열 단가
- 판매내역: A열 일자, B열 제품코드, C열 수량, D열 지역
XLOOKUP을 SUMPRODUCT 안에서 호출하는 패턴
전체 매출 합계를 구하는 수식은 다음과 같습니다.
=SUMPRODUCT(
판매내역!$C$2:$C$100,
XLOOKUP(
판매내역!$B$2:$B$100,
가격표!$A$2:$A$100,
가격표!$B$2:$B$100
)
)
서울 지역 매출만 합계하려면 조건 배열을 곱해 줍니다.
=SUMPRODUCT(
(판매내역!$D$2:$D$100="서울")*
판매내역!$C$2:$C$100,
XLOOKUP(
판매내역!$B$2:$B$100,
가격표!$A$2:$A$100,
가격표!$B$2:$B$100
)
)
SUMIFS·VLOOKUP/INDEX-MATCH와 비교
SUMIFS는 단순 조건부 합계에는 좋지만, “수량×단가”처럼 행마다 계산한 값을 합산하려면 매출 열을 따로 만들어야 하는 경우가 많습니다. XLOOKUP SUMPRODUCT 조합을 쓰면 가격표가 다른 시트에 있어도 한 줄 수식으로 해결할 수 있고, 조건을 추가해도 구조가 크게 바뀌지 않습니다.
실무 예제 2: 여러 조건(제품+지역+기간)으로 필터링한 매출 합계
조건 셀 준비
판매내역 시트 상단에 조건 입력 영역을 만듭니다.
- G3: 제품코드 (예: A001)
- H3: 지역 (예: 서울)
- I3: 시작일자 (예: 2025-01-01)
- J3: 종료일자 (예: 2025-01-31)
다중 조건 배열 수식
=SUMPRODUCT(
(판매내역!$B$2:$B$100=$G$3)*
(판매내역!$D$2:$D$100=$H$3)*
(판매내역!$A$2:$A$100>=$I$3)*
(판매내역!$A$2:$A$100<=$J$3)*
판매내역!$C$2:$C$100,
XLOOKUP(
판매내역!$B$2:$B$100,
가격표!$A$2:$A$100,
가격표!$B$2:$B$100
)
)
네 가지 조건을 모두 만족하는 행만 1이 되고, 그 행의 수량과 단가를 곱해 매출을 합산합니다.
OR 조건/복수 선택 확장
지역을 “서울 또는 부산”으로 합치고 싶다면 지역 조건만 다음처럼 바꿉니다.
((판매내역!$D$2:$D$100="서울")+
(판매내역!$D$2:$D$100="부산"))
배열 덧셈은 OR, 배열 곱셈은 AND 역할을 한다고 이해하면 편합니다.
대체 방법과 한계, 성능까지 생각한 설계
FILTER, SUMIFS, 피벗테이블과의 역할 분담
- 단순 조건부 합계: SUMIFS가 빠르고 직관적
- 행마다 계산한 값을 합산: XLOOKUP SUMPRODUCT 조합이 열 추가 없이 유리
- 데이터 집계·시각화: 피벗테이블 또는 Power Pivot 권장
LET, LAMBDA와 함께 쓰는 구조
복잡한 수식은 LET 함수로 정리하면 가독성이 좋아집니다.
=LET(
제품, 판매내역!$B$2:$B$100,
수량, 판매내역!$C$2:$C$100,
지역, 판매내역!$D$2:$D$100,
단가, XLOOKUP(제품, 가격표!$A$2:$A$100, 가격표!$B$2:$B$100),
조건, (지역="서울"),
SUMPRODUCT(조건*수량*단가)
)
Troubleshooting: 자주 나오는 오류와 체크리스트
| 증상 | 대표 원인 | 해결 방법 |
|---|---|---|
| #N/A 오류 | XLOOKUP에서 찾는 값이 가격표에 없음 | 가격표 범위와 오타, 공백 확인, [if_not_found] 인수 또는 IFERROR 사용 |
| #VALUE! 오류 | SUMPRODUCT에 들어가는 배열 길이가 서로 다름 | 모든 배열의 행·열 개수 일치 여부 확인 |
| 결과가 0 또는 너무 작음 | 조건 배열이 잘못되어 대부분 0이 됨 | AND/OR 논리, 괄호 위치 재점검 |
| 결과가 너무 큼 | 조건이 빠져 있거나 중복 곱셈 발생 | 불필요한 배열 곱셈 제거, 조건 범위 재확인 |
| 계산이 느려짐 | 수많은 행에 복잡한 XLOOKUP SUMPRODUCT 조합 사용 | 범위 축소, LET로 중복 계산 제거, 필요 시 Power Query/피벗테이블 사용 |
마무리: XLOOKUP SUMPRODUCT 조합 잘 쓰는 체크리스트
- XLOOKUP SUMPRODUCT 조합으로 단가 조회와 조건부 합계를 한 줄 수식으로 처리한다.
- 조건은
(조건1)*(조건2)처럼 배열 곱셈으로 구현한다. - OR 조건은
(조건A)+(조건B)처럼 배열 덧셈으로 구현한다. - SUMPRODUCT 인수로 들어가는 배열들은 항상 같은 크기인지 확인한다.
- XLOOKUP의
[if_not_found]인수를 적절히 사용해 #N/A 오류를 미리 방지한다. - 수식이 길어지면 LET 함수로 범위에 이름을 붙여 가독성을 유지한다.
이 체크리스트만 기억하면, XLOOKUP SUMPRODUCT 조합으로 가격표·판매내역·복잡한 조건이 섞인 대부분의 실무 계산을 안정적으로 처리할 수 있습니다.
VLOOKUP에서 XLOOKUP으로 전환하는 방법이 궁금하다면 VLOOKUP 대신 XLOOKUP으로 바꾸는 방법 글을 참고해 보세요.