- 최초 작성일: 2002-09-05
- 최종 수정일: 2026-09-29
- 조회수: 32 회
- 작성자: 권현욱 (엑셀러)
- 강의 제목: COUNTIF와 배열 수식으로 특정 문자(@) 포함 여부·개수 세기
들어가기 전에
오래 전 엑셀 파일로 진행하던 강의를 웹 글로 복원한 것이라, "버튼을 눌러보세요"처럼 엑셀에서 직접 조작하라는 표현이 남아 있을 수 있습니다. 결과는 본문의 그림이나 표로 확인하실 수 있으며, 현재와 맞지 않는 내용은 보완하되 전반적인 스타일은 당시의 갬성(?)을 유지했습니다.
That which a man likes is half done.
좋아서 하는 일은 이미 반은 이룬 셈입니다. 무슨 일을 하든 흥미를 느끼는 것이 중요하겠지요.
- 최신 글을 불러오는 중...
COUNTIF와 배열 수식으로 특정 문자(@) 포함 여부·개수 세기
핵심 요약
특정 문자가 포함된 셀이 몇 개인지는 COUNTIF의 와일드카드로, 그 문자가 전체 데이터에서 총 몇 번 등장하는지는 LEN과 SUBSTITUTE를 결합한 배열 수식으로 각각 다르게 구해야 합니다.
COUNTIF(범위,"*"&문자&"*")로 해당 문자가 포함된 셀의 개수를 셉니다.LEN(원문)-LEN(SUBSTITUTE(원문,문자,""))은 문자를 지운 만큼 줄어든 길이, 즉 등장 횟수입니다.- 여러 셀에 걸쳐 합산하려면 SUM과 함께 배열 수식(Ctrl+Shift+Enter)으로 입력합니다.
질문: A열에서 "@"이 들어있는 셀의 개수는 어떻게 구하나요?
A열에 ragi@sirpo.seoul.kr, 34, 23, apple, @, 비린내, 굿, cu@k리그와 같은 데이터가 있다고 가정하고, 이 중에서 "@"이 들어있는 셀의 개수는 어떻게 구하나요? 즐거운 하루 보내세요.
이 문제를 해결하기 위해 VBA를 사용하여 코딩을 할 수도 있으나, 우리가 아주 잘 알고 있는 COUNTIF 함수를 사용하면 굳이 프로그래밍을 할 필요도 없습니다. 자주 들러서 좋은 답변을 주시는 드래곤볼님이 이런 답변을 주셨네요.
함수로는, =COUNTIF(범위,"*@*") 하면 되죠…
잘 되는지 한번 볼까요? 아래의 "찾을 문자열" 셀에 검색할 문자열을 입력해 보세요.
| 데이터 | 구분 | 수식 |
|---|---|---|
| ragi@sirpo.seoul.kr | 찾을 문자열 | @ |
| 34 | 해당 셀의 수 | =COUNTIF(B38:B45,"*"&F38&"*") |
| 23 | ||
| apple | ||
| @ | ||
| 비@린@내@ | ||
| 굿 | ||
| cu@k리그 |
잘 되지요? 당연히… 그런데 셀의 개수를 파악하는 것이 아니라 "@" 표시가 모두 몇 개나 있는지를 파악해야 한다면 어떻게 해야 할까요?
{=SUM(LEN(B38:B45)-LEN(SUBSTITUTE(B38:B45,F38,"")))}그렇습니다! 위와 같이 배열 수식을 사용하면 해결이 됩니다. LEN 함수를 사용하여 전체 문자열의 길이를 구한 다음, SUBSTITUTE 함수를 이용하여 "@" 표시를 ""로 치환한 문자열 길이의 값을 빼버리면, 그 차이만큼이 곧 "@"가 등장한 횟수가 되는 것이지요.
정리 — 특정 문자 포함 여부·개수 구하기
| 구분 | 수식 |
|---|---|
| 포함된 셀의 개수 | =COUNTIF(범위,"*"&문자&"*") |
| 문자의 총 등장 횟수(배열 수식) | {=SUM(LEN(범위)-LEN(SUBSTITUTE(범위,문자,"")))} |
| 배열 수식 입력 방법 | 수식 입력 후 Ctrl+Shift+Enter (중괄호 {}는 자동 생성) |
자주 묻는 질문 (FAQ)
Q1. COUNTIF(*문자*)와 배열 수식의 차이는 무엇인가요?
COUNTIF(범위,"*@*")는 '@'가 하나라도 포함된 셀의 개수만 셉니다. 한 셀에 '@'가 여러 번 나오더라도 1개로 셉니다. 반면 LEN과 SUBSTITUTE를 결합한 배열 수식은 셀 안에 등장하는 '@' 문자의 총 등장 횟수를 셉니다.
Q2. 배열 수식은 어떻게 입력해야 하나요?
수식을 입력한 뒤 Enter가 아니라 Ctrl+Shift+Enter를 눌러야 합니다. 정상적으로 입력되면 수식 앞뒤에 중괄호 {}가 자동으로 붙습니다.
Q3. 찾는 문자를 셀 참조가 아니라 직접 입력해도 되나요?
네, SUBSTITUTE(B38:B45,F38,"") 부분의 F38 대신 "@"처럼 따옴표로 감싼 문자를 직접 넣어도 동일하게 동작합니다. 다만 자주 바뀌는 검색어라면 셀 참조 방식이 더 편리합니다.
마치며
'포함된 셀의 개수'와 '문자의 총 등장 횟수'는 비슷해 보이지만 전혀 다른 질문입니다. COUNTIF는 전자를, LEN+SUBSTITUTE 배열 수식은 후자를 담당한다는 점만 구분해서 기억해 두면 비슷한 문제를 만났을 때 헷갈리지 않을 것입니다.
