조건에 맞는 값만 더하는 엑셀 함수는?

엑셀에서 제일 많이 사용하는 함수는 아마도 Sum, average, 그리고 IF일 것입니다. 그렇다보니, 엑셀을 만든 개발자들도 Sum함수와 If함수를 한번에 활용할 수 있도록 sumif함수를 만들어 놓았습니다. 이번 글에서는 회사에서 엑셀로 자료를 정리할 때 유용하게 사용할 수 있는 sumif함수에 대해서 알아보도록 하겠습니다. 

 

Sumif함수가 뭐지?

sumif함수는 조건에 맞는 값들만 더해주는 함수입니다. 이 함수는 아래와 같이 세 가지의 구성요소로 이루어져 있습니다.

=sumif(①조건범위, ②조건, ③더할범위)

sumif는 여러가지 입력된 자료들 중에서 조건에 맞는 것을 찾고, 그 조건에 맞는 값들의 특정 숫자들을 더해주는 함수입니다. 여기서 찾는 부분을 if가 담당하는 것이고, 찾은 값에 대해서 더하는 계산작업을 sum이 담당하는 것입니다. ①조건범위 조건을 적용할 셀 범위를 입력해주시면 됩니다. 즉 조건에 맞는지를 검사할 셀들이라고 생각하시면 됩니다.②조건 조건은 더할 셀의 조건을 지정하는 수, 식 또는 텍스트를 입력해주시면 됩니다.③더할범위 합을 구할 실제 셀의 범위를 설정해주시면 됩니다. 생략하면 ①조건범위의 값들이 더해집니다.

 

조건을 충족하는 값의 합계를 구해주는 sumif함수 알아보기

위와 같이 네개의 회사가 있고, 각 회사의 매출액, 그리고 당기순이익이 입력되어있다고 생각해 봅시다. 이제 이 예제를 통해서 다양한 조건에 맞는 합계를 구하는 함수, 즉 sumif함수를 배워보도록 하겠습니다.

 

조건을 직접 작성하여 조건에 맞는 값의 합계 구하기

예를 들어 매출액이 200원을 넘는 회사들의 당기순이익을 구하려면 어떻게 해야할까요? 아래 그림과 같이 수식을 입력하시면 됩니다.

먼저, ① Range에는 조건을 적용할 셀의 범위를 지정해주어야 합니다. 우리의 문제에서 조건은 '매출액이 200원을 넘는 것'입니다. 따라서 각 회사의 매출액이 입력되어있는 B2셀부터 B5셀을 드래그하여 범위로 지정해 줍니다. 그리고 ② Criteria에는 조건을 입력해 주어야 합니다. 따라서 ">=200"을 입력해줍니다. 조건은 큰 따옴표 안에 작성해야 한다는 점을 유의하세요. 또한 작거나 같아야 하므로 >=의 부등호를 사용해 주어야 합니다. 마지막으로 ③ sum_range는 합계를 구할 범위입니다. 우리는 매출액이 200을 넘는 회사의 '당기순이익의 합계'를 구해야 합니다. 따라서 당기순이익이 입력된 C2셀부터 C5셀을 지정해줍니다. 결과는 어떻게 되었을까요?

 

위 그림을 보시면, 매출액이 200을 넘는 회사는 B,D 두개 회사입니다. 그리고 이 둘의 당기순이익은 20, 15입니다. 따라서 이 둘의 당기순이익을 더하면 35가 계산됩니다. 이렇게 직접 조건을 손으로 입력해서 계산할 수도 있습니다.

 

조건을 셀주소로 지정하여 조건에 맞는 값의 합계 구하기

반면에 조건을 직접 입력하지 않고도 sumif함수를 사용할 수도 있습니다. 

위 그림을 참고해주시기 바랍니다. 이번에는 조건으로 인천지역에 있는 회사들의 당기순이익을 구해보려고 합니다. 그래서 ① Range(조건 범위)에는 지역이 입력되어있는 B2:B5셀을 지정했습니다. 그리고 ② Criteria(조건)은 지역이 입력되어있는 F2셀을 선택했습니다. ③ Sum_range(더할 범위)는 당기순이익을 더해야 하므로, D2:D5셀을 선택했습니다. 이처럼 조건을 특정 셀의 주소로 설정해서 조건에 맞는 값들의 합을 구할 수 있으니 참고하시기 바랍니다. 

 

