여러 기준이 있는 경우 계산 – Excel 및 Google 스프레드시트

이 튜토리얼에서는 Excel 및 Google 스프레드시트에서 COUNTIFS 함수를 사용하여 여러 기준으로 ‘count if’를 활용하는 방법을 보여드립니다.사용 중인 Excel 버전에서 COUNTIFS함수를 지원하지 않는 경우, 대신 SUMPRODUCT 함수를 사용하는 방법도 설명해 드립니다.

COUNTIFS 함수

COUNTIFS함수를 사용하면 여러 기준을 충족하는 값을 계산할 수 있습니다. 기본 수식 구조는 다음과 같습니다.

=COUNTIFS(범위 1, 조건 1, 범위 2, 조건 2)

예제를 살펴보겠습니다. 아래에는 영어 및 수학 학생의 성적이 포함된 목록이 표시됩니다. 수학 시험 점수가 60점보다 높은 학생수를 계산해 보겠습니다.

=COUNTIFS(C3:C12, ">"&60, D3:D12, "수학")

이 경우 두 가지 기준을 테스트합니다.

1. 시험 점수가 60점보다 높은 경우(범위 C3:C12)
2. 과목이 “수학”인 경우(범위는 D3:D12)

아래에서 수학 점수가 60점보다 높은 학생이 4명인 것을 확인할 수 있습니다.

다른 범위와 기준을 추가하여 추가 조건을 테스트할 수 있습니다. 예를 들어 수학에서 60~80점 사이의 점수를 받은 학생 수를 계산하려는 경우 이 기준을 사용할 수 있습니다.

1. 시험 점수가 60점보다 높은 경우
2. 시험 점수가 80점보다 낮은 경우
3. 과목이 “수학”인 경우

다음은 이러한 예가 테스트되는 강조 표시된 섹션입니다.

이 경우 결과는 3입니다

=COUNTIFS(B3:B13,">" & 60,B3:B13,"<"&80,C3:C13,"수학")

SUMPRODUCT 함수

COUNTIFS에 액세스할 수 없는 경우 SUMPRODUCT 함수를 사용하여 동일한 작업을 수행할 수 있습니다. SUMPRODUCT 함수는 복잡한 수식을 사용하여 무한하게 활용될 수 있습니다.( SUMPRODUCT 튜토리얼 참조) 따라서 COUNTIFS 함수에 액세스할 수 있더라도 이 함수에 익숙해지기 시작하는 것이 좋습니다. 구문은 다음과 같습니다.

=SUMPRODUCT(범위 1=조건 1*범위 2=조건 2)

위와 같은 예를 사용하여 ‘수학’ 과목에서 시험 점수가 60점 초과인 모든 학생의 수를 계산하려면 이 수식을 사용하면 됩니다.

=SUMPRODUCT((B3:B13>60)*(C3:C13="수학"))

첫 번째 조건은 해당 범위의 값이 60보다 큰지 테스트하고, 두 번째 조건은 해당 범위의 값이 “수학”인지 테스트합니다. 이렇게 하면 1(TRUE) 또는 0(FALSE)의 부울 값 배열을 얻을 수 있습니다

=SUMPRODUCT(({82;62;71;76;95;45;55;56;72;85;86}>60)*({"수학";"수학";"영어";"수학";"영어";"수학";"영어";"수학";"수학";"영어";"영어"}="수학"))

=SUMPRODUCT(({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE})*({TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE}))

=SUMPRODUCT({1;1;0;1;0;0;0;0;1;0;0})

=4

그런 다음 SUMPRODUCT 함수는 배열의 모든 항목을 합산하여 수학에서  60점보다 높게 받은 학생의 수를 알려줍니다.

Google 스프레드시트에서 여러 기준에 대한 COUNTIFS 및 SUMPRODUCT 사용

Google 스프레드시트에서 위와 동일한 수식 구조를 사용하여 여러 기준으로 계산할 수 있습니다.