엑셀 sumif 함수 사용방법
엑셀의 활용에 있어서 절대적인 비중을 차지하는 것이 sum 함수라는 것에 대해서는 이전의 포스팅을 통해서 소개해드렸습니다. 우리는 알게 모르게 일상 생활의 많은 영역에서 덧셈을 이용하고 있고, 업무의 영역에서는 훨씬 더 그 비중이 커진다는 것도 알려드렸었죠. 그리고 결정적으로 엑셀에서는 덧셈 함수인 sum만 제대로 활용할 수 있어도 엑셀을 아주 잘 이용할 수 있게 된다는 점도요. 지난 sum 함수에 대한 포스팅을 보고 싶으시면 아래 링크된 글을 확인하시면 되겠습니다.
엑셀 덧셈 수식 sum 함수 활용하기
엑셀 덧셈 더하기 방법 알아보기 엑셀은 사무 자동화와 관련하여 가장 핵심적인 프로그램이 아닌가 싶습니다. 엑셀의 적당한 기능을 활용할 수 있느냐 여부에 따라서 작업의 능률과 속도는 비
assistmanual.tistory.com
이번 포스팅에서는 sum 함수를 활용하여 조건이 있는 덧셈을 할 수 있도록 해주는 sumif 함수에 대해서 살펴볼 것입니다. 수학의 여러 공식이 그렇듯 조건이 들어간다는 건 난이도를 확 올려주는 것 같은데요. 마찬가지로 sumif도 덧셈인 sum 함수와 조건 함수인 if가 결합되어 활용되는 함수로 활용난이도가 올라갑니다. 하지만 매우 기초적인 것들이기 때문에 몇 번만 연습을 해보면 실전에서도 유용하게 활용할 수 있는 간단한 함수입니다. 천천히 쉽게 설명해 드릴테니 잘 따라와 주시기 바랍니다.
1. 조건이 뭐야?
엑셀에서 뿐만 아니라 모든 작업에는 조건이 붙기 마련입니다. 아무리 단순 업무라고 해도 최소한의 조건은 다 있습니다. 가령 "토마토가 몇개야?", "스티커 작업은 100개만 해", "수리 작업을 3시까지 해"와 같은 개수, 시간제한 등이 모두 조건이라는 것이죠. 따라서 조건을 집어넣은 것은 단순 연산보다 한 발 더 복잡한 업무를 할 수 있게 해줍니다. 엑셀에서도 마찬가지입니다.
단순히 셀에 들어있는 값만 더해주는 sum에서 '어떤 셀'에 있는 값을 더해주냐는 조건을 하나 붙인 것만으로 훨씬 고도화된 작업을 하게 됩니다. if는 조건을 제시하는 함수입니다. "~한 것만"이라는 조건을 더해주는 것이지요. 따라서 덧셈인 sum에 if를 결합한 함수가 sumif입니다. 따라서 "~한 것만 더해라"라는 명령어로 활용될 수 있습니다. 이를 통해 복잡한 업무를 한층 더 쉽게 만들어줍니다.
2. sumif 함수 수식
sumif의 수식도 sum이나 다른 함수와 다르지 않게 수식을 입력합니다. 셀의 내용을 기입하는 칸에 아래와 같이 입력하면 sumif 함수를 사용할 수 있게 됩니다.
=sumif(조건범위,조건,찾는범위)
'='기호를 통해서 함수식을 시작한다고 표기하고 함수인 sumif를 적어주면 됩니다. 그러면 엑셀이 인식을 하고 차례대로 조건범위, 조건, 찾는범위를 설정하라고 안내를 합니다. 활용 예제를 통해서 더 정확히 알아보도록 하겠습니다.
3. sumif 활용 예제
아래와 같이 식료품 구매 일지가 있습니다. 반복적으로 비슷한 항목을 구입을 했는데, 구매일지에는 총합은 없이 그날 그날의 금액만 나열 되어 있습니다. 이 경우 결산을 할 때 각 식료품 종류별로 얼마나 비용이 지출되었는지 한 눈에 확인하기가 어렵습니다. 따라서 sumif 함수를 통해서 각 식료품별 사용금액을 산출할 수 있습니다. 아래 붉은사각형 안에 있는 총계를 채워나갈 겁니다.
노가다를 한다고 하면 '표고버섯'의 값을 구하기 위해 표고버섯의 구입금액 셀마다 선택해서 sum함수로 더해주면 됩니다. 데이터 값이 많지 않다면 충분히 편하게 사용할 수 있는 방법입니다. 하지만 데이터값이 많다면, 그리고 항목의 종류가 많다면 매우 고단한 작업이 될 뿐 아니라 실수가 발생하기 쉽습니다. 따라서 이럴 때는 sumif 함수를 활용하는 것이 좋고 안전합니다.
우선 가장 상단에 있는 표고버섯 구매금액의 합을 sumif 함수를 통해 산출해보겠습니다.
① 함수입력
총계를 구하는 표고버섯 항목 우측 셀에 =sumif를 입력하고, 괄호를 열어줍니다.
② 조건범위 입력하기
이 수식에서 조건은 '표고버섯'입니다. 표고버섯 항목의 값을 모두 찾아내어 더해주는 것이 목적이기 때문입니다. 첫 번째 입력정보인 조건범위에서는 표고버섯이라는 종류 항목이 포함된 열을 지정해 줍니다. A2부터 A20까지가 각 식료품의 항목을 표시한 셀이므로 A2:A20을 지정해줍니다.
범위를 지정해줄 때는 직접 입력을 해줘도 되고, 마우스 드래그해서 설정해도 됩니다. 그리고 쉼표(,)를 넣어서 조건범위 입력은 마칩니다.
③ 조건 입력하기
조건범위를 입력했으면 조건을 지정해주어야 합니다. 구하려는 조건은 '표고버섯'입니다. 따라서 표고버섯이라고 표기되어 있는 셀(G4)를 지정해줍니다. 직접 입력하거나 마우스로 선택을 해주면 됩니다. 그리고 쉼표로 마무리.
③ 찾는범위 입력하기
이제 마지막 단계인 찾는범위를 입력해줍니다. 해당 조건을 입력했으므로 그 조건에 해당하는 값을 가져올 셀의 범위를 지정해주는 것입니다. 이 예제에서는 조건범위는 A열, 찾는범위가 D열이 됩니다. A열에서 찾고자 항목의 값이 D열에 들어가 있기 때문입니다. 따라서 D2부터 D20 안에서 값을 찾도록 찾는범위를 지정해줍니다.
위에서 처럼 동일하게 직접 D2:D20이라고 입력하거나 마우스 드래그로 범위를 지정해주면 됩니다.
그리고 이제 입력할 내용이 없으므로 괄호를 닫아서 수식입력을 마칩니다. 그리고 엔터를 누르면?
총 3개 항목이 있는 표고버섯의 구입금액이 합산되어 출력이 되었습니다. 간단하죠?
검산을 해봐도 값은 정확합니다. 22,500+15,000+63,000이기 때문에 출력된 값 100,500원이 정확한 것을 확인할 수 있습니다. 이렇게 sumif를 통해 간단하게 원하는 항목의 값을 구할 수 있게 됩니다.
** 유의사항 : 기본적으로 sumif는 행(가로방향)으로 우측으로 값을 찾기 때문에 예제에 나와 있는대로 항목, 가격 등을 정리할 때 열(세로방향)로 정리해야 활용하기 좋습니다.
4. 연속 적용하기
그렇다면 앞서 '표고버섯'의 값을 sumif로 구한대로 나머지 항목도 총합을 구해줍니다. 하지만 이 작업을 반복해서 할 필요는 없습니다. 숙달을 위해 직접 여러 번 해보는 것은 좋지만 간단하게 한 번의 드래그를 통해 모든 항목의 값을 연속 적용시킬 수 있기 때문입니다.
연속으로 수식을 적용하는 방법은 공통입니다. 아래 사진처럼 연속적용할 수식이 들어있는 셀의 우측 하단(점)을 클릭한 후 그대로 드래그 해서 내립니다. 연속 적용 원하는 칸까지 드래그 해서 내린 후 마우스 왼쪽 버튼에서 손을 떼면 연속 적용이 됩니다.
값이 연달아 적용된 것을 확인할 수 있습니다. 이렇게하면 반복해서 수식을 입력할 필요도 없이 단 한 번의 함수 작업으로 자동으로 각 항목의 값을 구할 수 있습니다. 매우 편리하겠죠?
5. 절대참조 지정하기(중요)
하지만 여기서 매우 중요한 유의사항이 있습니다. 수식이 적용된 다른 셀들을 살펴보면 조건범위가 자동으로 한칸씩 하향되어 있는 걸 확인할 수 있습니다. 해당 연속으로 수식을 적용하는 것은 기본적으로 지정한 셀의 범위도 마찬가지로 자동으로 한칸씩 밑으로 밀리도록 되어 있기 때문입니다. 이것을 '상대참조'라고 하며 기본적으로 엑셀은 상대참조를 합니다. 아래 두장의 그림을 비교해 보면 정확히 알 수 있습니다.
앞서 수식으로 입력한 '조건범위, 조건, 찾는범위' 중에서 '조건'의 경우 상대참조를 통해 한칸씩 내려오면서 바뀌는 것이 맞지만 다른 '조건범위, 찾는범위'까지 같이 바뀔 경우 결과값이 바뀌게 됩니다. 그래서 많은 계산식에서 오류가 발생하는 것입니다.
따라서 '조건범위, 찾는범위'는 변경되지 않도록 고정을 해야 합니다. 이렇게 절대적으로 지정된 범위만 활용하도록 하는 것을 '절대참조'라고 부릅니다.
절대참조는 달러($)표시를 해당 함수 조건에 입력함으로써 가능합니다. 표본이 되는 표고버섯의 수식에 적용해 봅니다. (범위가 아닌 '조건'에 해당하는 G4 값의 경우 달러표시를 하지 않습니다.)
=SUMIF($A$2:$A$20,G4,$D$2:$D$20)
조건범위, 찾기범위에 해당하는 모든 문자 앞에 달러($)를 입력했습니다. 그러면 해당 범위는 연속 수식을 채워넣어도 변경되지 않는 걸 확인할 수 있습니다.
가장 아래 항목인 미나리를 확인해 보면 조건인 G10에는 절대참조($)가 표기되지 않았기 때문에 그대로 순차적으로 값이 G4에서 G10으로 변경된 걸 확인할 수 있고 다른 조건범위, 찾기범위 값은 변경되지 않은 것을 알 수 있습니다.
**절대참조 적용에서 각 수식 항목마다 $표시를 직접 하는 것은 번거로우므로 단축키를 활용해야 합니다. 단축키는 F4입니다. 해당 항목을 클릭한 뒤 키보드에 있는 F4키를 누르면 자동으로 달러문자가 들어가는 것을 확인할 수 있습니다.
지금까지 엑셀 함수 중 자주 활용하게 되는 sumif 함수에 대해서 알아봤습니다. 자주 활용할 뿐 아니라 업무 프로세스를 획기적으로 줄여주는 기능이기 때문에 숙달하도록 연습해서 적극적으로 활용할 수 있기를 바랍니다.
