엑셀에서 특정한 단어가 있는 값의 합계 구하기 sumif

엑셀에서 특정한 단어가 있는 값의 합계 구하기 sumif

 

가계부를 작성하거나 회사에서 회계나 기타 서류를 작성할때 엑셀을 많이 사용할 것입니다.

엑셀을 사용하다 보면 함수라는 것을 알고 있으면 많이 편한데요.

가장 많이 사용되는 함수가 바로 sum이 아닌가 생각해요.

특정영역의 셀의 값들을 더해 주는 함수이죠.

SUM의 경우 워낙 자주 사용하는 수식이다 보니

상단의 메뉴중에 수식을 클릭하면 아래의 사진과 같이 별도로 아이콘으로 나와 줄 정도입니다.

 

 

오늘은 SUM 보다는 조금 더 어려운 특정한 단어가 들어가 있는 값들의 합을 구해 보겠습니다.

예를 들어 위 엑셀이 가계부라고 한다면 내가 구입한 각각의 과일값의 합이 궁금할 것입니다.

SUM으로 모두 더하려면 각 셀마다 다 지정을 해 줘야 하지만 sumif 라는 함수를 사용하면 간단하게 계산이 되어 집니다.

예제가 바로 오른쪽에 나와 있는데요.

이제 하나하나 예제를 보도록 하겠습니다.

 

sumif 예제

위 사진을 보면 오른쪽에서 사과, 배, 귤, 망고의 합계들을 모두 뽑아 보았습니다.

그럼 SUMIF 함수의 형태를 보도록 하겠습니다.

 

sumif 함수인수

위 사진을 보면 sumif의 함수인수를 알 수 있습니다.

우선은 첫번째 Range를 선택하고 그 다음 Criteria, 그리고 합계를 할 Sum_range 를 입력하면 되겠습니다.

즉 단어가 들어 있는 범위를 지정하고 그 단어가 들어 있는 범위 안에서 기준이 되는 단어를 선택한 다음 합을 구하라는 뜻입니다.

그리고 엑셀에서 함수를 사용하려면 "="로 시작을 하면 된답니다.

 

sumif 사과의 합계

위 예는 사과의 합을 구한 것입니다.

A 셀에서 사과를 찾아 B에 있는 값들을 더하라는 것이예요.

위에서 셀의 범위를 지정할 때는 그냥 입력하면 되지만 특정 단어를 입력할 때는 큰 따옴표로 싸준 것을 알 수 있습니다.

가끔씩 이 큰 따옴표를 깜빡 하시는 분들도 계시더라구요.

"사과" 대신에 A 열에서 사과가 있는 특정 셀을 지정해 줘도 해당 단어가 들어가 있는 합을 구할 수 있습니다.

 

사과의 합

바로 위와 같이 특정셀에 있는 단어가 있는 합을 구하는 것이죠.

F10에 보면 사과라고 되어 있습니다.

A2, A5 도 사과이기에 지정을 해도 되지만 A 열에서 사과가 항상 A2에 입력되라는 법이 없기에

위와 같이 특정 단어의 합을 구할 때는 별도로 구분하여 정리를 해 놓는 것이 편한 경우가 많습니다.

 

SUMIF를 사용하실때는 범위 지정이 중요한데요.

정확한 범위 지정이 정확한 값을 얻어 올 수 있습니다.

아래는 부정확한 범위 지정의 예입니다.

 

부정확한 범위 지정 1

앞에 범위를 보면 A1에서 A5까지 지정을 하였는데 값을 계산하는 것은 B열로 지정이 되었습니다.

그렇게 하니까 합이 맞아 떨어지네요.

그렇다고 계속 이렇게 지정을 하시면 안 되요.

운이 좋게 1열부터 지정이 되어 값이 맞아 떨어진 듯 한데요.

비슷하게 다른 방법으로 해 보았습니다.

 

부정확한 범위지정

A2에서 A5 까지를 범위로 정하고 B셀을 선택하였더니 5,000원이라는 완전 엉뚱한 값이 나왔습니다.

 

그럼 정확하게 범위 지정을 하여 계산을 해 보겠습니다.

A열 전체를 지정해 놓으면 합을 하는 범위도 A열 전체를 하고

특정 범위를 지정해 놓으면 합을 하는 범위도 그 특정 범위안에서 하도록 지정을 해 주세요.

 

 

자 그럼 확장형으로 가 보도록 하겠습니다.

예를 들어 사과로 시작하는 단어들의 합을 구하고자 하는 경우도 있으며

사과로 끝나는 단어들의 합을 구하고자 하는 경우도 있습니다.

또는 단어안에 사과라는 단어가 있는 것들의 합을 구하고자 하는 경우도 있을 것이네요.

이런 경우에는 * 을 사용하시면 됩니다.

 

그럼 예시를 보도록 하겠습니다.

 

사과로 시작하는 단어의 합

위는 사과로 시작하는 단어의 합을 구하여 보았습니다.

=SUMIF(A:A,"사과*",B:B)

가운데 보시면 "사과" 대신에 "사과*"이 들어가 있는 것을 보실 수 있습니다.

즉 사과로 시작하는 모든 것들의 합을 구하라는 것이죠.

 

아래 것들도 별표(*)의 위치를 잘 봐 주세요.

 

사과로 끝나는 단어의 합

사과로 끝나는 단어들의 합을 구한 것입니다.

=SUMIF(A:A,"*사과",B:B)

위에 보시듯이 별표가 사과 앞에 가 있는 것을 볼 수 있습니다.

 

사과 단어가 포함된 것

그럼 단어 안에 사과가 있는 것은 어떻게 할까요?

=SUMIF(A:A,"*사과*",B:B)

위와 같이 사과 앞뒤로 별표를 붙여 주시면 되겠습니다.

이런 식으로 별표를 주어 응용을 하실 수가 있겠습니다.

어려운 것 없으시죠.

 

참고로 범위를 지정할때 정확한 범위를 지정하라고 말씀드렸는데요.

아래는 별도의 예저를 하나 보도록 하겠습니다.

 

 

예를 들어 사과를 항목을 A 셀에 두고 달별로 B열, C열 이런식으로 확장을 했을 경우 모든 사과의 합을 구하고자 하는 경우도 있을 것입니다.

이런경우 간단하게 Sum_Range를 B1:C5 로 하여 범위를 지정 해 주면 될 것 같은데요.

=SUMIF(A2:A5,"사과",B1:C5)

이렇게 계산을 하니 또 엉뚱한 5,000원이 나와 버렸습니다. ㅠㅠ

즉 한 열의 값만 지정이 가능하다는 것을 알 수 있어요.

이런 경우는 D 열에 합을 구하고 =SUMIF(A:A,"사과",D:D) 이런식으로 구하는 것이 맞을 것 같습니다.

 

오늘은 엑셀 함수중에서 SUMIF 에 대하여 알아보았습니다.

잘 사용을 안 할 수도 있는 함수이지만 저 같은 경우에는 자주 사용하게 되더라구요.

자주 사용하시는 분들이야 사용법을 아시니 괜찮은데

가끔씩 사용을 할 때는 헷갈리는 경우가 있어 포스팅을 해 보았습니다.