콘텐츠로 건너뛰기
Home » 데이터 분석 » VLOOKUP/SUMIF/COUNTIF 엑셀 함수 사용법

VLOOKUP/SUMIF/COUNTIF 엑셀 함수 사용법

VLOOKUP/SUMIF/COUNTIF 함수 사용법
VLOOKUP/SUMIF/COUNTIF 함수 사용법

VLOOKUP 함수는 직장인이 알면 많은 도움이 되는 함수입니다. 그 이유는 VLOOKUP 함수를 통해 테이블의 특정 정보를 빠르게 검색, 동일한 테이블의 다른 열에서 관련 정보를 쉽게 검색할 수 있기 때문입니다.

SUMIF/ COUNTIF 함수 또한 기입 조건에 부합하는 셀들의 합계나 개수를 파악해주는 쉽고 간단하면서도 실무에 유용한 함수들입니다.

이번편에선 VLOOKUP/SUMIF/ COUNTIF 함수 별 사용법 및 주의사항, 그리고 수식에 사용 가능한 비교 연산자 종류까지 알아보겠습니다.우선 각 함수에 대해 설명하기 전 SUMIF/COUNTIF 함수에 사용되는 비교 연산자 종류를 간략히 알려드리겠습니다.


💡아래에서 관심 있는 주제를 클릭하여 해당 본문으로 바로 이동해보세요.

비교 연산자 종류

VLOOKUP 함수란?

VLOOKUP 인수

VLOOKUP 실전 예제

VLOOKUP 함수 사용 시 자주 겪는 오류


SUMIF 함수란?

SUMIF 인수

SUMIF 예시

SUMIF 실전 예제


COUNTIF 함수란?

COUNTIF 인수

COUNTIF 예시

COUNTIF 실전 예제

SUMIF/COUNTIF 함수 사용시 자주 겪는 오류


비교 연산자 종류

비교 연산자 종류입니다. SUMIF/COUNTIF 함수 사용 시 참고하면 많은 도움이 되는 연산자 입니다.

연산자설명예시
=~인=10
>~보다 큰>10
>=~보다 크거나 같은>=10
<~보다 작은<10
<=~보다 작거나 같은<=10
<>~가 아닌<>10, <>NASDAQ (NASDAQ이 아닌)
연산자 종류

VLOOKUP 함수란?

VLOOKUP 함수는 Vertical Lookup (수직으로 찾아보다)의 줄임말입니다. 하단 사진과 같이 참조 범위를 수직으로 내려가면서 값을 찾는다는 의미입니다.

Vertical Lookup

VLOOKUP 인수 

VLOOKUP은 총 4개의 인수를 사용하며 구문은 다음과 같습니다:

