두조건 이상 (여러조건) 만족 합계 SUMIFS

한개 이상의 조건을 만족하는 것들의 합 SUMIFS
1. 기본 사용법
2. 활용 예시
  ㄴ Raw data에서 요약표 만들기
  ㄴ 월간 품목별 수량 합계 (MONTH, 기간별합계, 피벗테이블)

안녕하세요. 이번에는 여러 조건을 만족하는 항목들의 합계를 구하는 SUMIFS에 대해 알아보겠습니다. SUMIF와 개념은 같아요. 범위내에 이거랑 같은 또는 이 조건을 만족하는 것들을 더해라. 다른 점은 함수 안에 넣는 정보의 순서랑, 조건을 여러 개 넣을 수 있다는 것이죠. 그럼 시작하겠습니다.

SUMIFS 예시.xlsx

1. 기본 사용법

SUMIFS는 더하려는 항목이 한개 이상일 때 쓰입니다. SUMIF는 한개의 조건을 만족하는 것들의 합이었죠? SUMIFS는 조건 한 개여도 되고 여러 개여도 됩니다. 아래 예시처럼 A에 해당하고 X에도 해당하는 항목들의 값들을 더해야 하는 경우 SUMIFS가 사용됩니다. SUMIF와 지정 순서가 다르니 주의해주세요.

방법
1. =SUMIFS( 적으시고
2. 더하려는 값들의 범위를 드래그로 지정해 주세요. D3:D8
   수식 범위 고정을 위해 F4 키를 한번 눌러서 $D$3:$D$8로 만들어주세요.
3. 콤마 적으시고,
4. 첫번째 조건이 있는 범위를 드래그 해주세요. 역시 F4키로 범위를 고정시킵니다.
5. 콤마 적으시고,
6. 첫번째 조건이 있는 셀 클릭, 또는 직접 입력합니다.
   (문자면 "문자", 숫자는 큰따옴표 없이 그냥 숫자로 적어주세요)
조건이 하나면 6번 까지 하시고 엔터!
조건이 여러 개면 3번부터 6번는 반복하시면 됩니다.

2. 활용 예시

Raw data에서 요약표 만들기

SUMIFS로 수 많은 data들 중에서 원하는 data만 빠르게 뽑아낼 수 있어요. 아래 일자별/고객별/제품별 판매 raw data가 있습니다. 이 자료에서 3가지 질문에 SUMIFS를 사용해서 답을 찾아볼게요. 

Q. C-200 판매 수량은?
Q. A 고객사 C-100 판매 수량은?
Q. 월별 A 고객사 C-300 판매 수량은?

첫번째 문제 C-200의 판매 수량은 130 입니다. (초록색)
=SUMIFS(더할 값들의 범위, 조건이 있는 범위, 조건)
=SUMIFS($E$3:$E$10,$D$3:$D$10,$D$4)
조건에는 셀을 선택하셔도 되고 D4 대신에 C-200을 직접 적으셔도 되요.
=SUMIFS($E$3:$E$10,$D$3:$D$10,"C-200")
C-200은 문자니까 꼭 큰 따옴표로 묶어주셔야 해요.
아래는 원하는 조건이 범위 밖 B12셀에 있네요. 조건은 조건 범위 내에 있어도 되고, 밖에 있어도 됩니다. 

두번째는 조건이 2개 입니다. A 고객사에 C-100이 얼마나 팔렸나를 보는거죠.
답은 250입니다. (오렌지)
=SUMIFS(역시 더할 값들의 범위 먼저 적고, 조건1범위, 조건1, 조건2범위, 조건2)
=SUMIFS($E$3:$E$10,$D$3:$D$10,$D$3,$C$3:$C$10,$C$3)
조건을 직접 적으려면 문자이니까 역시 큰 따옴표를 써야해요.
=SUMIFS($E$3:$E$10,$D$3:$D$10,"C-100",$C$3:$C$10,"A")
조건이 왼쪽부터 오른쪽일 필요는 없어요. D열, C열 순으로 해도 상관없습니다.

세번째는 난이도가 좀 있어요. 조건이 3개면 범위 잡고 콤마 찍고 조건 적으면 되는데 항목은 날짜별인데 문제는 월별이라네요... 일단 날짜별 조건해서 SUMIFS 연습 한번 더 해보죠.

10월 06일 A 고객사 C-100의 판매량은?
진한 박스에 해당하는 건데요. 답은 100이죠?
=SUMIFS(더할값들의범위,조건1범위,조건1,조건2범위,조건2,조건3범위,조건3)
=SUMIFS($E$3:$E$10,$D$3:$D$10,"C-100",$C$3:$C$10,"A",$B$3:$B$10,"2020/10/6")
날짜 부분에는 10월 06일만 보이지만 연도 정보가 포함되어 있어요. 그래서 올해가 아닌 다른 년도의 경우 "10/6"만 적으면 값이 안 더해질 수 있으니 "2020/10/6" 처럼 년도를 포함하는 것 주의해 주세요.

---

월간 품목별 합계

위의 세번째 질문에 대한 답을 이제 해볼게요. SUMIFS는 위의 예제들로 이해를 하셨을 것 같은데, 자료 분석하면 꼭 다른 특이사항들이 있잖아요. 그럴 때는 다른 함수들과 함께 사용해야 해요. 그 부분이 엑셀쓰면서 매우 중요한 것 같아요. 예시를 다시 봐주세요. B열은 날짜인데 질문은 월간 합계래요. 총 3가지 방법으로 해결해볼게요.

1) MONTH 함수

