일정한 조건에 해당하는 합계 구하기

엑셀 부분 합계.xlsx

0.01MB

 

 

1. 문제

A셀의 문자 길이가 4인 경우 B셀의 값을 합계하는 것입니다.

 

 

2. 해결 방안

다양한 해결방안이 있을 수 있습니다.

 

① A열의 코드를 살펴보니 A3세의 값이 5116인 경우 A4셀부터 5116으로 코드가 시작되므로 4개를 잘랐을 때 코드 값이 같은 것을 더할 수도 있고,

 

② 코드 길이가 7자리에서 4자리로 바뀔 때, 다시 말해 코드의 길이가 4인 것 전까지 더할 수도 있습니다.

 

그리고, 함수도 Sum과 If 배열 수식을 이용할 수도 있고, SumIf를 이용할 수도 있고, sumProduct, 그리고, Offset과 Match함수를 이용할 수도 있습니다.

 

(1) Sum과 If 배열 수식을 이용하는 경우

 

=SUM(IF(LEFT($A$4:$A$24,4)=A3,$B$4:$B$24,0))

A열의 왼쪽 4글자를 가져와서 A3와 일치하면 B열의 값을 반환하고, 아니면 0을 반환하므로 그 값을 더하면 됩니다.

 

그런데, 코드가 4글자인 것에만 값을 표시해야 하므로

=IF(LEN(A3)=4, 조건을 추가해서 길이가 4이면 합계를 출력하고 아니면 0을 출력하도록 해야 합니다.

=IF(LEN(A3)=4,SUM(IF(LEFT($A$4:$A$24,4)=A3,$B$4:$B$24,0)),0)

 

0이 보기 싫으면 ""으로 바꿔도 됩니다.

 

 

(2) SumIf(s) 함수를 사용하는 경우

SumIfs함수는 조건을 여러 개 줄 수 있고, 더할 범위를 첫 번째 인수로 지정하는데,

SumIf함수는 조건을 하나만 지정할 수 있고, 더할 범위를 세 번째 인수로 지정하는 차이점이 있습니다.

 

=IF(LEN(A3)=4,SUMIF(LEFT($A$4:$A$24,4),A3,$B$4:$B$24),0)

라고 하면 될 듯한데,

 

"수식에 문제가 있습니다."란 에러 메시지 창이 표시되면서 수식이 입력되지 않습니다.

 

 

따라서, & 결합 연산자와 * 와일드카드 문자를 이용해서

=IF(LEN(A3)=4,SUMIF($A$4:$A$24,A3&"*",$B$4:$B$24),0)라고 입력해야 합니다.

 

 

SumIfs함수를 이용할 때는 아래와 같이 더할 범위와 조건 범위, 조건의 위치만 바꾸면 됩니다.

=IF(LEN(A3)=4,SUMIFS($B$4:$B$24,$A$4:$A$24,A3&"*"),0)

 

(3) SumProduct 함수를 사용하는 경우

SumProduct함수는 배열끼리 곱한 후 값을 반환하는 함수이지만,

배열을 조건 식으로 바꿔서 합계를 구할 때 사용할 수 있습니다.

 

수식은

=SUMPRODUCT((LEFT($A$4:$A$24,4)=A3)*($B$4:$B$24))

입니다.

 

이 수식의 의미는 A열의 왼쪽 4개가 A3셀과 같을 때 일치하는 B4셀에서 B24셀의 값을 더하는 것입니다.

 

(4) SumIfs와 Offset, Match 함수를 이용하는 경우

질문자의 의도가 코드의 길이가 7에서 4로 바뀌기 전까지만 더하는 것이므로

Match함수를 이용해 글자가 4개로 바뀌는 위치를 찾은 다음

Offset 함수를 이용해 더할 범위를 지정하기 위해

(기준 셀, 행 길이, 열 길이, 높이, 너비)를 입력하는데,

 

기준 셀을 B4셀이 되고, 행 길이와 열 길이는 기본 값이 0이므로 생략해도 되고,

높이는 Match함수로 찾은 값에서 1을 뺀 값이 되고,

너비는 1이므로 마찬가지로 생략해도 됩니다.

 

따라서, 수식은

=IF(LEN(A3)=4,SUM(OFFSET(B4,,,MATCH(4,LEN($A$4:$A$24),0)-1)),0)

이 됩니다.

 

그런데 B22셀의 값을 30으로 바꿔도 이 수식의 결과만 #N/A 에러 값입니다.

 

이는 더할 범위가 코드의 길이가 4인 것을 찾아서 그전까지 더하는 것인데,

5121 아래에는 네 자기 코드가 없어서 Match 수식의 결과가 없어서 #N/A! 에러가 발생하는 것입니다.

 

따라서, IfError 함수를 이용해서 에러일 때 값을 지정해야 합니다.

 

=IF(LEN(A3)=4,SUM(OFFSET(B4,,,IFERROR(MATCH(4,LEN(A4:$A$24),0)-1,10))),0)

 

그러니까 값 30이 제대로 나왔습니다.

 

 

엑셀 부분 합계(완성).xlsx

0.01MB