=VLOOKUP(lookup_valuetable_array, col_index_num[range_lookup (TRUE or FALSE)]

=VLOOKUP(찾을 값참조 범위,열 번호[일치 여부 (유사 값 or 일치 값)]

인수설명
lookup_value (찾을 값)참조 범위의 첫번째열 (맨 좌측)에서 검색할 값
table_array (참조 범위)값을 참조할 범위 (일반적으로 ‘F4’ 클릭을 통해 절대참조($) 입력)
col_index_num (열 번호)참조 범위 내 열 번호, 첫번째 열 (=1)
range_lookup (일치 여부 (유사 값 OR 일치 값))찾을 값의 일치 옵션. 기본 값은 유사 일치 (TRUE)이지만 실무에선 0 or FALSE 입력하여 ‘정확히 일치’ 옵션으로 사용함.
VLOOKUP 인수 

VLOOKUP 실전 예제

‘도서 코드’로 ‘도서 제목’ 찾기 

아래 예시와 같이 TBWA 03월 구매 도서 정보가 있다 가정하겠습니다. 

참조 범위 열: 1. 도서 코드, 2. 도서 제목, 3. 가격, 4. 저자, 5. 요청 수량

여기서 코드에 해당하는 도서 제목을 찾아보겠습니다. 

=VLOOKUP(B17,$A$5:$E$11,2,FALSE)

2는 열 번호, 즉 ‘도서 제목’의 열 번호 입니다. 

'도서 코드'로 '도서 제목' 찾기

정상적으로 '도서 제목'을 불러온 경우

정상적으로 ‘도서 제목’을 불러왔습니다. 

‘도서 코드’를 통해 ‘도서 제목’을 찾는 자세한 과정은 다음과 같습니다. 

  1. [A5:E11] 범위의 첫째 열에서 수직으로 내려가면서 ‘도서 코드’ ‘A0304’를 찾는다. 
  2. 같은 행의 두 번째 열에서 ‘도서 제목’인 ‘챗 GPT – 마침내 찾아온 특이점’을 찾는다. 
VLOOKUP 함수를 통해 값을 불러오는 과정

가격 불러오기 

가격을 가져오기 위해 [C23] 셀에 다음 수식을 입력합니다. 

‘도서 코드로 도서 제목 찾기’와 다른 점은 ‘열 번호’입니다. 가격은 참조 범위의 세 번째 열에 기입되어 있으므로 ‘3​’을 기입합니다. 

=VLOOKUP(B23,$A$5:$E$11,3​,FALSE) 

올바른 열 번호 기입

올바른 열 번호 기입으로 '가격'을 불러온 과정

도서 코드 A0304의 가격인 11,700원 을 정상적으로 불러왔습니다. 


다른 시트에서 가격 불러오기 

다른 시트에서 참조 범위를 불러와 VLOOKUP 함수를 사용할 수 있습니다. 

참조 범위를 불러오는 데엔 두 가지 방법이 있습니다. 

A. 다른 시트를 직접 입력 후 참조 범위를 불러올 수 있습니다. 

B. 혹은 다른 시트를 클릭 후 참조 범위를 드래그하여 불러올 수 있습니다. ​

다른 시트에서 참조 범위 불러오기

두 방법으로 불러올 수 있지만 완성되는 형식은 아래와 같이 동일합니다. 

=VLOOKUP(A2,Sheet1!$A$5:$E$11,3,FALSE) 

시트 명!참조범위‘ 

시트 명 뒤에 ‘!‘가 붙어야 정상적으로 불러올 수 있는 점 명심하세요. 


VLOOKUP 함수 사용 시 자주 겪는 오류

1. VLOOKUP 함수 사용 시  #N/A 오류 발생 

보통 아래와 같은 두 가지 문제 때문에 #N/A 오류가 발생합니다. 

A) 조회 값이 table_array 인수의 첫 번째 열에 없는 경우

B) 조회 열이 오름차순으로 정렬되지 않은 경우 (Vlookup_TRUE 함수)


->A) 조회 값이 table_array 인수의 첫 번째 열에 없는 경우

조회 값이 table_array 인수의 첫 번째 열에 없는 경우

조회 값 ‘마케팅 5.0’이 table_array 인수 A2:E11의 두번째 열(도서 제목)에 나타나기 때문에 #N/A 오류가 발생합니다. 

해결법: 

1. ‘도서 제목’이 첫번째 열에 오도록 ‘도서 코드’과 ‘도서 제목’의 위치를 바꿉니다.

2. 조회 테이블 내 위치와 상관 없이 열에서 값을 조회할 수 있는 함수인 INDEX/MATCH 함수를 사용합니다. 


->B) 조회 열이 오름차순으로 정렬되지 않은 경우 (Vlookup_TRUE 함수)

TRUE 함수 기입 시 수식을 잘 작성했는데도 오류가 난다면 조회 열이 오름차순 정렬 되어있는지 확인해야 합니다. 

해결법:

TRUE 함수는 구간이나 범위를 적용하는 함수 (유사 일치)이기에 참조 범위는 항상 오름차순으로 정렬 되어야 합니다. 

하단은 조회열 (가격대 별 비고)을 불러와 각 도서 별 비고를 채우는 예시 입니다. 왼쪽 사진은 정렬이 안된경우, 오른쪽은 정상적으로 오름차순 정렬이 된 경우 입니다. 

TRUE 함수 사용 시 조회 열을 오름차순 정렬 하면 정상적으로 값을 불러올 수 있습니다. 


