엑셀에서 SUMIF 함수를 사용하면
- 한번에 하나의 조건을 만족하는 범위의 합계를 구할 수 있지만
SUMIFS 함수를 사용하면
- 여러 개의 조건을 만족하는 범위를 합계를 구할 수 있습니다.
SUMIFS는 SUMIF함수의 IF에 S가 붙은 형태입니다. S는 영어에서 복수형일 때 붙으므로 IF가 여러 개 있는, 즉 조건을 여러 개 정해서 합계를 구하는 함수라고 생각하면 됩니다.
구문(Syntax)
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
SUMIFS(합계범위, 조건범위1, 조건1, [조건범위2, 조건2], …)
여러 조건을 만족하는 범위의 숫자의 합계를 구해준다.
인수 :
- sum_range : 합계를 구할 숫자 범위
- criteria_range1 : 첫번째 조건을 적용할 범위
- criteria1 : 합계를 구할 첫번째 조건
- criteria_range2 : 두번째 조건을 적용할 범위(생략 가능)
- criteria2 : 합계를 구할 두번째 조건(생략 가능)
- …
criteria_range, criteria 는 최대 127개까지 입력할 수 있다.
사용 예
1) 2개의 조건을 만족하는 합계구하기
아래 그림의 자료에서 2개의 조건을 만족하는 합계를 구해 보겠습니다.
- 거래처가 '신촌 아트박스'이면서
- 상품이 '노트'인
것의 판매금액 합계를 구하려면 다음과 같이 수식을 입력합니다.
=SUMIFS(E4:E11,C4:C11,"신촌 아트박스",D4:D11,"노트")
수식 풀이:
이 복잡한 수식을 정리해 보면 SUMIFS 함수는 아래 그림과 같이
- 거래처명이 입력된 첫 번째 조건 범위 [C4:C11] 에서
- '신촌 아트박스'인 행만 골라낸 후(빨간색 박스 범위)
- 상품명이 입력된 두 번째 조건 범위 [D4:D11] 에서
- '노트'인 행만 골라내어(보라색 박스 범위)
- 판매금액이 입력된 범위 [E4:E11]에서 같은 행의 판매금액만 더해서 650,000원을 구해줍니다.
2) 3개의 조건을 만족하는 합계구하기
이번에는 다음과 같이 세 가지 조건을 만족하는 합계를 구해보겠습니다.
- 거래처가 '신촌 아트박스'이고
- 상품이 '노트'이고
- 판매금액이 5만원보다 큰
것의 판매금액 합계를 구하려면 다음 수식을 입력합니다.
=SUMIFS(E4:E11,C4:C11,"신촌 아트박스",D4:D11,"노트",E4:E11,">50000")
수식이 제대로 입력이 되었다면 600,000원이 구해집니다.
만약, 합계를 구할 조건이 더 필요하다면 조건 범위와 조건을 계속 추가할 수 있고 최대 127개까지 추가할 수 있습니다.
3) 인수를 셀참조로 입력하기
위의 예에서는 인수를 아래와 같이 직접 입력했는데 하나하나 입력해 주어야 하는 불편함이 있습니다.
=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 함수를 이용하면 여러 개의 조건을 만족하는 합계를 구할 수 있는데, 오늘은 약간 응용하여 조건을 만족하는 월별 합계를 구하는… …
DSUM 함수 - 데이터베이스에서 조건에 맞는 항목의 합계 구하기 - 엑셀의 데이터베이스(Database)함수는 다양한 조건에 맞는 합계를 구한다던지, 평균, 갯수 등을 쉽게 구할 수 있는 함수이다. 모든 함수는 알파벳 “D”로 시작하며… …
SUMIF 함수 - 조건을 만족하는 범위의 합계 구하기 - SUMIF 함수는 SUM 함수와 IF 함수가 합쳐진 형태입니다. "만약(IF) 어떤 조건을 만족하면 SUM하라"는 의미입니다. 이 함수는 자료에서 특정 조건을 만족하는… …
감사합니다! 다른 설명 읽어도 잘 안들어왔는데 쉽게풀어주셔서 이해했습니다.
질문이 있습니다!
A ㅣ B
1 ㅣ________
2 ㅣ________
3______ㅣ________
4______ㅣ________
세로열 숫자 위치가 이상하긴한데,,, A1~A3를 병합하여 사용중일 경우 SUMIFS등 다른 수식을 걸때
A2와 A3는 값이 없는것처럼 보이는데요, A1의 값을 A2에도 적용시킬 방법이 있을까요?
띄어쓰기 인식이 하나만 되나보네요...
아 그리고 신촌아트박스의 노트와 필기류의 값의 합을 구하려면
=SUMIFS(E4:E11,C4:C11,"신촌아트박스",D4:D11,"노트")+SUMIFS(E4:E11,C4:C11,"신촌아트박스",D4:D11,"필기류")로 수식을 만들어야 할까요??
네 맞습니다. SUMIFS를 두번 써야합니다.
안녕하세요. 셀을 병합한 경우에는 첫번째 셀만 참조할 수 있습니다. 따라서 A2, A3의 값을 참조해서 사용할 수 있는 방법은 없는 것으로 알고 있습니다.
감사합니다.
병합한 셀에 수식을 걸지 마시고, 왼쪽에 복사해서 병합하지 않은 셀을 만들고 수식을 넣으시면 됩니다.
새로 만든 셀을 인쇄영역 밖에 두시거나, 아니면 글자를 하얀색으로 하시면 됩니다. 2년 전에 올리신 문의니까 이미 잘 해결하셨겠지만요^^
쉽게 설명해주셔서 감사합니다. 단방에 이해가 갔어요. 감사합니다 ^^
안녕하세요 먼저 좋은 자료 감사합니다.
항상 잘 보고 있습니다. 고마움의 표시로 광고도 클릭합니다. ^^
이번에 질문이 있어 댓글 남깁니다.
근무표를 만들고 있습니다. 오전근무 사람수와 오후근무 사람수를 나누어서 몇명인지 메니저가 보고 싶어해서요
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명이 나오네요 ㅡㅡ
도움 부탁드립니다.
안녕하세요. 응원 감사드립니다^^
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바이트 문자가 들어가서 오류가 나는 경우가 있습니다.
감사합니다.
1보다 크면"1"1.1보다 크고 5보다 작으면 "3:", 5.1보다 크고 8보다 작으면"4"로 표시를 하고싶은데 어떤 함수를 어떻게 하면되나요?
안녕하세요. 질문하신 내용은 숫자의 구간별로 값을 가져오는 것으로 이해됩니다. 다음 링크를 참고하시어 값을 구간별로 기입하고 lookkup함수를 이용하면 될듯합니다.
https://xlworks.net/excel_function_lookup/
감사합니다.
위의 설명에서, 첫번째 조건 '신촌 아트박스'와 두번째 조건 '노트'를 수식에 직접 입력하지 않고, 자동으로 먼저 '신촌 아트박스', '신림문구', '서초 아트박스'로 구분하고, 다음으로 자동으로 '노트', '필기류', '클립'으로 구분하여 각각 합계를 구하는 방법이 있을까요?
제가 작업해야할 데이터가 첫번째 조건의 항목 종류만 1000천개를 넘어가고, 각 항목별로 98개의 값이있습니다. 그리고 두번째 조건이 요일인데, 요일별로에 데이터가 14개씩 입니다.
두번째 조건은 요일별이니, 월요일부터 일요일까지 직접 타이핑해서 조건식을 만들면 되지만,
첫번째 조건이 막막합니다. 조건에 1000천개가 넘는 항목을 일일히 타이핑 하기가 엄두가 안 나네요.
(예를 들어, ID값이 0001부터 1000까지 있고, ID 0001에 월요일부터 일요일까지 항목이 있고, 각 요일별 아침 7시부터 21시까지 한시간 간격으로 값이 있습니다. 그럼 각 ID별로 총 98개(7x14)의 분리된 값이 있고, ID가 총 1000개이니, 모두 98000개의 값이 있겠네요. 여기서 각 ID별로 구분하고, 요일별로 구분하여 합계를 구하려고 합니다. 즉, 14개의 시간 값의 합계를 구하고 싶어요.)
안녕하세요. 위 글의 마지막에 있는 설명(조건을 먼저 셀에 입력하고 참조하는 방식)으로 될듯한데요. 설명만 보고는 정확히 판단이 안되니 파일을 admin@xlworks.net으로 보내주시면 확인하고 답변드리겠습니다.
감사합니다.
방금 엑셀파일 첨부하여 메일 발송했습니다.
한번 살펴보시고 조언 부탁드려요.
정말 감사합니다.
안녕하세요. 메일이 오지 않았습니다.
구글계정으로 다시 메일 발송했으나, 안 보내지는 것 같아서,
네이트계정으로 다시 메일 발송했습니다.
여전히 메일이 오지 않네요. 메일 주소는 admin@xlworks.net 입니다. 맞는지 다시 한번 확인해 보시기 바랍니다.
메일발송이 되지 않아, 구글드라이브 링크 남깁니다.
아래 링크 들어가시면, 폴더안에 엑셀파일과 메일 발송했었던 정보도 있습니다.
https://drive.google.com/drive/folders/1zMBWoulZASE9OOY6hEsFmGbnM0-Xz7K6?usp=sharing
안녕하세요. 보내주신 파일을 보니 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
감사합니다.
감사합니다. 알려주신 링크에서 피벗공부해 해결해볼게요^^
=SUMIFS(E4:E11,C4:C11,"아트박스",D4:D11,"노트")
SUMIFS 에서는 로 제외가 안되나요??
아트박스와 노트를 제외했을때 만 남아서 200,000원
으로 계산되야한다고 생각했는데 값을 넣어보니 982,000원이 나왔습니다.
=SUMIFS(E4:E11,C4:C11,"아트박스”,D4:D11,”노트”)
SUMIFS 에서는 로 제외가 안되나요??
아트박스와 노트를 제외했을때 만 남아서 200,000원
으로 계산되야한다고 생각했는데 값을 넣어보니 982,000원이 나왔습니다.
중복글 남겨서 죄송합니다. 홑화살괄호를 아트박스와 노트 앞에 넣었는데
여기 홈페이지에서는 안보이네요...
답변이 늦었습니다. 다음 수식으로 해 보세요.
=SUMIFS(E4:E11,C4:C11,"<>*아트박스*",D4:D11,"<>노트",E4:E11,">50000")
혹시 안되면 쌍따옴표를 키보드에 있는 쌍따옴표로 다시 입력해 보세요.
감사합니다. 쉽게 설명해주셔서 업무에 잘 활용하였습니다.
안녕하세요 덕분에 업무에 많은 도움이 되고 있습니다.
궁금한게 있어 질문드립니다.
sumifs 함수관련해서 예를 들어 거리가 71km이상이고 100km와 같거나 작다라고 표기할때
SUMIFS(표2[입고수량(톤)],표2[대분류],"수확수종갱신",표2[소분류],"호그칩",표2[거리(km)],"=100")
위 수식에 오류가 있는지 결과값이 표기가 안되는데 방법이 없나요?
참고로 거리를 조건에 덧붙여 표기할때도 결과값이 도출이 되지 않습니다.
"=100"은 100인것만 가져옵니다.
71이상이면 ">=71"
100과 같거나 작으면 "<=100" 조건이 필요합니다.
혹시 일자별?함수 합계공식이 가능한지요.
저렇게 일자별로 정리중인데 1월 합계 만 넣고싶은데
시트에 1/12 . 1/13 이런식으로 적다보니.; 그것의 공식은 아예 다른것일까요?
다음과 같이 날짜 조건을 두개 넣으면 됩니다.(시작일보다 크거나 같고 끝날짜보다 작거나 같도록)
=SUMIFS(E4:E11,B4:B11,">="&DATE(2017,7,1),B4:B11,"<="&DATE(2017,7,31)) 혹시 수식이 작동하지 않으면 쌍따옴표를 키보드에 있는 것으로 다시 입력하세요.
함수의 조건에서 꼭 "신촌 아트박스"를 직접 써서 입력해야하나요?
C열 거래처명에 써놓은걸 참조해서 쓸수는 없는건가요?
다른 셀에 있는 값을 참조해서 사용가능합니다.
안녕하세요! 글 보고 이해가 안가서 메일로 문의드렸는데요 회신 가능할까요? 부탁드립니다 ㅠㅠ
메일로 답변드렸습니다^^
혹시 합쳐신 셀을 SUMIFS 조건으로 합산하는 방법이 있나요?
없다면 가능한 방법이 있는지 알수 있을까요?
합쳐진 셀(아마도 병합된 셀인 것 같은데요)도 합산은 되지만 병합된 셀에서 윗부분만 참조합니다. 예를 들어 C4:C5 범위를 병합했을 때 C4에만 값이 있는 것으로 인식하고 C5에는 값이 없는 것으로 인식합니다. 따라서 병합된 셀의 모양에 따라 원하는 결과가 나올 수도 있는 그렇지 않을 수 있습니다.
안녕하세요 어드민님 궁금한 점이 있어서 댓글 남깁니다.
합하고자하는 숫자 범위(셀이 2개일 경우에는)명령어를 어떻게 해야할까요?
예를 들어 y열에있는 숫자와 w열에있는 숫자들 중 ☆의 조건이되는 숫자들끼리 더하고싶은데
어떻게 해야할까요?
설명하신 내용으로는 정확히 어떤 경우인지 알기가 어렵습니다. 파일을 admin@xlworks.net으로 보내주시면 확인하고 답변드리겠습니다.
다른거보면서 낑낑대다가 이거보고 한방에 해결했습니다. 감사합니다.
감사합니다
고민하던 문제 해결했습니다
안녕하세요~! 10000개의 명단의 개당 3000원의 값을 20개씩 자동 합계로 만들수있는 수식이있을까요? 공백같이 포함되어있는데 공백의 값을 빼고 20개의 합계를 자동으로 구하고 싶은데요 어떻게 해야할까요?
a:33000
b:3000
c:6000
d:13000
e:3000
f:53000
g:3000
0
0
h:3000
i:63000
j:83000
A열에 이름, B열에 금액이 있다고 가정하고 3000원인 것만 20개까지 더해서 해당 행에 더한 값을 표시하는 것으로 이해되네요. 더한 값은 C열에 표시한다고 했을 때 C1열에 다음 수식을 입력하고 아래로 자동채우기 해 보세요.
=IF(AND(MOD(SUMIF(OFFSET($B$1,0,0,ROW(),1),3000),60000)=0,(B1=3000)),60000,0)
답변 너무 감사드립니다. 다만.. A열에 값이 1000,6000,50000, 이렇게 다양한값이 존재하는데 20개씩 자동합계 계산이 요 수식이 맞는걸까요? 1~20번의 합계 21~41번의 합계 다만 21~41번에 0이 포함되어있어 0을 포함해서 20개를 계산하면 21~43번까지의 계산값을 자동으로 되게끔 하고픈데 많이 복잡해질까요?
안녕하세요. 답변드린 수식은 3천원이 20개가 되면(즉 6만원이 되면) 20개가 되는 그 행에 합계를 내는 수식으로 정확한 결과가 나오는 수식입니다. 지금 설명하신 내용으로는 정확히 어떤 것을 원하시는지 몰라서 제가 답변드리기 어렵습니다. 정확한 답변을 원하시면 엑셀파일과 함께 구체적인 상황을 admin@xlworks.net으로 보내주세요.
파일 포함해서 메일 전송 드렸습니다~!
만약 진단 기준을 만든다고 할 때,
여러 점수결과 중 기준값 이상인 점수가 2개 이상할 때 진단하려고 하면,
어떤 함수를 써야 할까요? 미리 감사합니다.
점수결과가 A1:A10 범위에 입력되어 있고 기준 점수는 10점이라면 다음과 같이 수식을 입력하면 될것 같습니다.
=IF(COUNTIF(A1:A10,">=10")>=2,"진단","진단안함")
'진단', '진단안함'으로 표시하는 대신 필요한 수식을 넣으셔도 됩니다.
sumifs 함수에서 조건문에 부등호와 셀을 같이 사용하면 그 값을 불러오지 못하네요.
예를 들어 SUMIFS(c1:c10, A1:A10,">F5") 라고 쓰면, A1:A10 값과 F5 셀에 들어있는 값을 비교해 주기를 바랬는데, 실지로는 F5 셀에 있는 값을 사용하지 못하고 결과가 0이 나옵니다. 위에서 "F5" 대신 F5 셀에 들어있는 숫자를 직접 입력하면 제대로 결과가 나옵니다.
이 문제를 어떻게 해결할 수 있는지 가르쳐 주시면 감사하겠습니다.
안녕하세요.
">F5" 로 입력하면 F5셀을 참조하는 것이 아니라 'F5'라는 문자열보다 큰 것이라고 인식합니다. 따라서 결과가 0이 나옵니다.
대신 다음과 같이 입력하면 됩니다.
SUMIFS(c1:c10, A1:A10,">" & F5)