엑셀(Excel) sumifs or and 다중조건 합계의 일부 조건이 or함수 또는 And함수로 작성 해야 할때 수식 작성을 해보자.

안녕하세요. 일상에서 얻는 정보를 모두 기억할 수 없기 때문에 일상에서 일어나는 에피소드들의 정보들을 기록하여 기억하고자 하는 시점에 기록된 정보를 활용할 수 있도록 글을 쓰고자 하는 이작가입니다.

저 역시 지나간 모든 과거의 경험들을 전부 기억할 수 없기 때문에 경험을 통해 얻은 지식과 정보를 기록함으로써 필요한 시점에 과거의 경험의 정보를 활용해 보기 위해 이 글을 작성합니다.

기록된 정보들이 많은 분들에게 도움이 될 수 있도록 전달될 수 있길 바라겠습니다.

---

목차

 

 

sumifs함수의 조건이 or 또는 and조건일때 수식 작성 방법 썸네일 이미지.

엑셀(excel) 데이터 작업을 하면서 정말 많이 사용되는 함수 sumifs함수로 다중 조건의 합계를 계산 하는 함수 입니다.

합계 함수는 sum, sumif, sumifs함수가 있는데 그중에서도 sumif, sumifs함수는 조건이 있는 합계를 계산 할때 사용 함수이고, sumif함수는 단일 조건의 합계를 계산, sumifs는 다중 조건의 합계를 계산 하죠.

 

sumif와 sumifs의 수식 구문 작성에도 약간의 차이가 있는데 단일 조건 합계 역시 sumifs함수 다중 조건 합계 함수로도 수식 작성이 가능 하고 동일한 결과값을 반환하여 가져 오기 때문에 필자는 sumifs함수로 통일해서 사용하는것을 선호 합니다.

 

앞서 작성한 "분리된 텍스트 조합의 조건 합계를 단일 텍스트 조건 합계로 계산 하는 수식 작성 - 셋트 상품 또는 제품의 개수를 단일 상품 또는 제품 개수 합계로 계산 하는 수식 작성 방법."의 글과 함께 오늘 내용도 같이 확인해 보시면 sumifs함수를 기본 계산 사용 방법보다 좀 더 다양한 형태로 계산 수식을 작성 할 수 있을것 같습니다.

분리된 텍스트 조합의 조건 합계를 단일 텍스트 조건 합계로 계산 하는 수식 작성을 해보자.(셋트 상품의 개수를 단품 상품의 개수 합계로 계산 하는 수식 작성 방법.)

오늘 sumifs함수로 작성해볼 수식은 sumifs함수의 합계를 계산 할 조건이 or함수 또는 and함수로 작성되면 좋을것 같은 수식인데 막상 sumifs함수의 인수를 or함수 또는 and함수로 작성하게 되면 결과값이 0이거나 오류값을 반환하여 표기 됩니다.

 

그럼 sumifs함수의 조건을 or함수 또는 and함수와 같이 사용해서 계산 하는 수식 작성 방법 확인해 보시죠.

 

1. sumifs함수의 특정 조건이 or조건 또는 and조건일때 수식 작성.

sumifs함수의 조건이 or 또는 and조건일때 수식 작성 방법 샘플 예제 서식 이미지.

sumifs함수의 조건이 or조건 또는 and조건일때 수식을 작성하여 계산된 결과값을 확인해 보기 위해 작성한 이미지를 보게 되면 A열부터 C열에 각각 이름, 구분(A,B,C), 금액을 입력한 데이터가 있고 이 데이터를 참조 영역으로 G열에 이름과 구분 2가지 조건을 만족하는 데이터의 합계를 결과값으로 표기 하고자 하는것입니다.

 

조건1에는 이름이 일치해야 하고, 조건2에는 A또는B, B와C, A그리고C의 조건으로 무작위로 구분 조건을 or, and조건과 같이 입력을 하고, G열에는 조건1과 조건2를 만족하는 합계가 결과값으로 나오도록 수식을 작성합니다.

 

만약 조건2가 a, b, c로 조건1에도 조건 1가지, 조건2에도 조건1가지라면 단순하게 sumifs함수로 =SUMIFS($C$2:$C$12,$A$2:$A$12,$E3,$B$2:$B$12,$F3)의 수식을 G3셀에 작성할 수 있겠죠.

 

하지만 조건2를 보게 되면 이름은 글쓰는과 일치 하면서 구분 데이터는 A이거나 B라면 그 금액의 합계를 계산 하는것이기 때문에 =SUMIFS($C$2:$C$12,$A$2:$A$12,$E3,$B$2:$B$12,$F3)수식에서 F3셀을 or(A,B) 또는 and(a,b)와 같은 형태로 작성 하고 싶지만 실제로 or함수와 and함수로 수식을 작성 하게 되면 올바른 결과값을 가져올 수 없습니다.

 