위 그림에서 초록색 부분이 바로 인천에 위치한 회사들의 당기순이익을 나타낸 것입니다. 인천에 위치한 회사는 A,B사입니다. 그리고 그들의 당기순이익은 각각 5,20입니다. 따라서 그 합은 25입니다. sumif를 사용해서 계산한 결과도 25로 정확하게 계산됨을 확인할 수 있습니다.

 

조건을 셀 주소로 지정해주는 것이 어떤 이익이 있을까요? 위 움직이는 사진을 참고하시기 바랍니다. 조건이 입력된 F2셀에 서울을 입력했습니다. 그랬더니 합계가 인천지역 기업의 당기순이익 합계인 25에서 서울지역 기업의 당기순이익 합계인 30으로 변화하는 것을 확인할 수 있습니다. 이처럼, 조건을 셀 주소로 지정해 주면, 셀의 내용을 변경해 가면서 자동으로 합계를 구할 수 있다는 장점이 있습니다.

 

조건에 *를 이용하여 조건에 맞는 값의 합계 구하기

이번에는 *를 이용해서 조건 자체를 자유자재로 변형시켜서 sumif함수를 사용해보도록 하겠습니다. *(별표)는 와일드키라고도 하는데요, 이 녀석이 들어가는 자리는 수학에서의 x(미지수)처럼 아무 내용이나 들어가도 된다는 뜻입니다. 예를 들어서, "인*"이라고 컴퓨터에게 입력하면 컴퓨터는 '인'자로 시작하는 모든 글자를 찾게 됩니다. 반대로 "*천"이라고 컴퓨터에게 입력해주면 컴퓨터는 '천'자로 끝나는 모든 글자를 검색하게 되는 것입니다. 그럼 이 핫키를 이용해서 sumif를 활용해 볼까요?

 

회사가 늘어났네요? 이번에는 부천, 인제에 주소를 둔 회사들의 매출액과 당기순이익이 기록되어있는 것을 확인할 수 있습니다. 만약, '인'으로 시작하는 지역에 회사가 있고, 그 회사들의 당기순이익을 모두 더하고 싶다면 어떻게 해야 될까요? 물론 핫키를 사용해서 말입니다.

 

정답은 이렇습니다. ① Range(조건 범위)에는 우리가 조건을 걸어서 조건에 맞는지 확인할 자료들의 범위가 들어가야 합니다. 우리는 지역을 확인해야 하기 때문에, B2:B7를 조건에 맞는지 확인해야 할 범위로 설정했습니다. ② Criteria(조건)은 인자로 시작하는 모든 자료를 찾아야 하므로, "인*"으로 입력하였습니다. 컴퓨터에게 '이 조건은 글자로 인식해야 해'라고 알려줄 때는 쌍따옴표를 써야 한다는 점을 기억하시기 바랍니다. ③ Sum_range(더할 범위)는 당기순이익을 더해야 하므로 D2:D7을 지정해 주었습니다. 결과는 어떨까요?

 

인자로 시작하는 지역은 인천과 인제입니다. 그리고 인천에 위치한 회사의 당기순이익과 인제에 위치한 회사의 당기순이익을 모두 더한 값은 33임을 알 수 있습니다. 

 

반대로 '천'으로 끝나는 지명에 위치한 회사의 당기순이익을 알려면 어떻게 해야 할까요? 네, 조건에 "*천"으로 입력하면 되겠지요? 그 결과는 어떨까요?

 

지명이 천으로 끝나는 곳은 인천, 부천입니다. 그리고 이곳에 위치한 회사들의 당기순이익은 43입니다(5+20+18). 정확하게 계산된 것을 확인할 수 있습니다.

 

sumif함수는 엑셀에서 제일 많이 쓰이는 sum과 if함수를 하나로 묶은 하이브리드 함수라고 할 수 있습니다. 각각의 함수는 아주 기초적인 함수지만, 모르면 참 시간을 많이 잡아먹는 기능입니다. 이 두 기능을 한데 묶은 sumif는 더 유용하고 강력합니다. 조건에 맞는 값을 찾을 뿐 아니라, 그 값들을 더해주기 때문입니다. 이 글이 sumif함수를 이해하고 활용하는데 도움이되었으면 좋겠습니다. Value Creator.