2. 첫 번째 값은 정상적으로 나오는데 드래그 후 그다음 값들은 정상적으로 안나오는 경우 

보통 Table_array 오류 때문에 발생합니다. 

Table_array가 절대 참조로 범위가 고정되지않은 상태에서 드래그하면 범위가 한 칸씩 밀립니다. 

해결법:

각 범위 앞에 $를 넣거나 범위를 드래그 후 F4키를 눌러서 절대 참조하세요. 


3. #NAME? 오류 발생하는 경우

수식에 오타가 있는 경우 발생하는 오류입니다. 

VLOOKUP 함수 사용시  #NAME? 오류가 발생하는 경우
#NAME? 오류 원인틀린 예시옳은 예시
함수 철자 틀린 경우VLOOUPVLOOKUP
lookup_value (찾을 값)이 텍스트이지만 큰따옴표 (” “)를 안 넣은 경우A0307“A0307”
범위 설정 시 콜론 (:)을 안 넣은 경우A5,E11A5:E11
VLOOKUP 함수 사용시 #NAME? 오류가 발생하는 경우

SUMIF 함수란? 

​SUMIF 함수는 범위에서 하나의 조건을 만족하는 값의 합계를 구하는 함수인데요. 함수의 조건으로 연산자 및 와일드카드를 사용할 수 있습니다. SUMIF 함수는 데이터에서 특정 조건을 만족하는 범위의 숫자 합계를 구할 때 유용하게 사용됩니다. 


SUMIF 인수

SUMIF는 총 3개의 인수를 사용하며 구문은 다음과 같습니다:

=SUMIF(rangecriteria[sum_range])

=SUMIF(조건 범위조건, [합계 범위])

인수설명
range (조건 범위)조건을 적용할 범위
criteria (조건)합계를 구할 조건
[sum_range] (합계 범위)합계를 구할 범위 (생략 가능합니다)
SUMIF 인수

SUMIF 예시

= SUMIF ( {10, 20, 30, 4050}, “>=40” )

범위에서 ‘40‘ 이상인 값의 합계를 계산합니다.

결과값으로 40 + 50=90을 도출합니다.


= SUMIF ( { 채소채소,육류, 육류, 과일 }, “채소“, { 10, 15, 20, 35, 50 } )

조건 범위에서 ‘채소‘인 조건의 합계를 계산합니다.

결과값으로 = 10 + 15 = 25를 도출합니다.


SUMIF 실전 예제 

종목이 같은 회사의 시가총액 합계 구하기

아래 예시는 주식 상장된 회사별 종목과 시가총액을 나타내는 테이블 입니다. 

종목이 NASDAQ인 회사들의 시가총액 합계를 구해보겠습니다. 

=SUMIF(B4:B11,”NASDAQ”,C4:C11)

=SUMIF(조건 범위,”조건”,[합계 범위])

SUMIF 예제

종목이 NASDAQ인 회사들의 시가총액 합계가 12539.31로 정상적으로 출력되었습니다. 


특정액 이상인 회사 시가총액 합계 구하기

다음은 시가총액이 50 ($1억)이상인 회사들의 시가총액 합계를 구해보겠습니다.

=SUMIF(C4:C11,”>50″) 

=SUMIF(조건 범위,”조건”)

SUMIF 예제

시가총액이 50 ($1억)이상인 회사들의 합계가 12513.45로 정상적으로 출력되었습니다.

​보시다시피 합계범위가 지정되지 않아도 조건 범위 (시가총액)에서 조건 (>50)을 만족하는 값의 합계를 정상적으로 계산합니다.


COUNTIF 함수란? 

지정된 범위에서 ‘한 가지’ 조건을 만족하는 셀 개수를 계산하는 함수입니다. 


COUNTIF 인수

COUNTIF는 총 2개의 인수를 사용하며 구문은 다음과 같습니다: 

=SUMIF(rangecriteria)

​=SUMIF(조건 범위조건)

인수설명
range (조건 범위)조건을 적용할 범위
criteria (조건)개수를 셀 조건
COUNTIF 인수

