엑셀 COUNTIF · SUMIF 함수 — 조건 쓰는 법 · 셀 참조 · 와일드카드(컴활 2급 실기 유형 연습 3문제)
조건에 맞는 칸만 세고(COUNTIF), 더하고(SUMIF), 평균 내는(AVERAGEIF) 함수입니다. 중급 1강에 이어 시험 문제 꼴로 새로 만든 연습 문제 3개(기본 · 응용 · 함정)를 이 PC 엑셀에서 직접 풀었습니다. 조건을 따옴표 안에 쓰는 법, 조건에 셀을 참조하는 법, 와일드카드(*), 범위에 $ 를 붙여야 하는 이유를 칸마다 나온 값으로 보여 줍니다.
이렇게 쟀습니다
문제 문장 · 이름 · 숫자는 모두 이 강을 위해 새로 만들었습니다(문제 번호는 중급 1강에서 이어 4~6). 실습 파일을 이 PC 엑셀(Microsoft 365)에서 열어 답 칸에 수식을 넣고(문제 5 는 아래로 채움) 칸에 보이는 값을 셀 주소별로 읽었습니다. 자주 틀리는 식 11개도 같은 칸에 넣어 결과를 읽었고, 기준 칸 E25 를 바꿨을 때 답이 따라 바뀌는지도 쟀습니다. 그림은 범위를 PDF 로 내보내 만든 것입니다.
대한상공회의소 문제가 아닌 연습용 자작 문제입니다. 함수 인수 창의 칸 이름 표기, 식을 받지 않을 때 뜨는 알림 창 문구, 컴활 실기의 채점 기준 원문은 이번에 확인하지 못했습니다(확인 안 됨).
문제 4 — COUNTIF 와 & "명"
문제 4 (기본) [표4]에서 점수[C3:C10]가 80 이상인 인원 수를 [E3]에 표시하시오. ▶ COUNTIF 함수와 & 연산자 사용 ▶ 표시 예: 3명
COUNTIF 는 칸 두 개 — 어디서(범위) 와 무엇을(조건) 입니다. '80 이상'처럼 비교가 들어간 조건은 통째로 따옴표 안에 ">=80" 로 씁니다. 표시 예가 '3명'이므로 결과 뒤에 &"명" 을 붙입니다. E3 에 =COUNTIF(C3:C10,">=80")&"명" 을 넣자 4명(92 · 80 · 88 · 100 — 딱 80 인 형여울 포함)이 나왔습니다.
![엑셀 표 [표4] 사내 엑셀 평가 점수. A 열 이름, B 열 부서, C 열 점수, E2 머리글 80점 이상 인원, E3 에 4명 이 있고 빨간 테두리가 있다.](/lectures/excel/mid-02-q4-result.png)
| E3 에 넣은 식 | 결과(이 PC 엑셀) | 왜 |
|---|---|---|
=COUNTIF(C3:C10,">=80")&"명" 정답 | 4명 | — |
=COUNTIF(C3:C10,">=80") | 4 | &"명" 빼먹음 — 표시 예와 다름 |
=COUNTIF(C3:C10,"=>80")&"명" | 0명 | '이상'은 >= — 순서를 뒤집으면 0 |
=COUNTIF(C3:C10,>=80) | 엑셀이 식을 받지 않음 | 비교 조건은 따옴표 안에 |
&"명" 을 붙이면 결과가 글자(4명)가 됩니다. 이 칸을 다른 수식에서 숫자로 더해야 한다면 붙이지 말고, 셀 서식(표시 형식)으로 '명'을 보이게 하는 방법도 있습니다 — 시험 문제가 '& 연산자 사용'이라고 했으니 여기서는 &.
문제 5 — SUMIF · AVERAGEIF 와 $
문제 5 (응용) [표5]에서 부서[B14:B21]별 판매액[C14:C21]의 합계와 평균을 [F14:G16]에 표시하시오. ▶ SUMIF, AVERAGEIF 함수 사용 ▶ [F14] · [G14]에 넣은 수식을 아래로 채워 완성할 것
SUMIF 는 칸 세 개 — 조건을 볼 범위(부서), 조건(어느 부서), 더할 범위(판매액)입니다. 조건으로 부서 이름을 직접 쓰지 않고 옆 칸 E14 를 가리키면, 아래로 채울 때 E15 · E16 으로 바뀌어 한 수식으로 세 부서를 다 구합니다. 대신 두 범위는 채워도 움직이지 않게 $ 로 고정합니다(F4 키 — 기초 8강).
| A | B | C | E | F | G | |
|---|---|---|---|---|---|---|
| 12 | [표5] 영업팀 판매 실적 | |||||
| 13 | 담당 | 부서 | 판매액 | 부서 | 합계 | 평균 |
| 14 | 마해든 | 영업1팀 | 1,250,000 | 영업1팀 | ||
| 15 | 반가온 | 영업3팀 | 980,000 | 영업2팀 | ||
| 16 | 방늘봄 | 영업2팀 | 640,000 | 영업3팀 | ||
| 17 | 변단비 | 영업1팀 | 1,120,000 | |||
| 18 | 석라미 | 영업2팀 | 760,000 | |||
| 19 | 설모아 | 영업3팀 | 455,000 | |||
| 20 | 소빛찬 | 영업1팀 | 1,030,000 | |||
| 21 | 염새롬 | 영업2팀 | 1,315,000 | |||
| 칸 | 수식(아래로 채운 뒤) | 값(이 PC 엑셀) |
|---|---|---|
| F14 | =SUMIF($B$14:$B$21,E14,$C$14:$C$21) | 3,400,000 (1,250,000 + 1,120,000 + 1,030,000) |
| F15 | =SUMIF($B$14:$B$21,E15,$C$14:$C$21) | 2,715,000 |
| F16 | =SUMIF($B$14:$B$21,E16,$C$14:$C$21) | 1,435,000 (980,000 + 455,000) |
| G14 · G15 · G16 | =AVERAGEIF($B$14:$B$21,E14,$C$14:$C$21) … | 1,133,333 · 905,000 · 717,500 |
![엑셀 표 [표5] 를 수식 보기로 본 모습. F14:F16 에 =SUMIF($B$14:$B$21,E14,$C$14:$C$21) 부터 E16 까지, G14:G16 에 =AVERAGEIF 같은 꼴이 들어 있고 빨간 테두리가 있다.](/lectures/excel/mid-02-q5-formula.png)
영업1팀 평균은 3,400,000 ÷ 3 = 1,133,333.33… 인데, 칸 표시 형식(#,##0)이 소수를 감춰 1,133,333 으로 보입니다. 칸에 든 값은 소수까지 그대로입니다.
함정 — $ 없이 채우면 셋째 칸만 틀린다
| F14 에 넣고 아래로 채운 식 | F14 · F15 · F16(이 PC 엑셀) |
|---|---|
=SUMIF($B$14:$B$21,E14,$C$14:$C$21) 정답 | 3,400,000 · 2,715,000 · 1,435,000 |
=SUMIF(B14:B21,E14,C14:C21) $ 없음 | 3,400,000 · 2,715,000 · 455,000 |
=AVERAGEIF(B14:B21,E14,C14:C21) $ 없음(G 열) | 1,133,333 · 905,000 · 455,000 |
=SUMIF($B$14:$B$21,E14) 더할 범위 빼먹음 | 0 · 0 · 0 |
![엑셀 표 [표5] 에 $ 없이 채운 SUMIF 결과. F14 3,400,000, F15 2,715,000, F16 455,000 으로 F16 만 정답 1,435,000 과 다르다. F14:F16 에 빨간 테두리가 있다.](/lectures/excel/mid-02-trap-relative.png)
$ 가 없으면 F16 의 식이 =SUMIF(B16:B23,E16,C16:C23) 이 됩니다 — 범위가 두 줄 내려가 15행(반가온, 영업3팀 980,000)이 빠집니다. F15 는 범위가 한 줄 내려갔지만 14행이 영업1팀이라 영업2팀 합계에는 영향이 없어 우연히 맞았습니다. 그래서 위 두 칸만 보고 넘어가기 쉽습니다. 채운 뒤에는 마지막 칸의 수식을 꼭 눌러 범위를 확인하세요.
문제 6 — 조건에 셀 참조 · 와일드카드
문제 6 (함정) [표6]에서 ① 판매 수량[B25:B32]이 기준 수량[E25] 이상인 품목 수를 [F25]에, ② 품목[A25:A32]에 '볼펜'이 들어간 품목의 판매 수량 합계를 [G25]에 표시하시오. ▶ COUNTIF, SUMIF 함수 사용 ▶ ①의 기준은 [E25]를 참조할 것
① 비교 기호와 셀 주소를 함께 쓸 때는 기호만 따옴표 안에, 셀 주소는 & 로 이어 붙입니다 — ">="&E25. 그러면 엑셀이 E25 의 값(50)을 읽어 ">=50" 으로 만듭니다. ② 이름 '어디에든' 볼펜이 있으면 앞뒤에 * 를 붙인 "*볼펜*" 을 씁니다(* = 글자 몇 개든).
| A | B | E | F | G | |
|---|---|---|---|---|---|
| 23 | [표6] 문구 판매 수량 | ||||
| 24 | 품목 | 판매 수량 | 기준 수량 | 기준 이상 품목 수 | 볼펜 들어간 품목 합계 |
| 25 | 젤 볼펜(검정) | 48 | 50 | ||
| 26 | 젤 볼펜(빨강) | 35 | |||
| 27 | 볼펜 리필 | 60 | |||
| 28 | 형광펜 | 72 | |||
| 29 | 연필 | 50 | |||
| 30 | 수정 테이프 | 18 | |||
| 31 | 노트 A5 | 95 | |||
| 32 | 볼펜 세트 | 22 | |||
| 칸 · 식 | 결과(이 PC 엑셀) | 왜 |
|---|---|---|
F25 =COUNTIF(B25:B32,">="&E25) 정답 | 4 | 60 · 72 · 50 · 95 — 딱 50 인 연필 포함 |
| E25 를 60 으로 바꾸면 | 3 | 참조라 기준이 따라 바뀜 |
F25 =COUNTIF(B25:B32,">=50") | 4(E25 를 60 으로 바꿔도 4) | 값은 맞지만 'E25 참조' 조건 위반 |
F25 =COUNTIF(B25:B32,">=E25") | 0 | 따옴표 안의 E25 는 글자 — 'E25' 와 비교 |
F25 =COUNTIF(B25:B32,>=E25) | 엑셀이 식을 받지 않음 | 비교 기호는 따옴표 안에 |
G25 =SUMIF(A25:A32,"*볼펜*",B25:B32) 정답 | 165 | 젤 볼펜(검정) 48 + 젤 볼펜(빨강) 35 + 볼펜 리필 60 + 볼펜 세트 22 |
G25 =SUMIF(A25:A32,"볼펜*",B25:B32) | 82 | '볼펜'으로 시작하는 것만(볼펜 리필 · 볼펜 세트) |
G25 =SUMIF(A25:A32,"볼펜",B25:B32) | 0 | 이름이 딱 '볼펜'인 품목이 없음 |
![엑셀 표 [표6] 문구 판매 수량. A 열 품목, B 열 판매 수량, E25 기준 수량 50, F25 기준 이상 품목 수 4, G25 볼펜 들어간 품목 합계 165. F25:G25 에 빨간 테두리가 있다.](/lectures/excel/mid-02-q6-result.png)
![엑셀 표 [표6] 에 ">=E25" 로 쓴 COUNTIF 결과. F25 가 0 이다. F25 에 빨간 테두리가 있다.](/lectures/excel/mid-02-trap-quote.png)
풀이 순서 한눈에
| 문제 | 누르는 칸 | 넣는 수식(그대로) | 그다음 |
|---|---|---|---|
| 4 | E3 | =COUNTIF(C3:C10,">=80")&"명" | Enter |
| 5 | F14 | =SUMIF($B$14:$B$21,E14,$C$14:$C$21) | Enter → F14:F16 고르고 Ctrl+D |
| 5 | G14 | =AVERAGEIF($B$14:$B$21,E14,$C$14:$C$21) | Enter → G14:G16 고르고 Ctrl+D |
| 6 ① | F25 | =COUNTIF(B25:B32,">="&E25) | Enter |
| 6 ② | G25 | =SUMIF(A25:A32,"*볼펜*",B25:B32) | Enter |
함수 인수 창으로 넣으면 COUNTIF 는 범위 · 조건 두 칸, SUMIF · AVERAGEIF 는 조건 범위 · 조건 · 합계(평균) 범위 세 칸에 위 수식의 인수를 차례로 넣습니다. 문제 4 의 &"명" 은 창을 닫은 뒤 수식 입력줄에서 붙입니다.
자주 막히는 곳
- COUNTIF 가 0 이 나온다: 조건을
"=>80"처럼 거꾸로 썼거나, 셀 주소를 따옴표 안에 넣음 —">="&E25 - 식을 아예 받지 않는다: 비교 기호(
>=)를 따옴표 밖에 씀 - 아래 칸으로 갈수록 합계가 이상하다: 범위에 $ 를 안 붙임 — 마지막 칸의 수식을 눌러 범위가 그대로인지 확인
- SUMIF 가 0 이 나온다: 셋째 칸(더할 범위)을 빼먹어 글자 칸을 더함
- 와일드카드 결과가 적다:
"볼펜*"은 '시작',"*볼펜"은 '끝',"*볼펜*"은 '어디든'
같이 보면 좋은 글
- 엑셀 중급 1강 — IF 함수(AND · OR · 중첩 IF): https://www.coding-now.com/blog/excel/excel-intermediate-01-if-function
- 엑셀 기초 8강 — 단축키(F4 로 $ 붙이기): https://www.coding-now.com/blog/excel/excel-basic-08-shortcuts
- 네이버 블로그 cflab 컴활 2급 실기 ④ — COUNTIF · SUMIF · AVERAGEIF(S): https://blog.naver.com/cflab/224436915256
- 네이버 블로그 cflab 컴활 2급 실기 ⑲ — SUMIF: https://blog.naver.com/cflab/224458755864
- 네이버 블로그 cflab 컴활 2급 실기 ⑳ — COUNTIF: https://blog.naver.com/cflab/224459975252
연습 문제
실습 파일의 '문제' 시트에 세 문제가 있고, '계산작업' 시트의 [표4]~[표6] 답 칸이 비어 있습니다. 풀고 나서 정답 파일과 맞춰 보세요.
- 1문제 4 — E3 이 4명인지 확인합니다.
- 2문제 5 — F14 · G14 에 한 번씩만 수식을 쓰고 아래로 채워 3,400,000 · 2,715,000 · 1,435,000 / 1,133,333 · 905,000 · 717,500 이 나오는지, F16 의 수식 범위가 $B$14:$B$21 그대로인지 확인합니다.
- 3문제 6 — F25 4 · G25 165 를 확인한 뒤 E25 를 60 으로 바꿔 F25 가 3 이 되는지 봅니다.
- 4더 해 보기 — G25 의 조건을
"*펜*"으로 바꾸면 어떤 품목이 더해질지 먼저 예상해 보세요(젤 볼펜 2 · 볼펜 리필 · 형광펜 · 볼펜 세트 — 직접 확인).
자주 묻는 질문
Q. COUNTIF 조건에 다른 셀 값을 쓰려면요?
비교 기호만 따옴표 안에 두고 셀 주소는 & 로 이어 붙입니다 — =COUNTIF(B25:B32,">="&E25). 이 PC 에서 4 가 나왔고, E25 를 60 으로 바꾸자 3 으로 따라 바뀌었습니다. ">=E25" 처럼 주소를 따옴표 안에 넣으면 0 이 나왔습니다.
Q. SUMIF 를 아래로 채웠더니 마지막 칸만 틀려요.
조건 범위 · 합계 범위에 $ 가 없어 채울 때 범위가 내려간 것입니다. 이 강 문제 5 에서 $ 없이 채우자 F14 · F15 는 맞고 F16 만 455,000(정답 1,435,000)이었습니다. =SUMIF($B$14:$B$21,E14,$C$14:$C$21) 처럼 범위를 고정하세요.
Q. SUMIF 에서 '포함'하는 조건은 어떻게 쓰나요?
와일드카드 * 를 앞뒤에 붙입니다. =SUMIF(A25:A32,"*볼펜*",B25:B32) 는 이 PC 에서 이름 어디에든 볼펜이 있는 네 품목을 더해 165, "볼펜*"(볼펜으로 시작)은 82, "볼펜"(딱 그 이름)은 0 이었습니다.
Q. 이 문제가 컴활 2급 실기 기출인가요?
아닙니다. 대한상공회의소 문제가 아닌 연습용 자작 문제입니다. 시험의 계산작업 꼴로 문장 · 데이터를 새로 만들었고, 채점 기준 원문은 확인하지 못했습니다.
댓글 0
아직 댓글이 없습니다. 첫 댓글을 남겨 주세요.
댓글은 회원만 쓸 수 있습니다.
광고·욕설·비방·개인정보·불법 내용이 담긴 댓글은 숨기거나 지웁니다. 광고·홍보 글이나 혐오·비방 글을 올린 회원은 강제 탈퇴되고 쓴 글이 모두 지워집니다. 댓글은 닉네임과 함께 공개됩니다. 개인정보처리방침