Home > 엑셀 함수 > 수학 및 삼각 함수 > 엑셀함수 SUMIFS – 여러 조건을 만족하는 범위의 합계구하기

엑셀함수 SUMIFS - 여러 조건을 만족하는 범위의 합계구하기

엑셀에서 SUMIF함수를 이용하면

  • 한번에 하나의 조건을 만족하는 범위의 합계를 구할 수 있지만

SUMIFS를 이용하면

  • 여러 개의 조건을 만족하는 범위를 합계를 구할 수 있다.

SUMIFS는 SUMIF함수의 IF에 S가 붙은 형태이다.
S는 영어에서 복수형일 때 붙으므로 IF가 여러 개 있는...
즉 조건을 여러 개 정해서 합계를 구하는 함수라고 생각하면 된다.

▶ SUMIF함수가 궁금하면 여기를 클릭!

※ SUMIFS는 어떻게 발음해야 할까? 마이크로소트프 홈페이지 동영상 강의를 보면 “썸이프스”라고 하는데 우리나라 사람들은 “썸이프에스”라고도 많이 부른다.
구문(Syntax)

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

여러 조건(criteria)에 맞는 범위 값을 더함

인수 :
– sum_range : 합계를 구할 숫자 범위
– criteria_range1 : 첫번째 조건을 적용할 범위
– criteria1 : 합계를 구할 첫번째 조건
– criteria_range2 : 두번째 조건을 적용할 범위
– criteria2 : 합계를 구할 두번째 조건

criteria_range, criteria 는 최대 127개까지 입력할 수 있다.

 

사용예 :

1) 판매실적에서 거래처별,상품별 합계구하기

아래 그림의 자료에서

  • 거래처가 "신촌 아트박스"이면서
  • 상품이 "노트"인

것의 판매금액 합계를 구하기 위해 다음과 같이 수식을 입력해 보자.

=SUMIFS(E4:E11,C4:C11,"신촌 아트박스",D4:D11,"노트")

엑셀함수 SUMIFS - 여러 조건을 만족하는 범위의 합계구하기

이 복잡한 수식을 정리해 보면 SUMIFS함수는 아래 그림과 같이

  • "C4:C11"범위에서
  • "신촌 아트박스"인 행만 골라낸 후(주황색 박스 범위)
  • "D4:D11"범위에서
  • "노트"인 행만 골라내어(빨간색 박스 범위)
  • 골라낸 행의 판매금액만 더해서 650,000원을 구해준다

엑셀함수 SUMIFS - 여러 조건을 만족하는 범위의 합계구하기

 

이번에는 위 그림의 자료에서 세가지 조건으로 합계를 구해보자.

  • 거래처가 "신촌 아트박스"이고
  • 상품이 "노트"이고
  • 판매금액이 5만원보다 큰

것의 판매금액 합계를 구하기 위해 다음과 같이 수식을 입력해 보자.

=SUMIFS(E4:E11,C4:C11,"신촌 아트박스",D4:D11,"노트",E4:E11,">50000")

수식이 제대로 입력이 되었다면 600,000원이 구해질 것이다.

 

2) SUMIFS함수의 인수를 셀참조로 입력하기

위의 예에서는 인수를 아래와 같이 직접 입력했는데 일일이 입력해 주어야 하는 불편함이 있다.

=SUMIFS(E4:E11,C4:C11,"신촌 아트박스",D4:D11,"노트")

실무에서는 다음과 같이 인수를 셀 참조로 많이 사용한다.

아래 표와 같이

  • 거래처와 상품명을 표에 미리 입력하고
  • 수식에서는 미리 입력된 셀을 참조하도록 한다.

이렇게 하면 거래처명이나 상품명의 변경이 발생할 때 표에서 거래처와 상품명만 바꾸면, 바꾼이름으로 SUMIFS함수가 작동하므로 편리하다.

=SUMIFS($E$4:$E$11,$C$4:$C$11,$B$19,$D$4:$D$11,C19)

엑셀함수 SUMIFS - 여러 조건을 만족하는 범위의 합계구하기

 

[ 수학 및 삼각 함수 ]

