구글 스프레드시트 - 초급편 (6) (SUMIF, SUMIFS 함수 이해하기)

구글 스프레드시트에서 수식 적용하기

- SUMIF와 SUMIFS

 

아무도 알려주지 않아서 실무에서 혼자 맨땅에 헤딩하면서 습득한 내용을 다룹니다.
(최적화된 방법보다는 그냥 제가 할 줄 아는 방법을 다룬 것이라 이 블로그에 있는 방법이 가장 효율적인 방법은 아닐 수 있습니다!)

Image from Pixabay

---

안녕하세요 데이빗입니다.

지난 포스팅에 이어서 계속해서 조건부 함수들에 대해 알아보도록 하겠습니다.

* 조건부 함수가 뭔지 기억나지 않는다면?! >> 구글 스프레드시트 - 초급편 (3)

 

이번 포스팅에서는 SUMIF 함수와 SUMIFS 함수에 대해 알아보도록 하겠습니다.

 

지난 포스팅에서 알아본 COUNTIF 함수와 COUNTIFS 함수가 설정한 조건에 맞는 셀(Cell)의 개수를 계수(count)하는 함수였다면, 이번 포스팅에서 알아볼 SUMIF 함수와 SUMIFS 함수는 설정한 조건에 맞는 셀(Cell)이 포함하고 있는 값들의 합을 구하는 함수입니다. 

지난 포스팅의 COUNTIF 함수와 COUNTIFS 함수를 연습해 봤다면 SUMIF와 SUMIFS 함수도 어렵지 않게 활용하실 수 있을 것입니다.

 

지난 포스팅과 마찬가지로 예시로 사용하실 수 있도록 실습 예제 스프레드시트 링크를 공유합니다.

구글 스프레드시트 예제 >> 링크 

다양한 사람들이 사용할 수 있기 때문에 해당 예제는 편집이 불가능하고 뷰어 기능만 열어두었습니다. 

해당 링크의 내용을 복사하여 각자 이용하시는 스프레드시트에 붙여 넣기 해서 직접 해보시면 됩니다.

---

SUMIF, SUMIFS 함수

SUMIF와 SUMIFS 함수 또한 이름에서 추측할 수 있다시피 특정한 조건을 만족하는 셀(Cell)들의 값을 합친 결과를 출력해주는 함수입니다. 조건이 하나라면 SUMIF 함수를 이용하면 되고, 조건이 두 개 이상이라면 SUMIFS 함수를 이용하면 됩니다.

(의미는 없지만 SUMIFS 함수를 하나의 조건만 가지고 이용할 수도 있습니다. 단, SUMIF와 SUMIFS 함수 사용 시 입력하는 값의 순서가 다르기 때문에 주의가 필요합니다.)

 

먼저 SUMIF에 대해 알아보도록 하겠습니다.

셀(Cell)에 '=SUMIF('를 입력하면 아래와 같이 설명이 나옵니다.

Image Captured by David Kim

COUNTIF와 거의 동일한데, 먼저 '범위' 부분에 조건문을 적용할 범위를 입력합니다.

그리고 '기준' 부분에 조건문을 입력합니다. 

마지막으로 '범위_합계' 부분에 합산할 범위를 입력합니다.

(위에도 나와 있듯이 '범위_합계' 부분은 선택사항입니다. 이 내용은 아래에서 조금 더 상세하게 다루도록 하겠습니다.)

위 두 가지, 혹은 세 가지가 입력되면 SUMIF 함수는 해당 '범위'의 값들을 하나씩 테스트하여 '기준'에 입력된 조건문에 부합하는지 확인하고, '범위_합계'가 별도로 입력된 경우, 위 '범위'에서 '기준'에 부합하는 셀(Cell)의 상대 주소를 기억하여, '범위_합계'에서 동일한 주소에 있는 값들을 합한 값을 출력해 줍니다.

위 설명이 매우 복잡한데요, 사실 직접 해보면 훨씬 간단합니다. (특히 로우 데이터(Raw Data)를 잘 모으면 더 간단합니다.)

 

아래 예시에서 확인해 보도록 하겠습니다.

이전 COUNTIF를 다룬 포스팅에서 가상의 직장인 A 씨는 1/4분기 동안 몇 번의 외식을 했는지 확인했었는데요, 이번에는 1/4분기 동안 외식비로 지출한 금액이 총얼마인지 알아보겠습니다.

예제 1 시트에서 D열(Column)은 지출 항목을 표시하고 있습니다. 발생한 지출이 어떤 목적으로 쓰여있는지 적혀있는 것이죠. 그리고 E열(Column)은 각 항목의 지출 금액을 표시하고 있습니다.

즉, 직장인 A 씨의 1/4분기 동안 지출한 총외식비를 구하려면, D열(Column)의 내용 중 '외식'이라고 입력된 셀(Cell)들을 확인하고, 해당 셀(Cell)들과 같은 행에 있는 E열(Column)의 값들을 더하면 될 것입니다.

