조건 두세 개 겹치는 순간, 기본 함수로는 한계가 온다

SUMIFS로 어지간한 조건은 다 처리할 수 있는데, 가끔 "이 조건도, 저 조건도 아닌데, 둘 중 하나만 맞으면서 특정 값보다 커야 하는" 것처럼 복잡한 조건을 만나면 SUMIFS만으로는 안 풀리는 순간이 옵니다. 그럴 때 선배가 알려준 게 배열 수식이었는데, 처음엔 수식이 낯설어서 거부감이 컸습니다. 그런데 원리 하나만 이해하면 SUMIFS나 COUNTIFS로 안 되던 것들이 거의 다 해결되더군요. 오늘은 기본 함수의 한계를 넘어서는 배열 수식을 정리해보겠습니다.

배열 수식이란

일반 수식은 셀 하나씩 계산해서 결과를 하나 냅니다. 배열 수식은 여러 개의 값을 한 번에 계산해서, 그 결과들을 다시 하나로 합치거나 여러 셀에 한꺼번에 채워 넣는 방식입니다. 말로는 감이 잘 안 오니 바로 예시로 넘어가 보겠습니다.

SUMPRODUCT — 배열 수식의 입문 함수

배열 수식 중에서 가장 접근하기 쉬운 함수가 SUMPRODUCT입니다. 다른 배열 수식과 달리 Ctrl+Shift+Enter 같은 특수한 입력 방식 없이, 일반 함수처럼 그냥 Enter만 눌러도 작동합니다.

기본 개념

 

=SUMPRODUCT(범위1, 범위2)

범위1과 범위2를 같은 위치끼리 곱한 다음, 그 결과를 전부 더합니다. 예를 들어 A열에 수량, B열에 단가가 있다면 이렇게 씁니다.

 

=SUMPRODUCT(A2:A10, B2:B10)

A2B2, A3B3, ... A10*B10을 각각 계산해서 전부 더한 값이 나옵니다. 수량 곱하기 단가를 각 행마다 따로 계산할 필요 없이 한 번에 전체 금액을 구할 수 있습니다.

실무 예시: OR 조건으로 집계하기

앞서 다룬 COUNTIFS는 조건들이 모두 만족해야 하는 AND 조건만 처리했습니다. "부서가 영업팀이거나, 혹은 인사팀인 사람의 매출 합계"처럼 OR 조건을 걸어야 한다면 SUMPRODUCT가 유용합니다.

 

=SUMPRODUCT((B2:B100="영업팀")+(B2:B100="인사팀"), C2:C100)

괄호 안의 조건이 참이면 1, 거짓이면 0으로 바뀌는데, 이 둘을 더하면(+) OR 조건이 됩니다. 이렇게 만들어진 배열과 매출 범위(C2:C100)를 곱해서 더하면, 영업팀이거나 인사팀인 사람들의 매출만 합산됩니다. AND 조건이 필요하면 +대신 *를 쓰면 됩니다.

실무 예시: 여러 조건을 동시에, 곱셈으로 연결하기

 

=SUMPRODUCT((B2:B100="영업팀")*(D2:D100>=1000000), C2:C100)

"부서가 영업팀이면서, 동시에 D열 값이 백만원 이상인" 행들만 골라서 C열 합계를 냅니다. 조건마다 괄호로 감싼 뒤 곱셈(*)으로 연결하면 AND로 묶입니다. SUMIFS로도 이 정도는 되지만, 조건 안에 부등호나 계산식이 복잡하게 섞이면 SUMPRODUCT가 더 유연하게 처리해줍니다.

배열 수식(Ctrl+Shift+Enter) 직접 입력하기

