■ 목차
1. 함수 설명
2. Sumif 함수의 구문 / 사용방법
3. Sumifs 함수의 구문 / 사용방법
4. Sumif 함수 사용해 보기
5. Sumifs 함수 사용해 보기
** 핵심 요약
1. 함수 설명
- "Sumif 함수"는 "Sum 함수 + if 함수"입니다.
- 그래서 조건을 만족하는(If 함수) 대상의 합계(Sum 함수)를 구할 수 있습니다.
- 판매현황 데이터에서 "1월달(if = 1월)"만의 "판매 금액" 합계 (Sum = 판매금액)를 구한다던지
- 또는 특정 지점 (If = 강남지점)만의 "판매 금액" 합계를 구하는 경우입니다.
- "Sumif 함수"가 없는 경우에도 "If 함수"만으로 구할 수도 있지만 조금 불편하기 때문에
- 이런 경우에 한해서 "Sumif 함수"로 간단하게 "합계"를 구하는 것이 가능합니다.
- 이렇게 특정 조건의 값을 구하는 함수로 "Sumif 함수"외에 "Countif 함수", "Averageif 함수"가 있습니다.
- 그리고 "Sumifs 함수" 같은 경우는 "ifs" 즉, 여러가지 조건을 만족하는 합계를 구하는 것이 가능합니다.
2. Sumif 함수의 구문 / 사용방법
SUMIF(range, criteria, [sum_range])
- range 필수 요소입니다. 조건을 적용할 셀 범위입니다
- criteria 필수 요소입니다. 숫자, 식, 셀 참조, 텍스트 또는 함수 형식의 조건입니다
- sum_range 선택 요소입니다. 지정하게 되면 이 영역의 값으로 합계를 구하고 지정을 하지 않게 되면 "rang" 영역에서 합계를 구합니다.
- Sumif 함수는 3가지 인수가 필요합니다.
- 첫 번째 인수 "range"는 영어로 "범위"라는 의미로 엑셀에서는 특히 "셀주소의 범위"를 얘기합니다.
- 여기서는 조건을 검사할 범위로 "A1:A10"과 같이 주로 셀주소 범위를 입력해 줍니다.
- 두 번째 인수는 "criteria", 영어로 "판단할 기준"을 의미하며 "60점 이상"과 같은 조건을 의미합니다.
- 이 때 엑셀은 조건(criteria)을 셀주소 범위(range)에서 하나하나 비교해가며 참(True)/거짓(False)을 모두 판정하게 됩니다.
- 세 번째 인수는 "sum_range", 즉 합계(sum)를 구해줄 범위(range)입니다.
- 생략이 가능하고 생략하는 경우 첫 번째 인수 "range"에서 합계를 구하게 됩니다.
- 주의를 하실 점은 조건을 검사하는 범위(range)의 개수랑 합계를 구할 범위(sum_range)의 개수가 같아야 한다는 것입니다.
- 엑셀은 먼저 조건을 검사하는 범위에서 각 셀에 대해서 "참, 참, 거짓, 참, … "처럼 셀의 개수만큼 판정을 하고
- 다음으로 합계를 구할 셀에서 순서대로 "참, 참, 거짓, 참, …"을 적용하여 참에 대해서만 합계를 해주게 됩니다.
- 그래서 동일한 개수의 range, sum_range를 사용해줘야 합니다.
- 실제 개수가 조금 틀려도 엑셀이 얼추 합계 값을 맞추어 냅니다. (똑똑한 엑셀 입니다.)
- 다만 동일한 개수의 셀을 한줄 밀려서 쓰게 되면(ex range = A1:A10, sum_range = B2:B11처럼) 잘 못된 합계를 만들 수 있습니다. ㅠㅠ
- 그리고 우리가 원하는 다양한 조건으로 합계를 구하기 위해서 "다양한 조건식"을 입력하는 방법들에 대해서 알고 있으면 좋습니다.
- 우선 아래 사례들에 대해서 눈으로 익혀 두시기 바랍니다.
- 외우거나 하실 필요 없습니다. Sumif 함수가 사용되는 사례정도로 이런 것이 있구나하고 눈에 익혀 두시면 됩니다.
1) Sumif 함수 예제1 - 조건 검사할 범위 (range) = 합계를 구할 범위(sum_range)인 경우 (같은 경우)
- 다음의 평균점수에서 합격자(60점 이상)의 총점을 구하는 경우입니다.
- 이경우 "=sumif ( 평균점수 범위, ">=60", 평균점수 범위)처럼 구할 수도 있지만
- 조건을 검사할 범위 (평균 점수)와 합계를 구할 범위(평균 점수)가 동일한 범위이기 때문에 합계를 구할 범위 하나를 생략할 수 있습니다.
- 사실 "sum_range" 범위를 입력해줘도 되기 때문에 그냥 속 편하게 sum_range을 입력하는 방식으로 외우고 사용하셔도 됩니다.
2) Sumif 함수 예제 2 - 조건 검사할 범위 (range) ≠ 합계를 구할 범위 (sum_range)인 경우 (다른 경우)
- 판매 리스트에서 "사과"에 대한 총판매금액을 구하는 경우입니다.
- 조건을 검사할 범위는 "판매 과일"이고 합계를 구할 범위는 "판매 금액"이 해당하는 경우입니다.
- 가장 많이 사용이 되는 케이스입니다.
- 여기서 중요한 것은 조건을 검사는 범위와 그와 동일 크기로 매칭이 되는 다른 위치에서 합계 범위를 적용할 수 있다는 것을 이해하는 것입니다.
- A범위에서 조건을 판정하고 다른 동일한 크기 범위 B에서 합계를 구한다는 의미입니다.
- 그리고 Sumif 함수 사용에서 날개를 달고자 하신다면 다양한 조건 적용방식을 이해하시고 사용이 가능해야 할 것입니다.
3) Sumif 함수 다양한 조건 적용 방식
① 일치하는 텍스트 찾기
- 텍스트에서 가장 많이 사용하는 형태입니다. 숫자로도 사용이 가능합니다.
- 함수에서 텍스트를 사용하는 경우 항상 큰따옴표( "" )를 같이 사용해줘야 합니다.
- 아래와 같은 형태로 사용이 가능합니다.
- 나머지 비교 연산자( <>, >, <, >=, <= )도 동일하게 사용이 가능하고
- 한글에 비교 연산자를 적용하게 되면 (ex, " >가" )되면 어떻게 될까요?
- 엑셀이 잘 계산해 줍니다. (^^)
- "가, 나, 다, …", "A, B, C, …" 모두 비교 연산자가 사용이 가능하고 엑셀이 순서대로 잘 찾아서 비교해 줍니다.
- 한 번씩 해보시고 똑똑한 엑셀을 경험해 보시기 바랍니다.
② 일치하는 텍스트 찾기 - 특정 셀의 값을 기준으로 적용
- 이번에는 다른 셀의 값을 기준으로 조건을 수식형태로 만드는 경우입니다.
- 문자/숫자 모두 동일한 방식으로 사용이 가능하며
- 특히 C8셀에 "=남"의 경우 반드시 문자형태로 입력이 되어야 합니다.
- 그냥 수식입력하 듯이 하면 오류가 나기 때문에 반드시 맨 앞에 "외 따옴표 ( = 아포스트로피, ' )를 붙여 주셔야 합니다.
③ 유사한 텍스트 찾기 - 와일드카드 (*, ?) 사용
- 엑셀의 유사한 문자를 찾기 위한 와일드카드(*, ?)의 사용은 아래와 같이 하시면 됩니다.
- 엑셀에서의 와일드 카드 사용법에 대해서 잘 모르시면 따로 학습을 해주세요. (얘기가 너무 길어질 것 같아서, 죄송합니다. ㅠㅠ)
- "신발"로 시작하는 모든 문자를 찾아 주는 수식입니다. "=신발*"
- 만약 "신발"이라는 단어가 포함된 문자를 찾고자 한다면 "=*신발*"과 같이 신발 양쪽에 "*" 와이드카드 문자를 붙여 주시면 됩니다.
④ 날짜를 조건식으로 사용하기
- 특정 일자 이후 혹은 이전과 같이 날짜를 조건으로 합계를 구해야 하는 경우는 좀 까다롭습니다.
- 먼저 "날짜"가 정확하게 엑셀 서식에 맞게 입력이 되어야 하고
- 며칠부터 며칠까지의 경우 "시작 날짜보다 크다"와 "종료 날짜보다 작다"의 조건이 2개 되기 때문에 "Sumifs 함수"를 사용해줘야 합니다.
- 그리고 특정 월 "3월"의 판매량 총계 같은 경우도 마찬가지로 3/1 이상과 3/31 이하의 조건이 2개가 되어 "Sumifs 함수"를 사용해줘야 합니다.
- 나머지는 기본적으로 날짜도 "숫자"이기 때문에 날짜 형식으로로 정확하게 사용되어 있다면 동일한 방식으로 사용을 하시면 됩니다.
3. Sumifs 함수의 구문 / 사용방법
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2],...)
- sum_ragne 필수 요소입니다. 합계를 구할 셀의 범위입니다.
- criteria_range1 필수 요소입니다. Criteria1을 사용하여 평가하는 범위입니다.
- criteria1 필수 요소입니다. Criteira_range1에 적용할 조건입니다.
- criteria_range2 / criteira2, …. 선택 요소 입니다. 추가로 적용할 조건이 있다면 해당하는 범위와 조건입니다.
- 첫 번째 인수 sum_range는 "합계를 구할 범위"입니다.
- 두 번째 인수부터 "조건적용 범위 1/조건 1", "조건 적용범위 2/조건 2"처럼 쌍으로 조건검사가 나오기 때문에
- 가장 먼저 나오는 인수가 sum_range 인수가 되어 Sumif 함수와 조금 차이가 있습니다.
- 두 번째 인수는 criteria_range1입니다. 조건을 검사할 범위 1번이라고 보시면 되고
- 세 번째 인수가 criteria1으로 criteria_range1에서 검사할 조건 1번이 됩니다.
- 이렇게 조검범위 1/조건 1, 조건범위 2/조건 2, …. 쌍으로 조건을 최대 127개까지 입력이 가능합니다.
- 그리고 우리가 이해를 꼭 해줘야 하는 부분이 이렇게 조건이 쌍으로 조건범위 1/조건 1, 조건범위 2/조건 2를 입력하게 되면
- 구해지는 합계는 이 모든 조건을 만족하는 (And 조건) 값을 구해준다는 것입니다.
- 그리고 수식의 목적에 따라서 criteria_range1 = criteria_range2가 될 수 있다는 것도 이해를 해주시면 좋을 듯합니다.
- 그럼 Or 함수처럼 적용해서 합계를 구하려면 어떻게 해야 할까요?
- 제한적으로 사용이 가능한 방법으로 "Sumifs 함수 + Sumifs 함수"를 사용할 수 있습니다.
- 지역별 판매수량에서 "서울" 지역의 판매합계와 "부산" 지역의 판매 합계를 더해주게 되면
- "서울 or 부산"의 판매 합계 수량을 구할 수 있습니다.
- 단, 이처럼 조건이 "서울이면 부산이 아니거나" "부산이면 서울이 아닌" 것으로 딱 떨어지는 경우에만 사용이 가능하고
- "남자"의 판매 합계랑과, "서울"지점의 판매합계를 더하는 경우처럼 남자이면서 서울지점 소속인 경우가 있는 경우는 중복계산이 되기 때문에 불가합니다.
- 이 경우는 좀 복잡한데 (남자이면서 서울 지점이 아닌 경우) + (서울지점이면서 남자가 아닌 경우)로 합계를 구해줘야 합니다. ㅠㅠ
- 이 부분은 엑셀의 영역이 아니기 때문에 필요하시면 천천히 되짚어 보시기 바랍니다.
- 좀 복잡한 설명이 되어 버렸는데 "Sumifs 함수"는 모든 조건을 만족하는 (And 함수처럼) 합계를 구해준다라고
- 범위 1/조건 1, 범위 2/조건 2, … 처럼 범위/조건이 쌍으로 입력된다 정도로 기억하시면 됩니다.
4. Sumif 함수 사용해 보기
- 다음은 거래선 매출 현황입니다.
- 각 거래선별 총판매금액을 구해 보세요.
- 이번 예제에서 우리가 주의해야 할 부분은
- 첫 번째 각 인수에 해당하는 범위를 정확하게 이해하는 것입니다. (rang, sum_range)
- 두 번째는 조건식(criteria)을 정확하게 사용하는 것이고
- 마지막으로 수식 복사를 위해서 "절대 참조"를 어디에 적용할 것인지를 이해하는 것 정도입니다.
- 먼저 "일성전기"의 총판매액을 구해 보도록 하겠습니다.
- 수식을 사용하기 위해서 등호( = )와 함수명 (sumif)를 넣고 괄호까지 입력을 합니다. " =sumif( "
- 첫 번째 인수 (range)는 조건을 적용할 셀 범위로 마우스로 "거래선 (C3:C8)"을 선택하고
- 나머지 "진도한국/간도월드"의 총 판매액을 수식복사 예정이기 때문에 "F4키"를 사용해서 절대 참조 형식($C$3:$C$3)으로 변경해 줍니다.
- 두 번째 인수 (criteria)는 조건을 입력해줘야 하는데 순수하게 숫자를 입력하거나 숫자가 아닌 경우는 모두 "텍스트"형식으로 입력이 되어야 합니다.
- 거래선 일성전기(B11) 셀을 참조하여 "="&B11과 같이 입력을 해줍니다.
- 인수와 인수는 쉼표( " , ")로 구분해 주는 것도 잊지 마시기 바랍니다.
- 세 번째 인수 (sum_range)는 합계를 해줄 셀의 범위인 "판매 금액 ($G$3:$G$8)"으로
- 마찬가지로 마우스로 셀 범위를 선택한 후 "F4키"를 사용해서 "절대 참조" 형식으로 입력을 해줍니다.
- 나머지 "진도한국/간도월드"의 "총 판매액"은 복사/붙여 넣기로 입력을 해줍니다.
- 이때 절대참조/상대참조를 정확하게 사용을 하셨다면 아래와 같이 결과 확인이 가능합니다.
5. Sumifs 함수 사용해 보기
- 이번에는 동일한 거래선 매출 현황 자료에서 2월 판매 금액을 구해 보도록 하겠습니다.
- 2월의 판매 금액은 (2월 1일 이상의 날짜) And (2월 28일 이하의 날짜) 2가지 조건을 적용해야 합니다.
- 2가지 조건을 적용하기 위해서 "Sumifs 함수"를 사용해 보도록 하겠습니다.
- 첫 번째 인수 (sum_range)는 합계를 구할 범위입니다. "판매 금액"의 "G3:G8"까지 마우스로 선택하여 입력합니다.
- 두 번째 인수 (criteria_range1)는 조건을 적용할 범위 1로 "일자"의 "B3:B8"까지를 마우스로 선택하여 입력해 줍니다.
- 세 번째 인수 (criteria1)는 조건 1로 "2월 1일 이상"의 조건이 되도록 ">=2022-02-01"를 정확하게 입력합니다.
- 다음으로 2번째 조건범위와 2번째 조건을 입력합니다.
- 2번째 조건 범위(criteria_range2)는 1번째 조건범위와 동일합니다. "B3:B8"입니다.
- 2번째 조건(criteria2)의 경우 "2월 28일 이하"의 조건이 되도록 "<=2022-02-28"를 정확하게 입력합니다.
- Sumifs 함수는 조건범위/조건이 항상 쌍으로 순서대로 입력이 되어야 합니다.
- 모두 입력이 완료되면 아래와 같이 결과 확인 가능합니다.
** 핵심 요약 :Sumif, Sumifs 함수 사용법 - 여러 조건에 따라 합계를 구해보자
1. 함수 설명
- "Sumif 함수"는 "Sum 함수 + if 함수"이다.
- 그래서 조건(If 함수)을 만족하는 대상의 합계(Sum 함수)를 구할 수 있다.
- 특정 조건의 값을 구하는 함수로 "Sumif 함수"외에 "Countif 함수", "Averageif 함수"가 있다.
2. Sumif 함수의 구문 / 사용방법
- SUMIF(range, criteria, [sum_range])
- range 필수 요소, 조건을 적용할 셀 범위이다.
- criteria 필수 요소, 숫자, 식, 셀 참조, 텍스트 또는 함수 형식의 조건을 입력한다.
- sum_range 선택 요소, 합계를 구하고자 하는 셀 범위이다.
- 조건을 검사하는 범위(range)의 개수랑 합계를 구할 범위(sum_range)의 개수가 같아야 한다.
- Sumif 함수 예제 1 - 조건 검사할 범위 (range) = 합계를 구할 범위(sum_range)인 경우 (같은 경우)
- Sumif 함수 예제 2 - 조건 검사할 범위 (range) ≠ 합계를 구할 범위 (sum_range)인 경우 (다른 경우)
- Sumif 함수 다양한 조건 적용 방식을 눈에 익혀두자.
3. Sumifs 함수의 구문 / 사용방법
- SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2],...)
- sum_ragne 필수 요소, 합계를 구할 셀의 범위이다.
- criteria_range1 필수 요소, Criteria1을 사용하여 조건을 평가하는 범위이다.
- criteria1 필수 요소, Criteira_range1에 적용할 조건이다.
- 이렇게 조검범위 1/조건 1, 조건범위 2/조건 2, …. 쌍으로 조건을 최대 127개까지 입력이 가능하다.
- 또한 구해지는 합계는 이 모든 조건을 만족하는 (And 조건) 값을 구할 수 있다.
- Or 조건처럼 사용하고자 하면 제한적이지만 "Sumifs 함수 + Sumifs 함수"를 사용할 수 있다.
4. Sumif 함수 사용해 보기
- 거래선 매출현황에서 각 거래선별 총판매금액을 구하는 경우
- 첫 번째는 각 인수에 해당하는 범위를 정확하게 이해해야 한다.. (rang, sum_range)
- 두 번째는 조건식(criteria)을 정확하게 사용하여야 하며
- 마지막으로 수식 복사를 하는 경우 "절대 참조"를 어디에 적용할 것인지 알아야 한다.
5. Sumifs 함수 사용해 보기
- 거래선 매출 현황 자료에서 2월달 판매 금액을 구해 보자.
- 2월의 판매 금액은 (2월 1일 이상인 날짜) And (2월 28일 이하인 날짜) 2가지 조건으로 "Sumifs 함수"를 사용해야 한다.
- 첫 번째 인수 (sum_range)는 합계를 구할 범위로 "판매 금액"을 마우스로 선택하여 입력한다.
- 두 번째 인수 (criteria_range1)는 조건을 적용할 범위 1로 "일자"를 마우스로 선택하여 입력한다.
- 세 번째 인수 (criteria1)는 조건 1로 "2월 1일 이상"의 조건이 되도록 ">=2022-02-01"를 정확하게 입력한다.
- 2번째 조건 범위(criteria_range2)는 1번째 조건범위와 동일한 "일자"이다.
- 2번째 조건(criteria2)의 경우 "2월 28일 이하"의 조건이 되도록 "<=2022-02-28"를 정확하게 입력한다.
- Sumifs 함수는 조건범위/조건이 항상 쌍으로 순서대로 입력이 되어야 한다.
[엑셀 기초 강좌] - 999. 엑셀 기초 함수 목차 (완료)
999. 엑셀 기초 함수 목차 (완료)
[엑셀 기초 강좌] - 029. (엑셀 기초 함수) Sum함수 : 숫자의 합계를 구해보자 [엑셀 기초 강좌] - 030. (엑셀 기초 함수) Max, Min 함수 - 최대값, 최소값을 구해보자 [엑셀 기초 강좌] - 031. (엑셀 기초 함
nextmeok.tistory.com
* 엑셀 관련 궁금하신 거나 어려운 점이 있으시면 자유롭게 질문을 해주세요.
* 가능한 도움을 드릴 수 있도록 하겠습니다.
* 저도 많이 알지는 못하지만 누구나 그렇듯 시작이란 게 있고 경험이란 게 다를 것 같습니다.
* 편하게 문의하시면 됩니다. 저도 모를 수 있다. 감안해 주시고.
- ILU, SH -
댓글