엑셀 함수, 합계·조건 집계·조회와 참조·오류 해결

합계·조건 집계·조회 중 무엇을 하려는지 정하면 함수가 좁혀진다

함수는 Excel에 들어 있는 계산 도구이고, 수식은 그 도구와 셀 주소·연산자를 조합한 계산식이다. =D2*E2는 곱셈 수식, =SUM(G2:G7)은 함수를 사용한 수식이다. 단순한 덧셈·뺄셈·곱셈·나눗셈에는 각각 +·-·*·/를 쓰면 된다. 예를 들어 =G2/D2는 금액을 수량으로 나눈 값이다.

전체 함수의 이름·분류가 필요하면 Microsoft 함수 목록에서 찾으면 된다. 처음부터 수백 개를 외우기보다 지금 필요한 결과 한 칸을 만들고, 다음 계산으로 이어 가는 편이 이해하기 쉽다.

수식은 결과 셀에 입력하고, 계산할 원본 표는 그대로 둔다

아래는 설명을 위해 만든 가상 주문표다. 새 시트의 A1:F7에 머리글과 데이터를 넣고, G1에는 금액을 쓴다. 날짜는 날짜 값으로, 수량·단가는 숫자로 입력한다. 아래 표의 ‘행’ 열은 Excel의 행 번호를 나타내므로 데이터로 붙여 넣지 않는다.

1. G2를 선택해 =D2*E2를 입력하고 Enter를 누른다. 결과는 20000이다.
2. G2를 복사해 G3:G7에 붙여 넣거나, G2 오른쪽 아래 채우기 핸들을 G7까지 끈다. 금액은 차례로 20000·30000·90000·20000·50000·10000이 된다.
3. 원본 표 밖인 I2에 =SUM(G2:G7)을 입력한다. 전체 주문금액은 220000원이다. 주문일과 상태를 가리지 않은 합계다.

G2:G7의 콜론은 G2부터 G7까지 이어진 범위, 쉼표는 함수에 넣는 값의 구분자다. 문자 조건은 "영업"처럼 큰따옴표로 감싸고 숫자 10000과 셀 주소 D2는 그대로 쓴다. 함수 구문에 보이는 대괄호는 생략 가능한 인수라는 뜻이며, 보통 수식에 직접 입력하지 않는다. 단, 뒤에서 설명할 표의 주문[금액]에서는 대괄호 자체가 문법이다.

