← 엑셀 강의 목차
📗 엑셀 · 중급 2강올린 날 · 10분 읽기

엑셀 COUNTIF · SUMIF 함수 — 조건 쓰는 법 · 셀 참조 · 와일드카드(컴활 2급 실기 유형 연습 3문제)

조건에 맞는 칸만 세고(COUNTIF), 더하고(SUMIF), 평균 내는(AVERAGEIF) 함수입니다. 중급 1강에 이어 시험 문제 꼴로 새로 만든 연습 문제 3개(기본 · 응용 · 함정)를 이 PC 엑셀에서 직접 풀었습니다. 조건을 따옴표 안에 쓰는 법, 조건에 셀을 참조하는 법, 와일드카드(*), 범위에 $ 를 붙여야 하는 이유를 칸마다 나온 값으로 보여 줍니다.

이렇게 쟀습니다

문제 문장 · 이름 · 숫자는 모두 이 강을 위해 새로 만들었습니다(문제 번호는 중급 1강에서 이어 4~6). 실습 파일을 이 PC 엑셀(Microsoft 365)에서 열어 답 칸에 수식을 넣고(문제 5 는 아래로 채움) 칸에 보이는 값을 셀 주소별로 읽었습니다. 자주 틀리는 식 11개도 같은 칸에 넣어 결과를 읽었고, 기준 칸 E25 를 바꿨을 때 답이 따라 바뀌는지도 쟀습니다. 그림은 범위를 PDF 로 내보내 만든 것입니다.

ℹ️

대한상공회의소 문제가 아닌 연습용 자작 문제입니다. 함수 인수 창의 칸 이름 표기, 식을 받지 않을 때 뜨는 알림 창 문구, 컴활 실기의 채점 기준 원문은 이번에 확인하지 못했습니다(확인 안 됨).

영상으로 보기 — 중급 1강(문제 1~3)과 이 강(문제 4~6)을 한 편에 풀었습니다(코딩나우 채널, 11/19 20시 공개)www.youtube.com/watch?v=3ortg2OZtW4

문제 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명 이 있고 빨간 테두리가 있다.
문제 4 결과 — E3 4명(이 PC 엑셀, 범위를 PDF 로 내보낸 그림)
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강).

수식
F14fx
ABCEFG
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
1/4부서별 판매액 합계 · 평균을 F14:G16 에. 합계 F14 를 누르고
따라 하기 — 실습 파일과 같은 주소(장면이 저절로 넘어갑니다)
칸수식(아래로 채운 뒤)값(이 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 같은 꼴이 들어 있고 빨간 테두리가 있다.
수식 보기 — $ 범위는 그대로, 부서 칸만 E14 → E15 → E16

영업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 에 빨간 테두리가 있다.
$ 없이 채운 결과 — F16 만 455,000(정답 1,435,000)

$ 가 없으면 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" 으로 만듭니다. ② 이름 '어디에든' 볼펜이 있으면 앞뒤에 * 를 붙인 "*볼펜*" 을 씁니다(* = 글자 몇 개든).

수식
F25fx
ABEFG
23[표6] 문구 판매 수량
24품목판매 수량기준 수량기준 이상 품목 수볼펜 들어간 품목 합계
25젤 볼펜(검정)4850
26젤 볼펜(빨강)35
27볼펜 리필60
28형광펜72
29연필50
30수정 테이프18
31노트 A595
32볼펜 세트22
1/6① 판매 수량이 기준 수량(E25) 이상인 품목 수를 F25 에 — 기준은 E25 를 참조합니다
따라 하기 — 실습 파일과 같은 주소(장면이 저절로 넘어갑니다)
칸 · 식결과(이 PC 엑셀)왜
F25 =COUNTIF(B25:B32,">="&E25) 정답460 · 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 에 빨간 테두리가 있다.
문제 6 결과 — F25 4 · G25 165(이 PC 엑셀)
엑셀 표 [표6] 에 ">=E25" 로 쓴 COUNTIF 결과. F25 가 0 이다. F25 에 빨간 테두리가 있다.
">=E25" 로 쓴 결과 — 0

풀이 순서 한눈에

문제누르는 칸넣는 수식(그대로)그다음
4E3=COUNTIF(C3:C10,">=80")&"명"Enter
5F14=SUMIF($B$14:$B$21,E14,$C$14:$C$21)Enter → F14:F16 고르고 Ctrl+D
5G14=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. 1문제 4 — E3 이 4명인지 확인합니다.
  2. 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. 3문제 6 — F25 4 · G25 165 를 확인한 뒤 E25 를 60 으로 바꿔 F25 가 3 이 되는지 봅니다.
  4. 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

아직 댓글이 없습니다. 첫 댓글을 남겨 주세요.

댓글은 회원만 쓸 수 있습니다.

광고·욕설·비방·개인정보·불법 내용이 담긴 댓글은 숨기거나 지웁니다. 광고·홍보 글이나 혐오·비방 글을 올린 회원은 강제 탈퇴되고 쓴 글이 모두 지워집니다. 댓글은 닉네임과 함께 공개됩니다. 개인정보처리방침