합계 IF 수식 – Excel 및 Google 스프레드시트
이 튜토리얼에서는 특정 기준을 충족하는 셀에 대해서만 SUBTOTAL 함수를 수행하여 “if조건별 합계”를 계산하는 방법을 보여 줍니다.
SUBTOTAL 함수
SUBTOTAL 함수는 다양한 데이터 범위(카운트, 합계, 평균 등)에 대해 다양한 계산을 수행하는 데 사용됩니다. 이 함수에 익숙하지 않다면 왜 COUNT, SUM 또는 AVERAGE 함수를 직접 사용하지 않는지 궁금할 것입니다. 두 가지 좋은 이유가 있습니다:
- SUBTOTAL 옵션(1,2,3,4 등)을 나열하는 테이블을 만들고 단일 수식을 복사하여 요약 데이터를 만들 수 있습니다. (이 문서에서 설명하는 것처럼 SUBTOTAL IF를 계산하려는 경우 특히 시간을 크게 절약할 수 있습니다.)
- SUBTOTAL 함수는 표시된(필터링된) 행만 계산하는 데 사용할 수 있습니다.
여기서는 SUBTOTAL 함수의 두 번째 특징을 구현하는데 중점을 두겠습니다.
이 예제에서는 SUBTOTAL function_num 인수를 3으로 설정하여 보이는 행 수를 세는(COUNT)하는 함수를 사용합니다(사용 가능한 함수의 전체 목록은 여기에서 확인할 수 있습니다.)
=SUBTOTAL(3,$D$2:$D$14)
행을 수동으로 필터링할 때 결과가 어떻게 달라지는지 확인해 보세요.
SUBTOTAL IF
“소계 IF”를 만들기 위해 배열 수식에 SUMPRODUCT, SUBTOTAL, OFFSET, ROW 및 MIN의 조합을 사용합니다. 이 조합을 사용하면 기본적으로 일반적인 “SUBTOTAL IF” 함수를 만들 수 있습니다. 예제를 살펴보겠습니다.
각 이벤트에 대한 회원 목록과 회원의 참석 상태가 있습니다:
다음과 같이 목록을 수동으로 필터링하면서 이벤트에 참석한 회원의 수를 동적으로 계산해야 한다고 가정해 보겠습니다.
이를 위해 다음 수식을 사용할 수 있습니다:
=SUMPRODUCT((<값 범위>=<기준>)*(SUBTOTAL(3,OFFSET(<범위 내 첫 번째 셀>,ROW(<값 범위>)-MIN(ROW(<값 범위>)),0))))=SUMPRODUCT((D2:D14="참석")*(SUBTOTAL(3,OFFSET(D2,ROW(D2:D14)-MIN(ROW(D2:D14)),0))))Excel 2019 및 이전 버전을 사용하는 경우, 배열 수식을 입력할 때는 배열 수식을 입력 중임을 Excel에 알리기 위해 CTRL + SHIFT + ENTER를 눌러 배열 수식을 입력해야 합니다. 수식 주위에 중괄호가 나타나면 수식이 배열 수식으로 제대로 입력되었음을 알 수 있습니다.(위 이미지 참조)
수식은 어떻게 작동하나요?
이 수식은 SUMPRODUCT 안에 두 개의 배열을 곱하는 방식으로 작동하며, 첫 번째 배열은 기준을 처리하고 두 번째 배열은 보이는 행으로만 필터링합니다.
=SUMPRODUCT(<기준 배열>*<가시성 배열>)기준 배열
기준 배열은 값 범위(이 예에서는 ‘참석’ 상태)의 각 행을 평가하여 다음과 같은 배열을 생성합니다:
=(<값 범위>=<기준>)=(D2:D14="참석")출력:
{TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE}수식의 첫 번째 배열의 출력은 행이 표시되는지 여부를 무시하므로 두 번째 배열이 도움이 된다는 점에 유의하세요.
가시성 배열
SUBTOTAL을 사용하여 범위에서 보이지 않는 행을 제외하면 가시성 배열을 생성할 수 있습니다. 그러나 SUBTOTAL만으로는 단일 값을 반환하는 반면 SUMPRODUCT는 여러 값의 배열을 기대합니다. 이 문제를 해결하기 위해 OFFSET을 사용하여 한 번에 한 행씩 전달합니다. 이 기법을 사용하려면 한 번에 하나의 숫자가 포함된 배열을 OFFSET에 공급해야 합니다. 두 번째 배열은 다음과 같습니다:
=SUBTOTAL(3,OFFSET(<범위 내 첫 번째 셀>,ROW(<값 범위>)-MIN(ROW(<값 범위>)),0))=SUBTOTAL(3,OFFSET(D2,ROW(D2:D14)-MIN(ROW(D2:D14)),0))Output:
{1;1;0;0;1;1}둘을 연결합니다:
=SUMPRODUCT({TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE} * {1; 1; 0; 0; 1; 1})= 4여러 기준이 있는 SUBTOTAL IF
여러 기준을 추가하려면 다음과 같이 SUMPRODUCT 내에 여러 기준을 더 추가하면 됩니다:
=SUMPRODUCT((<값 범위 1>=<기준 1>)*(<값 범위 2>=<기준 2>)*(SUBTOTAL(3,OFFSET(<범위 내 첫 번째 셀>,ROW(<값 범위>)-MIN(ROW(<값 범위>)),0))))=SUMPRODUCT((E2:E14="Attended")*(B2:B14=2019)*(SUBTOTAL(3,OFFSET(E2,ROW(E2:E14)-MIN(ROW(E2:E14)),0))))
Google 스프레드시트의SUBTOTAL IF
SUBTOTAL IF 함수는 Excel에서와 마찬가지로 Google 스프레드시트에서도 똑같이 작동합니다. 다만, 배열 수식을 입력할 때 CTRL + SHIFT _ ENTER 키를 눌러야 하지만 Google 스프레드시트에서는 수식에 ARRAYFORMULA 함수를 추가한다는 점이 다릅니다.(이 함수를 수동으로 추가할 수도 있음)
