포함하는 문자와 제외할 문자를 적용한 SumIfs 수식

 

1. 문제

 

sumifs(포함,제외)(문제).xlsx

0.01MB

 

D열에 E17셀 값이 들어 있고, i열의 값은 포함하지 않는 F열 값의 합계를 구하는 문제입니다.

 

 

2. 해법 1(실패)

 

가. SumIfs 함수의 구문과 화면의 재구성

SumIfs함수의 구문은

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

이므로

더할 범위는 F열이 되고,

비교할 범위는 D열과 i열이 되고,

기준은 E17, E18셀과 i열의 값 "유"가 됩니다.

 

따라서, 제외할 값을 D17셀과 D18셀에 입력하는 것이 더 나은 방법입니다.

 

그렇다면 아래와 같은 화면이 됩니다.

 

 

나. 수식

 

F17셀의 수식은

=SUMIFS($F$6:$F$14,$D$6:$D$14,E17,$I$6:$I$14,"<>"&D17)

이 됩니다. 

 

"유"가 있는 행을 제외하니 F6셀 10 + F10셀 30 + F12셀 40을 더하니 80 맞습니다.

 

F17셀의 채우기 핸들을 끌어 F18셀에 수식을 붙여 넣으면

값이 이상하게 0이 나옵니다.

 

 

3. 해법 2(성공)

 

가. 원인 찾기

원인을 찾기 위해

i20셀에 =$I$6:$I$14<>""라고 입력하면

 

빈칸이 False로 나오고, "유"가 있는 행이 True로 "유"가 아닌 것과 결괏값이 같습니다.

 

그래서 <> 다음의 ""(빈 셀)을 " "(공백 한 칸)로 바꾸면 모두 True로 잘 나옵니다.

 

 

나. 수식 변경

$I$6:$I$14,"<>"&D18을

 

길이가 0일 때, 다시 말해 빈 셀일 때는  공백 한 칸(" ")으로 하고,

아니면 D18셀 값을 가져오도록 

 

$I$6:$I$14,"<>"&IF(LEN(D18)=0," ",D18)라고

수정하면 됩니다.

 

수정된 수식은

=SUMIFS($F$6:$F$14,$D$6:$D$14,E18,$I$6:$I$14,"<>"&IF(LEN(D18)=0," ",D18))이고

결괏값도 '나'인 것의 합, 20 + 20 + 40 + 40 = 120 맞습니다.

 

F18셀의 채우기 핸들을 위로 끌어 F17셀에 복사하면

F17셀의 값도 80으로 당초 수식의 값과 일치합니다.

 

sumifs(포함,제외)(완성).xlsx

0.01MB