비교 연산자 >와 <는 초과와 미만, >=와 <=는 이상과 이하, <>는 ‘같지 않다’는 뜻이다. =F2<>"완료"는 F2가 완료 이외의 값이면 TRUE를 반환한다. 함수 이름이 떠오르지 않으면 수식 → 함수 삽입 또는 수식 입력줄 옆 fx를 누른다. Microsoft의 수식 입력 안내처럼 =SUM(을 입력해도 인수 힌트가 나온다. 지역 설정이 세미콜론을 구분자로 쓰는 환경에서는 예제의 인수 구분 쉼표를 세미콜론으로 바꾼다.

행마다 바뀌는 주소는 D2, 공통 기준은 $L$2처럼 고정한다

=D2*E2를 한 행 아래로 복사하면 =D3*E3이 된다. 주문 행을 따라 움직이는 상대 참조다. 반면 모든 주문에 같은 비율을 곱하려면 비율을 적은 주소는 움직이지 않게 한다.

예를 들어 L1에 ‘가정 수수료율’, L2에 3%를 적는다. H2의 수식을 =G2*$L$2로 만들고 아래로 복사하면 H3은 =G3*$L$2가 된다. 주문금액만 바뀌고 비율은 L2를 계속 읽는다. 이 예제의 3%는 설명용 가정이며 실제 수수료율이 아니다.

Microsoft의 참조 설명에서처럼 $는 바로 뒤의 열 또는 행을 고정한다. Windows Excel에서는 수식 편집 중 주소를 선택하고 F4를 눌러 참조 형태를 바꾼다. 복사한 수식만 틀린다면 첫 번째 셀과 두 번째 셀의 수식 입력줄을 비교하면 이동한 주소가 보인다.

SUM은 합계, COUNT는 숫자 개수, COUNTA는 입력된 셀을 센다

예제의 G2:G7에 금액을 만든 뒤, 아래 수식을 표 밖의 빈 셀에 하나씩 넣는다. 범위 끝인 7을 늘리지 않으면 나중에 8행에 입력한 주문은 집계에 들어가지 않는다.

AVERAGE는 참조 범위의 빈 셀·문자를 제외하고 숫자 0은 포함한다. 미입력과 실제 0원을 같은 값으로 처리하면 평균의 뜻이 달라진다. COUNT는 숫자 셀을 세므로 상품코드 같은 문자 목록에는 COUNTA를 쓴다. COUNTA는 수식이 반환한 빈 문자열 ""도 센다.

소수 자릿수 표시를 줄이면 화면만 반올림되고 계산값은 남는다. 계산에 쓸 값 자체를 바꾸려면 ROUND를 쓴다. ROUND(값,0)은 정수, ROUND(값,2)는 소수 둘째 자리, ROUND(값,-2)는 백 단위 반올림이다. 금액의 행별 반올림과 합계 후 반올림은 결과가 다를 수 있으므로 정산 규칙에 맞춰 계산 위치를 정한다.

조건에 맞는 금액은 SUMIFS, 건수는 COUNTIFS, 표시는 IF다

‘영업 부서’와 ‘완료 상태’는 어떤 행을 계산할지 정하는 조건이다. SUMIFS는 해당 행의 금액을 더하고, COUNTIFS는 해당 행을 센다. 두 함수에 조건을 여러 개 적으면 같은 행이 모든 조건을 만족할 때 계산한다.

SUMIF는 조건 범위부터, SUMIFS는 합계 범위부터 쓴다. Microsoft도 이 인수 순서 차이를 흔한 실수로 짚는다. 조건이 하나여도 =SUMIFS(G2:G7,B2:B7,"영업")으로 쓰면 140000원이다. 조건을 더 붙일 예정이라면 처음부터 SUMIFS를 써도 된다.

11월 영업 완료 금액만 더하려면 날짜 조건 두 개를 붙인다

=SUMIFS(G2:G7,B2:B7,"영업",F2:F7,"완료",A2:A7,">="&DATE(2026,11,1),A2:A7,"<"&DATE(2026,12,1))

결과는 40000원이다. 전체 영업 완료 50000원에서 12월 1일 주문 10000원이 빠진다. 월별 조건을 ‘11월 1일 이상, 12월 1일 미만’으로 쓰면 11월 30일에 시각까지 기록된 주문도 포함된다. Exceljet의 날짜 구간 예제처럼 날짜 열에는 실제 날짜 값이 들어 있어야 비교가 된다.

L4에 기준 금액 30000을 적었다면 조건은 ">="&L4처럼 쓴다. ">=L4"라고 적으면 L4 주소까지 문자로 읽힌다. 오빠두엑셀의 SUMIFS 예제도 이 연결 방식과 합계·조건 범위의 크기를 맞추는 점을 설명한다. 이 표에서는 모든 범위를 2행부터 7행까지 맞춘다.

IF는 집계 대신 각 행의 상태를 판단한다

H2를 =IF(F2="완료","정산","보류")로 바꾸면 ‘정산’이 표시된다. 여러 조건을 모두 요구하려면 =IF(AND(B2="영업",F2="완료"),"대상","제외")로 쓴다. AND 대신 OR를 쓰면 두 조건 중 하나만 맞아도 ‘대상’이다. H열에 앞 절의 수수료 예제를 남겨 뒀다면 다른 빈 열에 입력한다.

IF의 순서는 조건·참일 때 값·거짓일 때 값이다. 계산 결과가 숫자여야 한다면 =IF(F2="완료",G2,0)처럼 0의 따옴표를 뺀다. Ablebits의 2026년 1월 질의응답에는 IF로 반환한 금액에 따옴표를 넣어 합계가 안 되던 사용자가 따옴표를 뺀 뒤 해결했다고 답한 사례가 있다. 문자를 보여 줄 셀과 숫자를 계산할 셀을 나누면 이런 문제가 줄어든다.

상품코드로 한 값을 찾을 때는 XLOOKUP, 여러 주문은 FILTER를 쓴다

주문표와 별도로 J1:K4에 상품 목록을 만든다. J1은 ‘상품코드’, K1은 ‘상품명’, J2:K4에는 순서대로 P001·노트, P002·키보드, P003·펜을 넣는다. C열의 코드를 상품명으로 보여 주려면 H2의 수식을 다음 식으로 바꾸고 아래로 복사한다. 앞 절의 H열 계산을 남기려면 비어 있는 다른 열에 입력한다.

=XLOOKUP(C2,$J$2:$J$4,$K$2:$K$4,"코드 없음")

첫 결과는 ‘노트’다. C2의 값을 J2:J4에서 찾아 같은 위치의 K열 값을 돌려준다. 찾는 범위와 반환 범위는 고정했으므로 아래로 복사해도 움직이지 않는다. XLOOKUP은 기본값이 정확히 일치하는 검색이고, 일치하는 코드가 없으면 여기서는 ‘코드 없음’을 표시한다.

Excel 2016·2019와 공유할 파일은 VLOOKUP이나 INDEX·MATCH로 쓴다

=VLOOKUP(C2,$J$2:$K$4,2,FALSE)
=INDEX($K$2:$K$4,MATCH(C2,$J$2:$J$4,0))

두 식 모두 P001에 ‘노트’를 반환한다. VLOOKUP의 2는 J:K 범위에서 두 번째 열인 K열을 뜻한다. 끝의 FALSE는 정확히 같은 코드만 찾는 옵션이다. 생략하면 근사 검색이 기본이므로 상품코드 조회에서는 명시하는 편이 낫다. VLOOKUP 공식 문서의 구조대로 찾는 코드는 범위 맨 왼쪽 열에 둔다. 반환 열이 코드 왼쪽에 있으면 INDEX·MATCH를 쓰면 된다.

상품 목록에 같은 코드가 두 번 있으면 기본 XLOOKUP은 먼저 일치한 값을 반환한다. ‘최신 단가’나 ‘마지막 거래’를 찾는 일에는 날짜·검색 방향 같은 조건이 더 필요하다. 이런 표를 옮길 때는 XLOOKUP과 VLOOKUP의 중복·근사 일치 차이를 이어 읽으면 된다.

완료 주문을 전부 뽑으려면 한 값 조회와 분리한다

=FILTER(A2:G7,F2:F7="완료","해당 없음")

원본 표와 겹치지 않는 A10에 입력하면 완료 주문 네 행이 A10:G13에 펼쳐진다. 이 영역은 비워 둔다. FILTER는 조건에 맞는 배열을 반환하고 결과 크기만큼 여러 셀을 사용한다. 한 상품의 이름만 가져오는 XLOOKUP과 달리 여러 주문을 그대로 보여 줄 때 맞는다.

같은 동적 배열 계열인 =UNIQUE(B2:B7)은 중복 없는 부서 목록을, =SORT(UNIQUE(B2:B7))은 정렬한 부서 목록을 만든다. Excel 2016·2019에서는 데이터 탭의 필터로 해당 행을 보고, 별도 목록이 필요하면 고급 필터나 복사본에서 중복 제거를 사용한다. 원본 목록을 보존하면서 계산 결과만 뽑을지, 데이터를 직접 바꿀지에 따라 선택한다.

문자는 필요한 부분을 꺼내고, 날짜는 숫자 값으로 계산한다

상품코드에서 맨 앞 글자를 꺼내려면 =LEFT(C2,1)을 쓴다. P001에서는 ‘P’가 나온다. =RIGHT(C2,3)은 ‘001’, =MID(C2,2,2)는 두 번째 글자부터 두 글자인 ‘00’을 반환한다. 조회가 안 되는 코드에 앞뒤 일반 공백이 있으면 =TRIM(C2)로 정리한다.

웹에서 가져온 코드에는 일반 공백과 다른 공백이 섞이기도 한다. TRIM은 문자 코드 160인 줄 바꿈 없는 공백을 단독으로 지우지 못한다. 그 경우에는 별도 열에 =TRIM(SUBSTITUTE(C2,CHAR(160)," "))을 써서 일반 공백으로 바꾼 뒤 정리한다. 코드의 앞자리 0이 의미가 있다면 숫자로 변환하지 말고 원본·상품표 양쪽을 같은 문자 형식으로 맞춘다.

NETWORKDAYS의 휴일 인수에 별도로 날짜 목록을 넣어야 해당 공휴일도 빠진다. 한국 공휴일이 자동으로 들어가는 함수는 아니다. 오늘을 기준으로 경과일을 계속 바꾸려면 =TODAY()-A2를 쓰고, 월말 보고서를 보관한다면 고정된 기준일 셀을 참조한다.

TEXT는 숫자·날짜를 서식이 있는 문자로 바꾼다. 안내문에는 편리하지만, 다음 합계·날짜 비교에는 원본 G열·A열을 사용한다. 계산값이 필요하고 표시만 바꾸려는 상황이면 TEXT 대신 셀 서식을 바꾼다.

수식이 안 되면 오류 이름과 표시 증상부터 나눈다

모든 오류를 IFERROR로 가리면 잘못된 주소까지 빈칸으로 사라진다. 우선 원래 수식의 오류를 읽고 원인을 고친 뒤, 찾는 값이 없는 것처럼 업무에서 예상되는 경우만 표시 방식을 정한다. IFERROR의 기본형은 =IFERROR(수식,오류일_때_표시할_값)이다. 예를 들어 =IFERROR(G2/D2,"계산 확인")은 정상일 때 나눗셈 결과를, 오류일 때 ‘계산 확인’을 보여 준다.

수식 문자열이 그대로 보이거나 값이 갱신되지 않을 때

시트 전체에 수식이 보이면 수식 → 수식 표시가 켜졌는지 본다. 특정 셀만 =SUM(...)으로 남는다면 셀 서식을 일반으로 바꾸고 수식 맨 앞의 작은따옴표를 없앤 뒤 F2 → Enter로 다시 입력한다. 원본을 바꿨는데 이전 결과가 남으면 수식 → 계산 옵션 → 자동으로 설정한다. Ablebits의 수식 문제 예제도 이 세 증상을 분리해 해결한다.

오류는 없는데 합계가 0이거나 일부 금액이 빠질 때

수량·단가 열의 숫자 셀을 골라 =ISNUMBER(D2)처럼 검사한다. FALSE라면 숫자 모양의 문자일 가능성이 있다. Excel의 녹색 삼각형 경고에 숫자로 변환이 있으면 이를 사용한다. 별도 열의 =VALUE(D2)로 바꿀 수도 있다. 상품코드·전화번호처럼 앞자리 0을 보존할 값에는 이 변환을 적용하지 않는다.

조건 집계는 범위와 조건도 함께 본다. "완료"는 같은 값만, "*완료*"는 그 단어가 포함된 값까지 찾으므로 ‘미완료’도 걸린다. Contextures의 COUNTIF 예제처럼 정확히 같은 항목을 셀지 일부 문자를 포함한 항목을 셀지에 따라 조건이 달라진다. 상태 열은 정확히 일치를 쓰는 편이 안전하다.

화면에서 행을 숨겼는데 합계가 그대로라면 함수 선택의 문제다. SUM은 필터로 가린 행도 더한다. SUBTOTAL로 =SUBTOTAL(109,G2:G7)을 쓰면 필터에서 빠진 행과 수동으로 숨긴 행을 제외한다. 109와 9의 차이, 필터를 다시 바꿨을 때의 동작은 엑셀에서 보이는 행만 더하는 방법에서 이어 다룬다.

매달 늘어나는 표는 범위를 확장하고, 공유 파일은 상대방 버전에 맞춘다

이 글의 수식은 설명을 위해 2~7행으로 고정했다. 실제로 주문이 계속 쌓이면 A1:G7 선택 → 삽입 → 표로 바꾸고 머리글을 포함한다. 표 디자인 탭에서 표 이름을 ‘주문’으로 정한 뒤, 표 밖의 합계를 =SUM(주문[금액])으로 쓴다.

Excel 표의 구조적 참조는 표에 행을 추가할 때 범위를 따라 늘린다. 영업 완료 금액도 =SUMIFS(주문[금액],주문[부서],"영업",주문[상태],"완료")로 쓸 수 있다. 새 행이 실제 표에 포함됐는지와 금액 열의 계산이 채워졌는지는 새 주문 한 건을 넣었을 때 합계가 그 금액만큼 늘어나는지 보면 된다.

함수의 이용 가능 여부는 Microsoft의 버전 표기를 기준으로 삼았다. 특히 XLOOKUP 도움말의 적용 대상에 2016·2019가 함께 보이더라도, 본문은 두 버전에서 사용할 수 없다고 명시한다. Microsoft의 Excel 도움말에서도 두 버전의 지원 종료를 안내한다. 설치 버전은 Windows 데스크톱의 파일 → 계정 → Excel 정보에서 볼 수 있다.

같은 조건의 합계를 정해진 보고서 칸에 채우려면 SUMIFS를 유지하면 된다. 부서·상품을 수시로 바꿔 보며 전체 집계표를 만들려면 피벗테이블이 편하다. 두 방식을 고르는 구체적인 기준은 월별 보고서에서 피벗테이블과 SUMIFS를 고르는 방법에 있다. 내 표에 적용할 때는 먼저 금액 열과 조건 열의 주소를 바꾸고, 조건에 맞는 주문 두세 건을 직접 더해 수식 결과와 맞춰 본다. 그다음 범위를 Excel 표로 바꾸면 새 주문까지 이어서 집계할 수 있다.