아래와 같이 수식을 입력해 주었습니다.

=SUMIF($D:$D, "외식",$E:$E)

풀어서 설명하면, D열(Column) 전체를 대상 '범위'로 테스트를 하는데, 그 '기준'은 입력된 내용이 '외식'이라는 텍스트인지 여부를 확인합니다. 그리고 테스트 결과가 참(True)에 해당하는 셀(Cell)들의 상대적인 주소 값을 기억하여 E열(Column)에서 동일한 주소 값을 가진 셀(Cell)들의 값을 합하여 줍니다. '범위'와 '범위_합계' 모두 각각 단일 열(Cell) 전체를 범위로 잡았기 때문에 같은 행(Row)의 값을 가져오게 됩니다. (하나의 열(Column) 전체를 잡고자 할 때에는 D:D, E:E와 같이 ':'를 열 번호 혹은 행 번호에 입력해 줍니다.)

Image Captured by David Kim

위에서 '범위_합계' 부분이 선택사항이라고 말씀드렸는데, 그 이유는 상황에 따라서 '범위' 자체가 '범위_합계'가 될 수도 있기 때문입니다.

가령 전체 지출 중 '만원 미만의 지출 건'들의 합계를 구하고자 한다면 어떻게 할 수 있을까요?

위 예시와 동일한 방법으로 한다면 아래와 같이 입력하여 구할 수 있을 것입니다.

=SUMIF($E:$E, "<10000",$E:$E)

그렇지만 이렇게 '범위'와 '범위_합계'가 같을 경우, 맨 뒤에 있는 '범위_합계'를 생략해 줄 수 있습니다.

최종적으로 아래와 같이 입력하여 구할 수 있습니다.

=SUMIF($E:$E, "<10000")

Image Captured by David Kim

---

다음으로 SUMIFS 함수입니다.

마찬가지로 셀에 '=SUMIFS('를 입력하면 아래와 같이 설명이 나옵니다.

Image Captured by David Kim

SUMIF와 비슷한데 두 가지가 다릅니다.

첫 번째로 '범위_합계'가 맨 앞으로 나오도록 바뀌었고, 두 번째로 '범위'와 '기준'이 세트로 2개 세트 이상을 입력할 수 있게 되어 있습니다.

'범위_합계'가 맨 앞으로 나온 이유는 SUMIF와는 달리 '범위'와 '기준'이 뒤로 계속 붙을 수 있기 때문에 맨 뒤라는 개념을 정의하기가 어려워서 맨 앞으로 나온 것으로 보입니다. SUMIF와 순서가 다르니 꼭 기억하시기 바랍니다.

(그렇다면 헷갈리지 않게 SUMIF도 '범위_합계'를 맨 앞으로 빼면 되지 않나요?라고 생각할 수 있는데, SUMIF에서 '범위_합계'는 필수가 아닌 선택사항임을 기억하셔야 합니다.)

적용 방법 또한 어렵지 않은데, 먼저 값들을 합할 '범위_합계'를 정해주고, '기준_범위1'의 값들은 '기준1'에 입력된 조건문에 부합하는지 확인하고, '기준_범위2'의 값들은 '기준2'에 입력된 조건문에 부합하는지 확인하여, 이 두 가지 조건을 모두 만족하는 '범위_합계'의 값들을 더해 줍니다. (2개 세트 이상일 경우 '기준_범위3', '기준3', '기준_범위4', '기준4' 와 같이 계속 입력이 가능합니다.)

 

다시 한번 아래 예시에서 확인해 보도록 하겠습니다.

위에서 가상의 직장인 A 씨가 1/4분기 동안 외식비로 지출한 금액을 확인했다면, 이번에는 1/4분기 전체가 아닌, 1/4분기 주말에 외식비로 지출한 금액만 확인해 보도록 하겠습니다.

예제 1 시트에서 C열(Column)은 요일에 따라 평일과 주말을 표시하고 있습니다. 이를 활용해서 주말에 외식을 한 경우만 확인할 수 있습니다.

위와 같이 D열(Column)의 내용 중 '외식'이라고 입력되고, 동시에 C열(Column)의 내용 중 '주말'이라고 입력된 셀의 주소를 확인하여, 동일한 주소의 E열(Column)의 값들을 더해 줍니다.

아래와 같이 입력해 주었습니다.

=SUMIFS($E:$E,$D:$D,"외식", $C:$C,"주말")

Image Captured by David Kim

이렇게, 가상의 직장인 A 씨가 1/4분기 동안 주말에 외식비로 지출한 금액은 총 57,000원임을 확인할 수 있습니다.

 

이번 포스팅에서는 SUMIF와 SUMIFS 함수에 대해 알아보았습니다. 

다음 포스팅에서는 동일한 예제를 가지고 AVERAGEIF와 AVERAGEIFS 함수에 대해 알아보도록 하겠습니다.