“엑셀함수 SUMIFS - 여러 조건을 만족하는 범위의 합계구하기”의 32개의 댓글

  1. 감사합니다! 다른 설명 읽어도 잘 안들어왔는데 쉽게풀어주셔서 이해했습니다.

  2. 질문이 있습니다!
    A ㅣ B
    1 ㅣ________
    2 ㅣ________
    3______ㅣ________
    4______ㅣ________
    세로열 숫자 위치가 이상하긴한데,,, A1~A3를 병합하여 사용중일 경우 SUMIFS등 다른 수식을 걸때
    A2와 A3는 값이 없는것처럼 보이는데요, A1의 값을 A2에도 적용시킬 방법이 있을까요?

    1. 띄어쓰기 인식이 하나만 되나보네요...

      아 그리고 신촌아트박스의 노트와 필기류의 값의 합을 구하려면
      =SUMIFS(E4:E11,C4:C11,"신촌아트박스",D4:D11,"노트")+SUMIFS(E4:E11,C4:C11,"신촌아트박스",D4:D11,"필기류")로 수식을 만들어야 할까요??

    2. 안녕하세요. 셀을 병합한 경우에는 첫번째 셀만 참조할 수 있습니다. 따라서 A2, A3의 값을 참조해서 사용할 수 있는 방법은 없는 것으로 알고 있습니다.
      감사합니다.

  3. 쉽게 설명해주셔서 감사합니다. 단방에 이해가 갔어요. 감사합니다 ^^

  4. 스카이림

    안녕하세요 먼저 좋은 자료 감사합니다.
    항상 잘 보고 있습니다. 고마움의 표시로 광고도 클릭합니다. ^^
    이번에 질문이 있어 댓글 남깁니다.
    근무표를 만들고 있습니다. 오전근무 사람수와 오후근무 사람수를 나누어서 몇명인지 메니저가 보고 싶어해서요
    A
    1|0700-1530
    2|1330-2200
    3|0930-1800
    4|0700-1530
    5|1100-1930
    6|1330-2200
    7|1430-2300

    오전에 근무하는 사람만 몇명인지 계산하려고 하면 셀 서식은 텍스트로 해서 시간적인 의미만 부여한 상태입니다.
    =COUNTIFS(A1:A7,"0700-1530",A1:A7,"0930-1800",A1:A7,"1100-1730")
    이렇게 하면 안되는지요? 조건표셀이 같아도 상관이 없을것 같은데 답이 나오질 않네요 오전근무자가 4명이 나와야 하는데 0명이 나오네요 ㅡㅡ

    도움 부탁드립니다.

    1. 안녕하세요. 응원 감사드립니다^^
      COUNTIFS함수에 여러 조건을 넣으면 논리적으로 AND조건입니다. 근무시간이 0700-1530이고 0930-1800이고 1100-1930을 동시에 만족시키는 행이 없기 때문에 0이 나옵니다.
      OR조건으로 해야 됩니다. 셋중에 하나만 만족하면 COUNT를 해야 합니다.
      아래와 같이 조건을 만족했을 때 더하도록 하면 됩니다.
      =COUNTIF(A1:A7,"0700-1530")+COUNTIF(A1:A7,"0930-1800")+COUNTIF(A1:A7,"1100-1930")
      혹시 오류가 나면 붙여넣기 하지 마시고 키보드에서 다시 입력하세요. 붙여 넣을 때 2바이트 문자가 들어가서 오류가 나는 경우가 있습니다.
      감사합니다.

  5. 1보다 크면"1"1.1보다 크고 5보다 작으면 "3:", 5.1보다 크고 8보다 작으면"4"로 표시를 하고싶은데 어떤 함수를 어떻게 하면되나요?

  6. 위의 설명에서, 첫번째 조건 '신촌 아트박스'와 두번째 조건 '노트'를 수식에 직접 입력하지 않고, 자동으로 먼저 '신촌 아트박스', '신림문구', '서초 아트박스'로 구분하고, 다음으로 자동으로 '노트', '필기류', '클립'으로 구분하여 각각 합계를 구하는 방법이 있을까요?
    제가 작업해야할 데이터가 첫번째 조건의 항목 종류만 1000천개를 넘어가고, 각 항목별로 98개의 값이있습니다. 그리고 두번째 조건이 요일인데, 요일별로에 데이터가 14개씩 입니다.
    두번째 조건은 요일별이니, 월요일부터 일요일까지 직접 타이핑해서 조건식을 만들면 되지만,
    첫번째 조건이 막막합니다. 조건에 1000천개가 넘는 항목을 일일히 타이핑 하기가 엄두가 안 나네요.
    (예를 들어, ID값이 0001부터 1000까지 있고, ID 0001에 월요일부터 일요일까지 항목이 있고, 각 요일별 아침 7시부터 21시까지 한시간 간격으로 값이 있습니다. 그럼 각 ID별로 총 98개(7x14)의 분리된 값이 있고, ID가 총 1000개이니, 모두 98000개의 값이 있겠네요. 여기서 각 ID별로 구분하고, 요일별로 구분하여 합계를 구하려고 합니다. 즉, 14개의 시간 값의 합계를 구하고 싶어요.)

    1. 안녕하세요. 위 글의 마지막에 있는 설명(조건을 먼저 셀에 입력하고 참조하는 방식)으로 될듯한데요. 설명만 보고는 정확히 판단이 안되니 파일을 admin@xlworks.net으로 보내주시면 확인하고 답변드리겠습니다.
      감사합니다.

      1. 방금 엑셀파일 첨부하여 메일 발송했습니다.
        한번 살펴보시고 조언 부탁드려요.
        정말 감사합니다.

          1. 구글계정으로 다시 메일 발송했으나, 안 보내지는 것 같아서,
            네이트계정으로 다시 메일 발송했습니다.

          2. 안녕하세요. 보내주신 파일을 보니 SUMIFS함수가 아니라 피벗테이블 기능을 이용하셔야 할것 같습니다.
            피벗을 이용하면 다음 그림과 같이 한방에 가능합니다.
            피벗
            피벗기능은 약간의 학습이 필요합니다. 다음 링크 참조하시면 됩니다.
            https://support.microsoft.com/ko-kr/office/%ED%94%BC%EB%B2%97-%ED%85%8C%EC%9D%B4%EB%B8%94%EC%9D%84-%EB%A7%8C%EB%93%A4%EC%96%B4-%EC%9B%8C%ED%81%AC%EC%8B%9C%ED%8A%B8-%EB%8D%B0%EC%9D%B4%ED%84%B0-%EB%B6%84%EC%84%9D-a9a84538-bfe9-40a9-a8e9-f99134456576
            감사합니다.

          3. 감사합니다. 알려주신 링크에서 피벗공부해 해결해볼게요^^

  7. =SUMIFS(E4:E11,C4:C11,"아트박스",D4:D11,"노트")
    SUMIFS 에서는 로 제외가 안되나요??
    아트박스와 노트를 제외했을때 만 남아서 200,000원
    으로 계산되야한다고 생각했는데 값을 넣어보니 982,000원이 나왔습니다.

    1. =SUMIFS(E4:E11,C4:C11,"아트박스”,D4:D11,”노트”)
      SUMIFS 에서는 로 제외가 안되나요??
      아트박스와 노트를 제외했을때 만 남아서 200,000원
      으로 계산되야한다고 생각했는데 값을 넣어보니 982,000원이 나왔습니다.

      1. 중복글 남겨서 죄송합니다. 홑화살괄호를 아트박스와 노트 앞에 넣었는데
        여기 홈페이지에서는 안보이네요...

    2. 답변이 늦었습니다. 다음 수식으로 해 보세요.
      =SUMIFS(E4:E11,C4:C11,"<>*아트박스*",D4:D11,"<>노트",E4:E11,">50000")
      혹시 안되면 쌍따옴표를 키보드에 있는 쌍따옴표로 다시 입력해 보세요.

  8. 핑백: 엑셀에서 SUMIFS 함수로 월별 합계 구하기 - 엑셀웍스

  9. 안녕하세요 덕분에 업무에 많은 도움이 되고 있습니다.
    궁금한게 있어 질문드립니다.
    sumifs 함수관련해서 예를 들어 거리가 71km이상이고 100km와 같거나 작다라고 표기할때
    SUMIFS(표2[입고수량(톤)],표2[대분류],"수확수종갱신",표2[소분류],"호그칩",표2[거리(km)],"=100")
    위 수식에 오류가 있는지 결과값이 표기가 안되는데 방법이 없나요?

    1. 참고로 거리를 조건에 덧붙여 표기할때도 결과값이 도출이 되지 않습니다.

    2. "=100"은 100인것만 가져옵니다.
      71이상이면 ">=71"
      100과 같거나 작으면 "<=100" 조건이 필요합니다.

  10. 혹시 일자별?함수 합계공식이 가능한지요.
    저렇게 일자별로 정리중인데 1월 합계 만 넣고싶은데
    시트에 1/12 . 1/13 이런식으로 적다보니.; 그것의 공식은 아예 다른것일까요?

    1. 다음과 같이 날짜 조건을 두개 넣으면 됩니다.(시작일보다 크거나 같고 끝날짜보다 작거나 같도록)
      =SUMIFS(E4:E11,B4:B11,">="&DATE(2017,7,1),B4:B11,"<="&DATE(2017,7,31)) 혹시 수식이 작동하지 않으면 쌍따옴표를 키보드에 있는 것으로 다시 입력하세요.

  11. 함수의 조건에서 꼭 "신촌 아트박스"를 직접 써서 입력해야하나요?
    C열 거래처명에 써놓은걸 참조해서 쓸수는 없는건가요?

답글 남기기

이메일 주소를 발행하지 않을 것입니다.