목록에 없는 값 개수 세기 공식
지원 버전 자세히 보기
데이터에서 지정한 목록에 없는 값의 개수를 세는 공식입니다.
인수 설명
01제외 목록으로 개수 세기
=SUMPRODUCT ( --ISNA ( MATCH ( 데이터범위, 제외대상, 0 ) ) )인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
02고정 조건 COUNTIFS 공식
=COUNTIFS ( 데이터범위, "<>"&조건1, 데이터범위, "<>"&조건2 )인수 설명 자세히 보기
03목록에 있는 값 개수 세기
=SUMPRODUCT ( --NOT ( ISNA ( MATCH ( 데이터범위, 목록범위, 0 ) ) ) )인수 설명 자세히 보기
동작 원리
01제외 목록으로 개수 세기
MATCH 함수는 각 값을 제외 목록에서 찾고, ISNA 함수와 SUMPRODUCT 함수가 찾지 못한 값의 개수를 셉니다.
제외 목록으로 개수 세기 공식의 예제 시트에서 D2 셀이 4를 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
MATCH 함수가 목록의 위치를 찾습니다
MATCH 함수는 A2:A8의 각 값을 B2:B4에서 찾아 순번을 반환하고, 찾지 못한 값은 #N/A 오류로 반환합니다.
=MATCH ( A2:A8, B2:B4, 0 )
= {#N/A;1;#N/A;2;#N/A;3;#N/A}
제외 목록에서 찾은 순번과 찾지 못한 값입니다. ISNA 함수가 찾지 못한 값을 표시합니다
ISNA 함수는 MATCH 함수가 #N/A 오류를 반환한 사과, 참외, 사과, 상추 위치만 TRUE로 바꿉니다.
=ISNA ( MATCH ( A2:A8, B2:B4, 0 ) )
= {TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}
목록에 없는 값만 TRUE인 배열입니다. 논리값을 숫자로 바꿉니다
이중 단항 연산자는 TRUE를 1로, FALSE를 0으로 바꿉니다.
=--ISNA ( MATCH ( A2:A8, B2:B4, 0 ) )
= {1;0;1;0;1;0;1}
목록에 없는 값의 위치만 1입니다. SUMPRODUCT 함수가 개수를 셉니다
SUMPRODUCT 함수는 배열의 1을 모두 더해 목록에 없는 값의 개수를 계산합니다.
=SUMPRODUCT ( --ISNA ( MATCH ( A2:A8, B2:B4, 0 ) ) )
= 4
1+0+1+0+1+0+1=4입니다. 03목록에 있는 값 개수 세기
MATCH 함수가 각 값을 목록에서 찾고 ISNA 함수가 만든 논리값을 NOT 함수가 뒤집으면, SUMPRODUCT 함수가 목록에 있는 값의 개수를 셉니다.
목록에 있는 값 개수 세기 공식의 예제 시트에서 F2 셀이 3을 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
MATCH 함수가 목록의 위치를 찾습니다
MATCH 함수는 A2:A8의 각 값을 B2:B4에서 찾아 순번을 반환하고, 찾지 못한 값은 #N/A 오류로 반환합니다.
=MATCH ( A2:A8, B2:B4, 0 )
= {#N/A;1;#N/A;2;#N/A;3;#N/A}
목록에서 찾은 순번과 찾지 못한 값입니다. ISNA 함수가 찾지 못한 값을 표시합니다
ISNA 함수는 MATCH 함수가 #N/A 오류를 반환한 위치만 TRUE로 바꿉니다.
=ISNA ( MATCH ( A2:A8, B2:B4, 0 ) )
= {TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}
목록에 없는 값만 TRUE인 배열입니다. 논리값을 뒤집어 숫자로 바꿉니다
NOT 함수는 목록에 없는 값을 FALSE로, 목록에서 찾은 값을 TRUE로 뒤집고 이중 단항 연산자가 숫자로 바꿉니다.
=--NOT ( ISNA ( MATCH ( A2:A8, B2:B4, 0 ) ) )
= {0;1;0;1;0;1;0}
목록에 있는 값의 위치만 1입니다. SUMPRODUCT 함수가 개수를 셉니다
SUMPRODUCT 함수는 배열의 1을 모두 더해 목록에 있는 값의 개수를 계산합니다.
=SUMPRODUCT ( --NOT ( ISNA ( MATCH ( A2:A8, B2:B4, 0 ) ) ) )
= 3
0+1+0+1+0+1+0=3입니다. 
각 조건을 만족하는 모든 값을 제외 후 계산합니다.
그 공백도 카운트를 해버리더라구요
공백을 제외대상에 넣는 방법이 있을까요?
제시해드린 답변이 문제를 해결하시는데 도움이 되었길 바랍니다. 감사합니다.