COUNTIF 개수가 틀릴 때(원인과 해결 방법)
=COUNTIF(A2:A100,"*"&D2&"*")가 =COUNTIF(A2:A100,D2)보다 크면 일부 셀의 값 앞뒤에 공백이나 다른 글자가 붙어 있는 거예요. TRIM으로 정리하세요.원인별 해결 방법
- 공백이나 보이지 않는 문자가 붙어 있어요
- 데이터의 *나 ?가 와일드카드로 쓰여요
- 긴 번호는 앞 15자리만 비교해요
- 조건이 255자를 넘어요
- 빈 것처럼 보이지만 비어 있지 않아요
- 대소문자를 구분해서 세야 해요
1공백이나 보이지 않는 문자가 붙어 있어요
끝에 공백이 붙은 "완료 "는 "완료"로 세지 않아요. 웹이나 다른 시스템에서 복사한 데이터에는 눈에 안 보이는 공백이 자주 섞여 있어요.
이렇게 확인해요
=LEN(A2)가 보이는 글자 수보다 커요.
해결 방법
보조 열에서 =TRIM(SUBSTITUTE(A2,CHAR(160)," "))로 정리하고 값으로 붙여넣으세요.
2데이터의 *나 ?가 와일드카드로 쓰여요
COUNTIF에서 *는 "아무 글자나", ?는 "아무 글자 하나"라는 뜻이에요. "A*B"를 세면 "AxB"나 "A-12-B"도 함께 세요.
이렇게 확인해요
세려는 값에 *나 ?가 들어 있고, 개수가 너무 많이 나와요.
해결 방법
문자 앞에 물결표(~)를 붙이면 글자 그대로 세요: =COUNTIF(A2:A100,"A~*B"). 셀에 있는 값이라면 =COUNTIF(A2:A100,SUBSTITUTE(SUBSTITUTE(D2,"*","~*"),"?","~?"))를 쓰세요.
3긴 번호는 앞 15자리만 비교해요
COUNTIF는 숫자처럼 생긴 텍스트를 숫자로 바꿔 비교하고, 엑셀 숫자는 유효 자릿수가 15자리예요. 그래서 카드 번호나 계좌번호처럼 16자리가 넘는 번호는 끝자리만 다를 때 같은 값으로 세요.
이렇게 확인해요
값이 16자리 이상이고, COUNTIF가 실제로는 없는 중복을 찾아요.
해결 방법
텍스트를 그대로 비교하세요.
=SUMPRODUCT(--(A2:A100=D2))4조건이 255자를 넘어요
COUNTIF는 255자보다 긴 텍스트를 비교하지 못하고 #VALUE!를 내요.
해결 방법
똑같이 정확히 비교하는 수식을 쓰세요: =SUMPRODUCT(--(A2:A100=D2))
5빈 것처럼 보이지만 비어 있지 않아요
""를 돌려주는 수식은 비어 보여도 빈 셀이 아니에요. COUNTA는 이런 셀도 세기 때문에 채워진 셀 개수가 너무 많이 나와요.
이렇게 확인해요
"빈" 셀을 클릭하면 수식 입력줄에 수식이나 공백이 보여요.
해결 방법
실제로 뭔가 보이는 셀만 세세요.
=SUMPRODUCT(--(LEN(TRIM(A2:A100))>0))6대소문자를 구분해서 세야 해요
COUNTIF는 대소문자를 구분하지 않아서 "abc"와 "ABC"를 같은 값으로 세요.
해결 방법
대소문자를 구분하는 EXACT를 쓰세요.
=SUMPRODUCT(--EXACT(A2:A100,D2))그래도 안 된다면
- 연산자와 셀을 함께 쓸 때는 이어 주세요:
">D2"가 아니라=COUNTIF(B2:B100,">"&D2) - 텍스트로 저장된 숫자는
">100"같은 조건에서 빠져요. 숫자로 변환으로 바꾸세요. - COUNTIF는 숨기거나 필터로 가린 행도 세요.
- 조건이 여러 개면 COUNTIFS를 쓰세요. 모든 범위의 크기가 같아야 해요.