엑셀 SUMIF 함수에 대한 이해를 돕기 위해 한 가지 예를 들어 보겠다.
- 시나리오
A라는 가구점이 있다고 하자.
도매를 통해서 매 주 월요일에 한 번씩 창고로 가구들을 들여오는데 가구들의 거래 내역을 도매처에서 엑셀파일로 보내준다.
가구의 종류는 침대, 책상, 의자, 식탁 등이 있다.
보내준 엑셀 파일에는 도매처가 판매한 가구들의 가격과 수량을 정리한 표가 있을테고 가구점에서는 이 표를 기존의 표에 붙여넣기로 연결해서 저장해 놓는다.
이렇게 이어 붙여진 표들은 한달(1 Month)을 기준으로 하나의 표로 완성이 된다. 그리고 매 달 말에 가구별 구입비용을 계산해 놓는다.
이 때 어떻게 하면 쉽게 매 달 가구의 종류별 구입한 비용을 계산할 수 있을까?
이 문제는 조건부 합계를 구하는 문제라고 볼 수 있다. 이 문제에서 합계는 "가구의 종류"에 따라서 달라지므로 조건은 "가구의 종류"가 된다.
조건부 합계는 SUMIF 함수를 사용하면 쉽게 해결된다.
엑셀 SUMIF 함수
SUMIF ( 조건 범위, 조건, 합계 범위 )
조건 범위는 조건을 검사할 셀 범위다.
바로 이해가 안될테니 예를 들어 조건 범위에 1부터 10까지의 수가 순서대로 적혀져 있다고 하자. 이 때 조건을 7 이상이라고 하면 조건에 만족하는 수는 7, 8, 9, 10이 된다.
아래 그림에서 조건에 만족하는 셀들이 바로 7, 8 9, 10이다.
그림은 조건 범위와 합계범위가 동일하게 10개의 셀을 가졌다. 이 두 범위는 마치 함수처럼 대응관계가 있는데 중요한 것은 순서대로 대응된다는 점이다. 조건 범위와 합계범위의 크기가 다르다면 순서대로 대응된 후 모자라는 부분은 무시된다.
SUMIF 함수의 동작은 다음과 같이 이뤄진다.
- 먼저 조건 범위에서 조건(위 그림에서는 >=7)에 만족하는 셀이 결정되면 (빨간 박스)
- 이에 대응되는 합계 범위의 셀들(파란 박스)의 합계가 구해진다.
- 추가 사항
- 조건은 위 예처럼 조건식이 될 수도 있고 값이 될 수도 있다. 조건이 값이라면 조건 범위에서 조건값에 일치하는 셀을 찾는다.
- 조건 범위와 합계범위가 동일한 크기가 아니어도 상관없다. 범위가 일치하지 않아도 순서대로 대응되고 대응되는 값이 없으면 무시된다.
SUMIF 함수 예제
처음 서문의 가구점의 문제를 다시 꺼내보자. 가구점은 5월 8일자로 다음과 같은 내역의 물건들을 구입했다고 한다.
그리고 다음은 5월 15일자로 구입한 물건들이다.
가구들의 도매가와 수량은 언제나 변화된다. 날짜별로 관리할 필요도 있으므로 가구점에서는 이 두 표를 단순히 이어붙여서 관리를 한다.
문제 1> 두 표를 이어붙인 후에 침대A의 의 총 구입비용을 구해보자.
SUMIF 함수를 설명하면서 제시한 그림과 똑같은 방식의 그림이므로 앞에서 이해했다면 이 그림도 무리없이 이해할 수 있을 것이다.
문제 2> 이번에는 침대A 뿐 아니라 다른 물품에 대해서도 조건부 합계를 구해서 별도의 표를 만들어 보자.
이 문제는 합계 범위와 조건 범위를 절대 주소로 해놓고 셀을 드래그해서 채워넣기를 하면 편리하게 표를 만들 수 있다.
=SUMIF($C$3:$C$18,H3,$F$3:$F$18)
범위에 $를 붙여서 절대주소로 만드는 이유
$를 왜 붙이는지 반드시 이해해야 한다. 엑셀의 많은 함수들이 이런 메커니즘으로 사용된다. 만약 $를 안붙인다면 셀을 드래그해서 자동채우기를 할 때 조건 범위와 합계 범위의 주소가 자동채우기를 방향으로 상대적으로 밀리기 때문이다.(아래 그림 참고)
SUMIF 함수의 두 번째 파라미터인 조건의 주소는 상대주소 그대로 나두었다. 왜냐면 자동채우기를 할 때마다 조건에 해당하는 셀의 위치(주소)도 자동채우기를 하는 방향으로 밀리도록 해야하기 때문이다.
(그런데 우연하게도 위 표는 상대주소로 범위를 잡아도 결과는 동일하다. 우연일 뿐이니 이 사실에 의미는 둘 필요는 없다.)
문제3> 날짜별 구입비용을 구해보자.
이 문제도 큰 무리없이 해결할 수 있을 것이다.