표의 좌측이나 우측에 빈 공간에 저 날짜들의 월만 빼낼 수 있으면 바로 SUMIFS 적용이 가능하겠죠? 날짜가 적힌 셀에서 월 정보를 빼내는 함수는 MONTH 입니다.
=MONTH(날짜셀)하시면 날짜의 월 정보가 값으로 나옵니다.

F3 셀에 =MONTH(B3) 라고 적어보세요. 값이 10이 나옵니다. 그리고 밑으로 자동채우기 또는 복사 붙여넣기로 각 행의 월 정보를 적습니다.

위 사진처럼 MONTH 함수를 이용해서 리스트의 오른쪽(F열)에 월 정보를 빼냈어요. 이제 위의 SUMIFS 조건 범위에서 F열을 선택해서 10월 A 고객사 C-300 판매 수량을 계산해보죠.
답은 50이죠? 첫번째 파란색 항목입니다.
=SUMIFS($E$3:$E$10,$D$3:$D$10,"C-300",$C$3:$C$10,"A",$F$3:$F$10,$F$3) 또는
=SUMIFS($E$3:$E$10,$D$3:$D$10,"C-300",$C$3:$C$10,"A",$F$3:$F$10,10)
마지막 3번째 조건 10은 숫자니까 큰 따옴표 없이 적습니다.

---

2) 기간별 합계

Month와 sumifs 로도 충분히 값을 구할 수 있었습니다. 이번에는 조건을 직접 주어 볼게요. 10월 01일보다 크거나 같고 10월31보다 작거나 같은 것들의 합도 10월의 합계와 같은 얘기지요.
=SUMIFS($E$3:$E$10,$C$3:$C$10,"A",$D$3:$D$10,"C-300",$B$3:$B$10,">=2020/10/1",$B$3:$B$10,"<=2020/10/31")
날짜의 범위에서 2020년 10월 1일보다 크거나 같으면서 10월 31일보다 작거나 같은 조건을 추가해 주었습니다. 크고 작은 기호와 날짜들도 문자이기 때문에 큰 따옴표를 사용해 주셔야 해요.
사실 월별 합계는 1번 month 방식이 간단합니다만, 특정 기간의 합계를 구하셔야 하면 이 방법이 더 적절합니다. 기간별 정보칸에 시작시간, 종료시간을 적으면 시간대별 합계를 구할 수도 있겠죠?
아래는 10월 05일부터 (5일포함) 10월 07일 이전 (7일미포함) 기간의 A 고객사에 판매된 C-300 제품의 판매량 합을 구한 것입니다. 기간부분이나 부호를 수정하면 원하는 값을 찾을 수 있어요.

혹시 발견하셨나요? 범위가 E3:E10이 아니고 E:E로 적었어요. 순환참조 문제가 없다면 이렇게 열을 쫙 고를 수도 있습니다. 수식도 짧아지고 나중에 항목이 늘어났을 때 수식 수정 안 해도 되죠. 방법은 열 번호 부분을 클릭하는 겁니다. 범위 드래그 하실 때 드래그 대신에 열번호가 있는 E 를 클릭하시면 되요.

---

3) 피벗테이블

많은 함수를 아는 것도 중요하시만 피벗테이블 하나만 알아도 여러 종류의 원하는 값들을 찾으실 수 있어요.
month 함수 사용해서 월 정보를 빼낸 표에서 전체 범위를 선택해주세요.

그 다음에는 삽입 탭을 누르시고 왼쪽에 있는 피벗테이블 버튼을 눌러주세요.
새창이 뜨는데 바로 확인 누르시면 됩니다. 그러면 새 시트가 열립니다.

새 시트가 열렸죠? 화면 오른쪽을 보시면 위에는 목록 이름들이 있고 오른쪽 아래에는 밑에 사진처럼 영역 부분이 있어요. 위의 목록 이름들을 아래처럼 드래그해서 끌어 넣어주세요. 월 끌어서 행에 넣고, 고객사도 끌어서 행, 제품명도 끌어서 행에 넣으시고, 판매량은 끌어서 값 영역에 넣어주세요.

이렇게 넣으시면 화면 왼쪽에 요약 표가 완성 됩니다. 10월 A고객사의 C-300 판매량의 합계가 바로 나오죠? 그리고 그외 항목 모두 조건에 맞게 합계가 되어 있어요. SUMIFS쓰면서 이 항목 저 항목 다 수식 거는 것보다 피벗 테이블 만들고 필요한 정보 빼 쓰는 것이 더 효율적이지 않나요? 저는 사실 피벗테이블 더 많이 씁니다. 자료 분석할 때 이 항목도 넣어보고, 저 항목도 넣어보고 하면서 data의 경향도 볼 수 있고요.

---

이번에는 SUMIFS 함수의 기본 사용법과 몇가지 예시를 살펴보았어요. 여러 조건을 만족하는 합계.. 많은 data 안에서 필요한 정보만 뽑아주는 아주 유용한 기능이죠. 참고로 여러조건을 만족하는 값의 평균은 AVERAGEIFS 예요. SUM 대신에 AVERAGE만 바꾸면 되고, 범위 선택 등은 동일합니다. 

조건이 많아지니 수식이 길어져서 읽기가 힘드셨을텐데 끝까지 봐주셔서 감사합니다. 즐거운 하루 보내세요~~  

[관련 정보]
2020/10/12 - [Excel_Formula] - SUMIF 더하기 (항목/날짜/조건별 합계)