COUNTIFS로 여러 조건에 따라 세기

COUNTIFS는 여러 조건을 동시에 충족하는 행의 개수를 셉니다. COUNTIF가 “동부에서 판매된 건수는 몇 건인가요?”라는 질문에 답한다면, COUNTIFS는 “1월에 발생한 동부 판매 건수는 몇 건인가요?”라는 질문에 답합니다.

COUNTIFS는 값을 더하거나 평균을 계산하지 않고, 모든 검사를 통과한 행의 개수만 셉니다. 따라서 “신규 고객에게서 발생한 100달러 초과 주문은 몇 건인가요?”와 같은 질문에 적합합니다.

이제 구조를 배우고 직접 사용해 보겠습니다.

COUNTIFS에는 sum_range가 없습니다. 값을 더하는 함수가 아니라 검사할 범위와 그 범위에 적용할 조건만 필요하기 때문입니다.

• criteria_range1, criteria1 — 첫 번째 검사
• criteria_range2, criteria2 — 두 번째 검사
• ...이와 같은 방식으로 최대 127개의 쌍까지 사용

모든 인수는 깔끔한 범위, 조건 쌍으로 구성됩니다. 결과는 모든 조건이 참인 행의 개수인 하나의 정수입니다.

같은 판매 표를 사용해 보겠습니다. A열은 지역, B열은 월, C열은 금액입니다. 동부 지역이면서 1월에 발생한 판매가 몇 건인지 세어 보겠습니다.

이 수식에는 금액 열이 없습니다. 금액을 합산하는 것이 아니라 행의 개수를 세기 때문입니다. 각 쌍이 집계 범위를 더욱 좁힙니다.

SUMIFS와 마찬가지로 COUNTIFS도 숫자를 검사할 때 따옴표 안에 비교 연산자를 사용할 수 있습니다.

• ">100" — 100보다 큰 값
• ">=18" — 18세 이상
• "<>" — 비어 있지 않은 모든 셀

여기서는 금액이 100보다 큰 동부 지역의 행을 셉니다. 금액 열은 sum_range가 아니라 조건 범위로 사용된다는 점에 유의하세요.

두 숫자 사이에 있는 값을 세어야 하는 경우가 많습니다. 하나의 조건만으로는 이를 처리할 수 없으므로, 하한과 상한을 지정해 같은 열을 두 번 사용합니다.

이 수식은 50 이상 이고 200 이하인 금액을 셉니다. 두 조건이 모두 C열을 가리키며, COUNTIFS가 AND 논리를 사용하므로 해당 구간 안에 있는 행만 계산됩니다.

범위를 동적으로 만들려면 기준값을 셀에 저장하고 &로 연산자와 연결하세요. 낮은 값을 F1에, 높은 값을 F2에 입력합니다.

">="&F1은 F1 이상이라는 조건을 만듭니다. 따라서 사용자가 새로운 한도를 입력하면 수식 자체를 수정하지 않고도 업데이트된 개수를 즉시 확인할 수 있습니다.

데이터 품질을 확인할 때 유용한 두 가지 특수 조건이 있습니다.

• "<>"는 비어 있지 않은 셀을 셉니다.
• "=" 또는 ""는 비어 있는 셀을 셉니다.

이 수식은 판매 담당자가 입력된 동부 지역의 행 수를 셉니다. 즉, D열이 비어 있지 않은 행의 개수를 세어 누락된 데이터를 빠르게 찾을 수 있습니다.

텍스트 조건은 SUMIFS와 동일한 와일드카드를 지원합니다. 여러 문자의 연속에는 *를, 하나의 문자에는 ?를 사용하세요.

이 수식은 판매 담당자 이름이 M으로 시작하는 모든 행을 셉니다. 마리아, 마이크, 모하메드는 모두 일치합니다. 와일드카드는 텍스트에만 적용되며 숫자에는 무시됩니다.

SUMIFS에서 적용한 동일한 크기 규칙이 적용됩니다. 모든 조건 범위는 같은 형태여야 합니다. 한 범위가 A2:A100이고 다른 범위가 B2:B50이면 COUNTIFS가 #VALUE! 오류를 반환합니다.

A:A와 B:B 같은 전체 열 참조는 자동으로 크기가 같아지므로, 조건 범위에 자주 사용되는 오류를 방지하는 선택지입니다.

COUNTIFS는 여러 대시보드의 핵심 기능입니다. F열에 지역 이름을 세로로 배치하고 1행에 월 이름을 가로로 배치한 다음, 혼합 참조를 사용해 각 조합의 개수를 세면 수식으로 전체 격자를 채울 수 있습니다.

$A:$A로 열을 고정하고 $F2 / G$1로 조회 셀을 고정하면, 하나의 수식을 복사해 전체 교차표의 개수를 채울 수 있습니다.

두 함수 계열의 차이를 명확히 기억하세요. COUNTIF는 하나의 range, criteria 쌍을 받고, COUNTIFS는 하나 이상의 쌍을 받습니다. SUMIFS와 달리 둘 다 별도의 결과 범위가 없으며, 단순히 개수만 셉니다.

조건이 둘 이상 필요하다면 COUNTIFS를 사용하세요. SUM, COUNT, AVERAGE에서 일관된 하나의 패턴만 기억하기 위해 조건이 하나일 때도 COUNTIFS를 사용하는 사람이 많습니다.

COUNTIFS가 SUMIFS와 어떻게 다른지 생각해 보세요.

이제 여러 조건에 해당하는 행의 개수를 셀 수 있습니다.

• COUNTIFS(range1, crit1, range2, crit2, ...) — sum_range 없이 검사할 쌍만 사용합니다.
• 같은 열을 두 번 사용해 하한과 상한을 지정하면 두 값 사이의 범위를 셀 수 있습니다.
• 비어 있지 않은 셀에는 "<>"를 사용하고, 셀을 기준으로 한 한도에는 &로 연결하세요.
• 모든 범위의 크기가 같아야 합니다.

다음으로는 AVERAGEIFS를 사용해 여러 조건에 해당하는 값의 평균을 계산합니다.