COUNTIF 예시

= COUNTIF ( {10, 20, 30, 4050}, “>=40” )

범위에서 ‘40‘ 이상인 값의 개수를 계산합니다.

결과값으로 2를 도출합니다.

= COUNTIF ( { 채소채소, 육류, 육류, 과일 }, “채소“)

조건 범위에서 ‘채소‘인 셀의 개수를 계산합니다.

결과값으로 2를 도출합니다.


COUNTIF 실전 예제

같은 품목인 제품 개수 세기

식료품과 품목, 그리고 가격을 나타내는 테이블 입니다. 

품목이 채소인 제품들이 몇개 인지 구해보겠습니다

=COUNTIF(B3:B10,”채소”)

=COUNTIF(조건 범위,”조건”)

COUNTIF 예제

품목이 채소 (애호박, 당근, 양배추, 아스파라거스)의 개수를 4개로 정확히 산출했습니다. 


특정 가격 이상 제품 개수 구하기

가격이 1,200원 이상인 제품들이 몇개 인지 구해보겠습니다

=COUNTIF(C3:C10,”>=1200″)

=COUNTIF(조건 범위,”조건”)

COUNTIF 예제

1,200원 이상인 제품들의 개수를 5개로 정확히 불러왔습니다. 


SUMIF/COUNTIF 함수 사용시 자주 겪는 오류

1. SUMIF/ COUNTIF 함수에서 ‘숫자값’이나 ‘셀참조’가 아닌 텍스트/연산자 를 입력하는 경우 

-> 텍스트/연산자는 항상 ” ” (큰 따옴표)에 포함되어야 합니다. 

틀린 예시옳은 예시비고
= COUNTIF(C3:C10,>=1200)= COUNTIF(C3:C10,“>=1200”)조건문이 큰 따옴표에 포함되어야 합니다.
텍스트/연산자는 항상 ” ” (큰 따옴표)에 포함되어야 합니다. 

2. 닫힌 통합문서를 참조하는 경우 #VALUE! 오류 발생

SUMIF/ COUNTIF 함수에서 수식에 사용된 다른 통합문서 또한 실행이 되어야합니다. 

-> 해당 통합문서를 열고 F9를 눌러 수식을 새로고침 하세요. 


3. 조건 문자열이 255자 초과하는 경우 #VALUE! 오류 발생 

SUMIF/ COUNTIF 함수에서 255자를 초과하는 문자열을 일치시킬 경우 오류가 발생합니다. 

-> 가능하면 문자열을 줄이거나 텍스트 간 이어주는 & (앰퍼센드)를 텍스트 사이에 넣으세요. 

아래는 텍스트 사이 & (앰퍼센드)를 기입하는 예시입니다: 

BeforeAfter
=COUNTIF(B2:B12,”obfuscatequintilian”)​=COUNTIF(B2:B12,”obfuscate“&”quintilian”)
텍스트 사이 & (앰퍼센드)를 기입하는 예시 Before, After

FAQ

VLOOKUP 함수란?

VLOOKUP 함수는 Vertical Lookup (수직으로 찾아보다)의 줄임말입니다. 하단 사진과 같이 참조 범위를 수직으로 내려가면서 값을 찾는다는 의미입니다.

SUMIF 함수란? 

​SUMIF 함수는 범위에서 하나의 조건을 만족하는 값의 합계를 구하는 함수입니다. SUMIF 함수는 데이터에서 특정 조건을 만족하는 범위의 숫자 합계를 구할 때 유용하게 사용됩니다. 

COUNTIF 함수란? 

지정된 범위에서 ‘한 가지’ 조건을 만족하는 셀 개수를 계산하는 함수입니다. 


지금까지 3가지 함수들에 대해 알아보았는데요. 실무에 자주 쓰이지만 가끔씩 오류가 뜨며 헷갈리는 경우도 있으니 필요하실 때 본 포스트를 방문하시면 될 것 같습니다.

📌 이런 주제의 글은 어떠신가요? 😊

태그:

답글 남기기

이메일 주소는 공개되지 않습니다. 필수 필드는 *로 표시됩니다