1-1. sumifs함수 or조건 수식 작성 방법 첫번째.

따라서 G3셀의 수식은 다음과 같이 작성 하고 계산이 필요한 행까지 수식을 복사하여 사용 할 수 있습니다.

=SUMPRODUCT(SUMIFS($C$2:$C$12,$A$2:$A$12,$E3,$B$2:$B$12,{"a","b"}))

위와 같이 작성 하게 되면 C열의 합계를 계산 하는데 A열은 E열의 값과 일치하고, B열은 A이거나 B일때 금액의 합계를 계산 하라는 의미가 되는것입니다.

 

수식은 예제 수식 이외에도 다양하게 작성 할 수 있는데 예제 수식 이외의 수식으로 작성을 해 보면 다음과 같은 수식들로 변경해서 작성 할수도 있습니다.

① =SUM(SUMIFS($C$2:$C$12,$A$2:$A$12,$E3,$B$2:$B$12,{"a","b"})) ②=SUMIFS($C$2:$C$12,$A$2:$A$12,$E3,$B$2:$B$12,"a") +SUMIFS($C$2:$C$12,$A$2:$A$12,$E3,$B$2:$B$12,"b")

①번의 경우에는 sumproduct함수를 sum함수로 변경해서 a와 b의 or조건의 배열을 합계를 계산 하는것으로 큰 의미는 없을것입니다. 따라서 sum함수와 sumproduct함수를 혼용해서 사용해도 무방할수 있습니다.

 

②번의 경우에는 단순히 or조건을 각각의 조건별로 합계 금액을 계산 한 후에 각각의 합계 금액을 합산해 주는 계산 수식으로 만약 or조건이 많아진다면 수식이 길어지게 되어 단순하지만 길게 작성되어 헷갈리거나 복잡한 형태의 수식으로 보여질 수 있습니다.

 

다만, 수식을 작성하기 위한 조건이 A+B, B+C, A+C와 같이 각각의 조건이 서로 다르기 때문에 {"a","b"}부분의 or조건을 {"b","c"}, {"a","c"}와 같이 각각의 or조건과 일치 하도록 변경해 주어야 한다는 번거로움의 단점 역시 존재 할 수 있습니다.

 

1-2. sumifs함수 or조건 수식 작성 방법 두번째.

그러면 or조건의 다중 조건 합계를 계산 하기 위한 수식을 조건에 따라 계산 해야 할 모든 셀의 조건들과 일치 하도록 수정해 주어야 할까? or조건의 서식을 변경해서 사용하면 {"b","c"}와 같은 조건을 직접 입력 하지 않고도 작성 할 수 있는 수식을 완성 시킬 수 있습니다.

sumifs함수의 조건이 or 또는 and조건일때 수식 작성 방법 샘플 예제 서식 두번째 이미지. 수식의 직접 수정 없이 결과값을 반환 할 수 있도록 I열부터 L열까지 추가 하였다.

1-1번에서 G열의 수식을 작성 한 후에 조건2와 일치 하도록 수식의 일부분 or조건 {"b","c"}를 {"a","b"},{"a","c"}와 같이 조건과 일치되도록 직접 수정을 해 주어야 합니다.

 

하지만 I열부터 L열까지처럼 조건2의 or조건을 각각 다른 셀에 입력하여 L열에 수식을 작성 하게 되면 수식을 좀 더 간결하고 {"b","c"}의 or조건을 수식에서의 직접 수정 없이 J열과 K열에서의 수정으로 계산 될 수 있는 수식을 작성 할 수 있습니다.

 

L3셀에 수식을 다음과 같이 작성하고 계산이 필요한 행까지 수식 복사 하여 사용 할 수 있습니다.

=SUMPRODUCT(SUMIFS($C$2:$C$12,$A$2:$A$12,$E3,$B$2:$B$12,$J3:$K3))

해당 수식을 보면 1-1에서 or조건을 {"b","c"}와 같이 작성 했던것과는 다르게 J3:K3으로 or조건을 작성 하여 J열과 K열을 참조 하여 계산 할 수 있도록 수정된것을 확인 할 수 있습니다.

---

이상으로 "글쓰는이작가의 엑셀(Excel) 시간"은 마무리 하고,
"sumifs함수의 조건이 or 또는 and조건일때 수식 작성 방법" 예제 샘플을 하단에 첨부하였으니 필요한곳에서 활용할 수 있는 수식으로 적용해 보시길 바라겠습니다.

긴 글 읽어주신 여러분들께 감사 말씀 드리며 많은 도움이 될 수 있기를 바라겠습니다.

 

※ "sumifs함수의 조건이 or 또는 and조건일때 수식 작성 방법" 샘플 예제 다운로드.

글쓰는이작가 샘플 예제 - sumifs함수의 조건이 or 또는 and조건일때 수식 작성 방법 샘플 예제 서식..xlsx

0.01MB