엑셀 Sumifs함수의 범위를 동적 범위로 수식을 작성 하는 방법.

안녕하세요. 행아아빠입니다.

이번 엑셀 내용에서는 다중 조건의 합계를 계산 하는 Sumifs함수의 참조 범위를 조건에 따라 참조하는 범위를 자동으로 설정해서 결과값을 계산 하는 수식을 작성해 보도록 하겠습니다.

 

참조 하는 범위를 조건에 따라 자동으로 범위를 설정하기 위해서는 Offset함수를 사용해서 동적 범위로 활용한 수식을 작성할 수 있습니다.

 

Offset함수의 사용 방법과 동적 범위로 활용한 예제를 아래 링크된 글을 추가적으로 참고할 수 있습니다.

https://hang-a-fafa.tistory.com/194

---

■ 행아아빠의 Youtube 채널.

● 주요 컨텐츠 주제 - 내일은로또왕 / 내일은엑셀왕

● Youtube 채널 주소 - https://www.youtube.com/@economic-freedom

■ 행아아빠 후원.

● Youtube 채널 맴버십 - https://www.youtube.com/channel/UCPmC4OZu99Rqguwz5QEINjA/join

● Youtube 댓글 후원 - 영상 댓글에서 "Super Thanks" 구매 후 댓글 작성.

● 내일은엑셀왕 Youtube 대표 영상 - https://youtu.be/oYVZHgv_sKM(2023년 요율 적용 자동 급여 계산 서식과 수식 작성 방법.)

 

 

---

다중 조건 합계를 계산 하는 참조 범위를 동적 범위로 수식을 작성 하는 방법 썸네일 이미지.

1. Sumifs함수 수식 구문, Offset함수 수식 구문.

1-1. Sumifs함수 수식 구문.

=Sumifs(sum_rang, criteria_range1, criteria1, criteria_range2, criteria2, ...) sum_rang : 합계를 구하려는 셀의 참조 범위. criteria_range1 : 첫번째 조건과 일치 하는 값을 찾을 참조 범위. criteria1 : 첫번째 조건을 찾을 범위에서 찾고자 하는 조건.

 

1-2. Offset함수 수식 구문.

=Offset(reference, row, cols, [height], [width]) reference : 참조 범위의 시작셀, 또는 기준점이 되는 셀. row: 기준셀로부터 행 방향으로 이동하는 개수. cols : 기준셀과 행 방향으로 이동한 셀로부터 열 방향으로 이동하는 개수. [height] : 기준셀로부터 이동한 행과 열의 셀 주소로부터 범위의 높이 지정하는 선택적 인수. [width] : 기준셀로부터 이동한 행과 열의 셀 주소로부터 범위의 너비를 지정하는 선택적 인수.

 

 

2. 다중 조건 합계를 계산 하는 참조 범위를 동적 범위로 작성하기 위한 예제.

다중 조건 합계를 계산 하는 참조 범위를 동적 범위로 수식을 작성 하기 위한 예제 이미지.

다중 조건의 합계를 계산하는 참조 범위를 동적 범위로 수식을 작성하기 위한 예제로 A열은 나라 이름을 작성하고, B1, C1, D1셀에는 각각 나라 이름을, E, F열에는 A열에 입력한 나라의 남자, 여자 인구 숫자를 입력, B2셀부터 D11셀 안에는 아시아, 유럽, 남미에 해당할 경우 숫자 1을 입력해서 구분 했을때, H열에 아시아의 총 인구, 유럽의 총인구, 남미의 총인구를 I열에 결과값으로 계산 하는 수식을 작성하고자 합니다.

 

따라서 H2셀에 아시아를 입력하면 I2셀에 아시아계 국가들의 남자+여자 인구의 총 합계를 계산 하기 위한 수식을 작성해야 하는데 아시아 국가는 B열에서 체크한 한국, 일본, 중국 3개 나라의 인구를 합산하면 되기 때문에 E열과 F열의 데이터를 합산하는데 B열에서 1로 체크되어 있는 데이터만 합계를 계산 하라는것입니다.

 

H3셀에는 유럽을 입력했기 때문에 합계를 계산할 범위는 E열과 F열은 동일 하지만 유럽 국가를 찾는 범위는 C열이 되고, 남미 국가를 찾는 범위는 D열이 되겠습니다.

 

 

3. 다중 조건의 합계 참조 범위를 동적 범위로 작성해서 결과값 계산 하는 수식.

예제에서 보는것과 같이 조건과 일치 하는 결과값을 계산 하기 위해서 동적 범위로 활용하지 않고 sumifs함수만으로 아시아 국가의 총 인구수 합계를 계산 하는 수식을 I2셀에 작성하면 다음과 같이 작성 할 수 있습니다.

=SUMIFS($E$2:$E$11,$B$2:$B$11,1)+SUMIFS($F$2:$F$11,$B$2:$B$11,1) 또는 =SUM(SUMIFS($E$2:$E$11,$B$2:$B$11,1),SUMIFS($F$2:$F$11,$B$2:$B$11,1))

위와 같이 수식을 작성하는 경우에 H2셀에서 찾을 조건을 유럽으로 변경하게 되면 해당 수식에서 조건을 찾는 범위는 B열로 지정이 되어 있기 때문에 수식에서 참조 하는 범위를 C열로 수정해 줘야만 유럽 국가의 총 인구수 합계를 계산 할 수 있습니다.

 

 