SUMPRODUCT처럼 자체적으로 배열을 처리하는 함수가 아니라, 일반 함수를 배열처럼 강제로 동작시켜야 할 때는 수식을 입력한 뒤 Enter 대신 Ctrl+Shift+Enter를 눌러야 합니다. 이렇게 입력하면 수식 앞뒤로 중괄호({ })가 자동으로 붙는데, 이 중괄호는 직접 타이핑하는 게 아니라 Ctrl+Shift+Enter를 눌렀을 때 엑셀이 자동으로 붙여주는 것입니다.

실무 예시: 조건에 맞는 값 중 최댓값 찾기

MAX 함수는 원래 조건 없이 범위 전체의 최댓값만 구합니다. "영업팀 중에서 매출 최댓값"처럼 조건을 걸어야 한다면 이렇게 씁니다.

 

=MAX(IF(B2:B100="영업팀", C2:C100))

이 수식을 입력한 뒤 Ctrl+Shift+Enter를 누릅니다. 그러면 IF가 B열의 조건에 맞는 행에서만 C열 값을 골라내고, MAX가 그 중 최댓값을 찾는 방식으로 동작합니다.

여기서 신입들이 자주 하는 실수는 이런 수식을 그냥 Enter로 입력해서, 원하는 결과 대신 엉뚱한 값이나 오류가 나오는 경우입니다. IF 안에 범위(배열)가 통째로 들어간 수식은 반드시 Ctrl+Shift+Enter로 입력해야 한다는 걸 기억해두셔야 합니다.

최신 엑셀에서는 동적 배열이 기본이다

마이크로소프트 365나 최신 버전 엑셀에서는 Ctrl+Shift+Enter를 누르지 않아도 배열 수식이 자동으로 처리되는 경우가 많아졌습니다. 이걸 동적 배열이라고 부르는데, UNIQUE(중복 제거), FILTER(조건에 맞는 값만 추출), SORT(정렬) 같은 함수들이 대표적입니다. 다만 회사에서 오래된 버전을 쓰고 있다면 여전히 Ctrl+Shift+Enter 방식을 알아두셔야 합니다.

자주 하는 실수 정리

• 배열 수식이 필요한데 그냥 Enter로 입력해서 결과가 이상하게 나오는 경우: 수식 안에 범위(배열)가 통째로 들어가 있는데 결과가 하나만 나와야 한다면, Ctrl+Shift+Enter가 필요한 신호입니다.
• 이미 만들어진 배열 수식을 수정한 뒤 다시 Ctrl+Shift+Enter를 안 누르는 경우: 한 번 배열 수식으로 입력했더라도, 수정한 뒤 그냥 Enter로 마무리하면 배열 기능이 풀려서 원하는 결과가 안 나옵니다.
• SUMPRODUCT에서 조건 범위와 계산 범위의 행 개수가 다른 경우: 곱셈으로 연결되는 구조라서, 범위 크기가 안 맞으면 바로 오류가 뜹니다.
• AND는 곱셈(*), OR는 덧셈(+)이라는 걸 헷갈리는 경우: 반대로 쓰면 원하는 조건과 정반대의 결과가 나올 수 있습니다.

마치며

배열 수식은 처음 마주치면 낯설고 복잡해 보이지만, "여러 값을 한 번에 계산해서 하나로 모은다"는 핵심 원리만 이해하면 SUMIFS, COUNTIFS로 안 풀리던 복잡한 조건들도 대부분 해결됩니다. 처음부터 어려운 수식을 만들려고 하기보다, SUMPRODUCT로 OR 조건 처리하는 것부터 익혀보시고, 익숙해지면 Ctrl+Shift+Enter가 필요한 배열 수식으로 넘어가시길 추천드립니다. 지금까지 데이터 입력의 기본부터 함수, 피벗테이블, 차트, 문서 보호, 통합, 시트 비교, 배열 수식까지 실무에서 정말 자주 쓰는 엑셀 기능들을 쭉 정리해봤습니다. 다음에는 이 시리즈를 보신 분들이 실제로 궁금해하시는 질문들을 모아서 정리해보려고 합니다.