엑셀 COUNTIF로 1~50 번호별 등장 횟수 세기

2026-09-11

번호가 한 줄에 여섯 개씩 수백 줄 쌓여 있는데, 1번이 몇 번 나왔고 37번이 몇 번 나왔는지 세려니 눈으로는 답이 없는 상황입니다. 회차별 당첨번호든, 설문의 선택 항목이든, 상품 옵션 코드든 모양은 같습니다. 이 글은 그 표를 COUNTIF 한 줄로 집계하는 방법과, 숫자가 맞게 나온 것처럼 보이면서 틀리는 자리를 다룹니다.

먼저 안 되는 것부터 말씀드립니다. 한 칸에 "3, 12, 25, 31, 40, 47" 처럼 쉼표로 붙어 있으면 COUNTIF를 그대로 쓸 수 없습니다. =COUNTIF(A:A,"*1*") 로 세면 11, 21, 31, 41 이 전부 1로 잡힙니다. 이 경우는 두 갈래입니다. 데이터 탭의 텍스트 나누기(구분 기호: 쉼표)로 여섯 칸에 펼친 뒤 아래 방법을 쓰거나, 펼치기 싫으면 앞뒤에 쉼표를 붙여 정확히 그 번호만 찾습니다.

=SUMPRODUCT(--ISNUMBER(SEARCH(","&H2&",", ","&SUBSTITUTE($A$2:$A$100," ","")&",")))

여기서는 번호 하나가 한 칸씩 A2:F100 에 들어 있다고 하겠습니다.

1. H2 에 1, H3 에 =H2+1 을 넣고 H51 까지 채웁니다. 마이크로소프트 365라면 H2 에 =SEQUENCE(50) 한 번이면 됩니다.
2. I2 에 아래 수식을 넣고 I51 까지 채웁니다.

=COUNTIF($A$2:$F$100,H2)

범위를 $ 로 고정하는 것이 전부입니다. 고정을 빼면 아래로 내려갈수록 범위가 밀려서 아래쪽 번호가 실제보다 적게 잡힙니다. 행 끝은 실제 데이터에 맞춰 바꾸되, 여유 있게 잡아도 빈 칸은 안 세니 괜찮습니다.

집계표는 만들었다고 끝이 아니라 검산 한 줄이 붙어야 표가 됩니다. I52 에 =SUM(I2:I51), 그 옆에 =COUNT($A$2:$F$100) 을 넣고 두 숫자가 같은지 봅니다. 여섯 개씩 100줄이면 둘 다 600 이어야 합니다. 다르면 이유는 대개 둘 중 하나입니다.

첫째, 번호가 1~50 밖에 있습니다. 0 이나 51, 오타로 들어간 123 은 어느 칸에도 안 잡히니 SUM 이 COUNT 보다 작아집니다. =COUNTIFS($A$2:$F$100,"<1")+COUNTIFS($A$2:$F$100,">50") 로 몇 개인지 바로 확인할 수 있습니다.

둘째, 숫자가 아닌 값이 섞여 있습니다. 여기가 실무에서 제일 많이 걸리는 자리입니다. 다른 곳에서 복사해 온 번호는 겉으로는 숫자인데 텍스트인 경우가 많습니다. 그런데 COUNTIF 는 관대해서 텍스트 "7" 도 숫자 7 로 세 줍니다. 그래서 I열은 멀쩡해 보이는데 COUNT 는 텍스트를 안 세니 검산에서만 차이가 납니다. 진짜 문제는 눈에 안 보이는 공백입니다. "7 " 이나 웹에서 붙여넣을 때 딸려오는 특수 공백(CHAR(160))이 붙으면 COUNTIF 도 못 찾아서 그 번호만 조용히 빠집니다. =LEN(A2) 가 자릿수보다 크면 이 경우입니다. 옆 시트에 =VALUE(TRIM(SUBSTITUTE(A2,CHAR(160),""))) 로 한 번 씻어낸 뒤 그 범위로 세십시오.

저희도 같은 종류의 실패를 겪었습니다. 쇼핑몰에서 내려받은 주문 내역을 넣으면 흔한 실수를 찾아 주는 점검 도구를 만들었는데, 취소 행이 섞인 파일을 넣어도 "발견된 문제 없음" 이 나왔습니다. 여섯 가지 검사가 전부 숫자가 텍스트인지, 날짜가 날짜인지, 주문번호가 겹치는지 같은 형식 검사였고, 주문상태 열에 "취소"나 "반품" 이 몇 건인지 세는 검사는 하나도 없었기 때문입니다. 형식은 다 맞으니 통과였고, 매출은 취소분만큼 부풀려진 채였습니다. 주문상태 열을 찾아 취소·반품·교환·환불 행을 세고, 한 건이라도 있으면 건수와 함께 경고하도록 검사를 하나 더 붙여 여섯 개를 일곱 개로 만들었습니다. 세지 않은 것은 언제나 0 으로 보입니다. 오류가 나지 않는 것과 맞는 것은 다릅니다.

집계가 맞았으면 정렬만 남습니다. J2 에 =RANK(I2,$I$2:$I$51) 을 넣으면 순위가 붙는데, 같은 횟수가 여럿이면 순위가 겹치고 다음 순위가 비니 =RANK(I2,$I$2:$I$51)+COUNTIF($I$2:I2,I2)-1 로 쓰면 빈틈 없이 매겨집니다. 원본 표는 건드리지 말고 H1:J51 만 복사해 값으로 붙여넣은 뒤 I열 내림차순으로 정렬하시면 됩니다.

정리하면, 수식은 COUNTIF 한 줄이고, 일은 검산 두 줄에 있습니다. SUM 과 COUNT 가 같으면 그때 표를 믿으셔도 됩니다.