그럼 H열에 찾을 조건을 입력하면 참조 범위가 B열, C열, D열로 자동으로 조건의 범위를 설정해서 총 인구수를 계산 하는 수식은 어떻게 작성 할 수 있을까?

 

이럴때 참조 범위가 조건에 따라서 유동적으로 움직일 수 있는 offset함수를 사용해서 수식을 작성하면 간단하게 작성 할 수 있는데, offset함수를 사용한 수식을 작성하기 이전에 if함수와 sumifs함수로 결과값을 계산하는 수식을 I2셀에 작성해 보면 다음과 같이 수식을 작성 할 수 있습니다.

=IF($H2="아시아",SUM(SUMIFS($E$2:$E$11,$B$2:$B$11,1),SUMIFS($F$2:$F$11,$B$2:$B$11,1)), IF($H2="유럽",SUM(SUMIFS($E$2:$E$11,$C$2:$C$11,1),SUMIFS($F$2:$F$11,$C$2:$C$11,1)), IF($H2="남미",SUM(SUMIFS($E$2:$E$11,$D$2:$D$11,1),SUMIFS($F$2:$F$11,$D$2:$D$11,1)))))

위 수식처럼 if함수를 활용해서 각각의 조건마다 참조할 범위를 지정해줘서 총 인구수 합계를 계산하는 수식을 작성해 줘도 되지만, 예제에서는 아시아, 유럽, 남미 3개의 조건으로 구분되고 데이터의 양이 많지 않기 때문에 어렵지 않게 수식을 작성해 줄 수 있습니다. 그런데 데이터의 양이 많아진다면 모든 데이터의 조건들을 if함수로 일일이 범위를 지정해서 수식을 작성하는것이 어렵거나 수식이 길어지고, 참조 범위가 변경된다면 수식의 참조 범위들을 모두 일일이 수정을 해야 하는 번거로움이 생길 수 있습니다.

 

 

그럼 오늘 엑셀 내용에서 작성해보고자 했던 offset함수로 조건에 따라서 참조 범위가 자동으로 설정되어 결과값을 계산하는 수식을 작성해 보도록 하겠습니다.

=SUM(SUMIFS($E$2:$E$11,OFFSET($A$1,1,MATCH($H2,$B$1:$D$1,0),10,1),1), SUMIFS($F$2:$F$11,OFFSET($A$1,1,MATCH($H2,$B$1:$D$1,0),10,1),1))

위 수식과 같이 작성하게 되면 조건에 따라서 참조 하는 범위를 자동으로 설정해서 총 인구수 합계를 계산 할 수 있습니다.

인구수는 남자와 여자의 합계이기 때문에 E열과 F열을 결과값으로 반환하는 각각의 Sumifs함수를 합산해주기 위해서 Sum함수로 묶어주었고, 조건에 따라서 동적 범위를 설정 하기 위해서  참조 범위에 offset함수로 OFFSET($A$1,1,MATCH($H2,$B$1:$D$1,0),10,1),1) 참조 영역을 작성해 주면 A1셀 기준점으로부터 1행 아래로 이동한 뒤에 MATCH($H2,$B$1:$D$1,0)로 H열에 입력된 찾을 조건을 B1셀부터 D1셀과 일치 하는 지점으로 이동한 뒤셀로부터 높이 10행, 너비 1열을 참조 범위로 설정할수가 있습니다.

 

따라서 H열에 아시아를 입력하면 아시아를 참조 하는 범위 B열을 참조 범위로 자동으로 설정하고, 유럽을 입력하면 유럽을 참조 하는 범위 C열을 참조 범위로 자동 설정해서 다중 조건의 합계를 계산하는 수식을 작성 할 수 있습니다.

 

오늘은 다중 조건의 합계를 계산 하기 위한 참조 범위를 동적 범위로 활용하는 수식을 작성해 보았는데, ① sumifs함수만으로 수식을 작성 해서 계산 하는 방법, ② if함수를 추가로 활용해서 각각의 조건과 일치 하는 참조 범위를 하나씩 지정해주는 방법, 마지막으로 조건만 입력하면 ③ 조건과 일치 하는 참조 범위를 자동으로 설정하는 방법으로 수식을 작성해 보았습니다.

 

만약, 여러분이 Sumifs함수만 알고 있다면 ①번 수식으로도 충분히 결과값을 계산 하는 수식을 작성할 수 있고, If함수까지도 자유롭게 활용할 수 있다면 ①번 수식에 비해서 ②번 수식으로 활용도가 조금더 높고 결과값을 계산하는데 조금 더 편리한 수식을 작성 할 수 있습니다.

여기에 더해서 offset함수까지도 활용한다면 ①번과 ②번 수식에 비해서 훨씬 더 활용도가 높고 자동 서식에 가까운 수식으로 완성 시킬수가 있습니다.

 

오늘 내용을 참고 하셔서 여러분들이 실제로 사용하는 엑셀 서식에서 적용해 보시길 바라겠습니다. 감사합니다.

 

▼ 샘플 예제 서식 다운로드

행아아빠의 내일은엑셀왕 샘플 예제 서식 - 다중 조건 합계를 계산 하는 참조 범위를 동적 범위로 수식을 작성..xlsx

0.01MB

Blog :https://hang-a-fafa.tistory.com

Youtube :https://www.youtube.com/@economic-freedom

Naver 까페 :https://cafe.naver.com/hatime