안녕하세요 모닝수잔입니다 :)
엑셀에서 숫자들의 합을 구하는 SUM 함수와 개수를 구하는 COUNT 함수는 기초 함수이기 때문에 많은 분들이 알고 계실 것 같은데요, 오늘은 좀 더 응용해서 "조건에 맞는" 합을 구하는 SUMIF(S) 함수와 "조건에 맞는" 개수를 구하는 COUNTIF(S) 함수에 대해 알아보겠습니다.
먼저, 간단한 예제를 한번 만들어봤습니다. 왼쪽 표에는 고객별로 A, B, C양말을 주문한 수량이 기재되어 있습니다. 이 데이터를 이용해서 오른쪽표에 A, B, C양말 각각의 총 주문건수(G열)와 주문수량(H열)을 구하려고 합니다. 이때 어떤 함수를 사용할 수 있을까요?
맞습니다. 주문건수(G열)는 A양말을 구매한 고객의 수, B양말을 구매한 고객의 수, C양말을 구매한 고객의 수를 각각 카운트하는 COUNTIF 함수를, 주문수량(H열)는 A양말 주문수량의 합, B양말 주문수량의 합, C양말 주문수량의 합을 각각 구하는 SUMIF 함수를 사용할 수 있습니다.
COUNT & COUNTIF(S)
COUNTIF 함수에 대해 알아보기에 앞서, COUNT 함수를 잠깐 살펴보겠습니다. COUNT 함수는 숫자가 포함된 셀의 개수를 구하는 함수입니다. 엑셀에서 =COUNT를 치시면 아래 화면 빨간칸과 같이 "Counts the number of cells in a range that contain numbers"라고 기재되어 있는데요, 숫자를 포함하는 범위의 셀 수를 센다는 뜻입니다.
범위 내에서 숫자 데이터 개수 = COUNT(범위)의 형식으로 나타낼 수 있는데요, 아래 화면 분홍색 셀을 보시면 B열 전체(B:B)에 COUNT 함수를 적용하면 =COUNT(B:B) 그 값은 0이 됩니다.
왜 그럴까요?
맞습니다. "고객명"이 기재된 B열에는 숫자(Number)가 없기 때문입니다. 반면에 D열 전체(D:D)에 적용한 COUNT 함수 =COUNT(D:D) 값은 17로 "주문수량"이 기재된 D열은 모두 숫자를 포함한 셀이기 때문에 전체 행의 개수인 17의 값을 반환한 것이죠.
이제 본격적으로 COUNTIF 함수입니다. COUNTIF 함수는 =COUNTIF(범위, 조건)의 형식으로 나타낼 수 있습니다. 다만 그 범위에 숫자가 꼭 포함되지 않아도 된다는 점이 COUNT 함수와의 차이점입니다.
COUNTIF에서 "조건"은 크게 3분류로 나눌 수 있습니다.
1. 문자 조건 = COUNT(A:A, "<>양말")
▶ A열 전체 범위에서 "양말"이라는 문자를 포함하지 않는 조건을 만족하는 셀의 개수
2. 대표 문자 조건 = COUNTIF(A:A, "양*")
▶ A열 전체 범위에서 "양"으로 시작하는 모-든 문자라는 조건을 만족하는 셀의 개수
3. 숫자 조건 = COUNTIF(A:A, ">=100")
▶ A열 전체 범위에서 100 이상이라는 조건을 만족하는 셀의 개수
이 중에서도 저는 2번의 대표 문자 조건을 활용해서 주문건수(G열)를 구해보겠습니다. 먼저 A양말의 주문건수를 구해야하는데요, 상품명이 기재된 C열(C:C)에서 A양말이 기재된 셀의 개수를 구하면 A양말의 주문건수가 되겠죠?
"범위"는 C열 전체로 정해도 되지만 좀 더 세부적으로 데이터가 있는 C3셀에서 C19셀까지만 범위로 지정해봤습니다. 다음은 "조건"입니다. A양말 / B양말 / C양말에서 구분되는 문자는 바로 A / B / C입니다. 그래서 A* 이라는 조건을 통해 A로 시작하는 모든 문자라는 조건을 부여했습니다. C3:C19 범위에서 A로 시작하는 문자는 A양말밖에 없기 때문에 A양말만 카운트되겠죠?
=COUNTIF(C3:C19, "A*")
결과적으로 아래 화면 분홍색 셀처럼 10이라는 값을 얻게됩니다. 실제로 A양말 셀을 세어보시면 10개라는 것을 확인하실 수 있습니다.
※ COUNTIFS
COUNTIF 함수는 1개의 범위에 1개의 조건을 부여하는 함수이지만, S가 붙은 COUNTIFS 함수는 여러 범위에 여러 조건을 부여할 수 있는 함수입니다. 형식은 =COUNTIFS(범위1, 조건1, 범위2, 조건2, 범위3, 조건3 ...)입니다. 예를 들어, 주문수량이 20을 초과하는 A양말의 주문건수를 구한다고 한다면 COUNTIFS 함수를 사용할 수 있는데요, 기존 예제 =COUNTIF(C3:C19, "A*")에서 COUNTIF 함수를 COUNTIFS로 바꾸고 =COUNTIFS(C3:C19, "A*", D3:D19, ">20") 라고 주문수량(D열)이 20을 초과한다는 추가 범위2와 조건2를 넣어주면 10이 아닌 9라는 값을 얻게됩니다.
SUM & SUMIF(S)
SUM 함수는 범위에 있는 모든 숫자를 더하는 함수입니다. 엑셀에서 =SUM을 치시면 아래 화면 빨간칸과 같이 "Adds all the numbers in a range of cells"라고 기재되어 있는데요, 모든 숫자를 더하는 함수라는 것을 알려줍니다.
범위에 있는 모든 숫자의 합은 = SUM(범위)의 형식으로 나타낼 수 있습니다. 한가지 주의하실 점은, 아래 화면 분홍색 셀을 보시면 B열 전체(B:B)에 SUM 함수를 적용하면 =SUM(B:B) 그 값은 0이 됩니다. COUNT 함수와 마찬가지로 "고객명"이 기재된 B열에는 숫자(Number)가 없기 때문입니다. 반면에 "주문수량"이 기재된 D열의 경우 =SUM(D:D)의 값은 1001로 총 주문수량의 합을 구하실 수 있답니다.
SUMIF 함수는 조건에 맞는 합계를 구하는 함수로 =SUMIF(범위, 조건, 합계를 계산할 범위)의 형식으로 나타낼 수 있습니다. SUMIF 함수를 사용해서 A양말의 주문수량(H열)을 구해보겠습니다. 왼쪽표에서 상품명이 A양말인 경우 해당 주문수량을 모두 더하라는 함수식을 만들면 됩니다.
=SUMIF(C3:C19, F3, D3:D19)
먼저 "상품명이 A양말인 경우"라는 조건을 부여해주려면 상품명이 기재된 C3:C19를 범위로 넣어야 합니다. 조건은 F3셀 즉, A양말입니다. 여기까지는 C3:C19의 범위인 상품명에서 A양말을 찾으라는 뜻이죠.
다음으로는 무엇을 더해야할지 합계 범위를 지정해줘야 하는데요, 이때 바로 D3:D19 주문수량의 범위를 넣어주시면 됩니다. "C3:C19의 범위인 상품명에서 A양말을 찾으면 D3:D19 범위에 있는 주문수량을 더해라"라는 식이 완성됩니다. 결과적으로 아래 화면 분홍색 셀처럼 681이라는 결과값을 확인하실 수 있습니다.
※ SUMIFS
SUMIFS 함수는 하나가 아닌 그 이상의 조건을 만족하는 합을 구할 때 사용하는 함수입니다. =SUMIFS(합계를 계산할 범위, 범위1, 조건1, 범위2, 조건2, 범위3, 조건3 ...) 형식으로 나타낼 수 있는데요, SUMIF 함수에 S가 붙을 뿐만 아니라 "합계를 계산할 범위"의 위치가 맨 뒤에서 ▶ 맨 앞으로 변경되었다는 점을 유의해주셔야 합니다.
예를 들어, 개별 주문수량이 20을 초과하는 A양말의 총 주문수량을 구한다고 한다면 기존 예제 =SUMIF(C3:C19, F3, D3:D19)에서 SUMIF 함수를 SUMIFS로 바꾸고 "합계를 계산할 범위"인 D3:D19를 맨 앞으로 위치를 변경해준 뒤, =SUMIFS(D3:D19, C3:C19, "A*", D3:D19, ">20") 라고 개별 주문수량(D열)이 20을 초과한다는 추가 범위2와 조건2를 넣어주면 681이 아닌 669라는 값이 나온답니다.
오늘은 엑셀에서 가장 기초적인 COUNT함수와 SUM 함수에 대해 짧게 알아보고, 그 응용편으로 COUNTIF 함수와 SUMIF 함수까지 알아봤습니다. 실제로 업무에서 데이터를 많이 다루시는 분들이라면 VLOOKUP, IFERROR 함수 못지 않게 많이 활용하실 수 있는 함수랍니다.
그럼 오늘도 즐거운 하루 되세요 :)
