countifs함수는 조건을 만족하는 개수를 세우는 함수로 자주 사용되는 함수입니다.
함수 수식 구문을 보면 다음과 같습니다.
| countifs(참조범위1, 조건1, 참조범위2, 조건2, …) |
수식 구문 역시도 단조롭기 때문에 누구나 어렵지 않게 수식을 작성할 수 있고 countifs함수 조건이 여러개일 경우 반듯이 and조건을 만족하는 경우 결과값을 반환하는 구조라고 볼 수 있습니다.
그러나 동일한 참조범위에서 조건1,조건2,조건3…과 같이 복수조건을 찾을 경우 countifs(참조범위1,조건1,참조범위1,조건2,참조범위1,조건3)로 작성하면 그 결과값은 어떻게 될까? 아마도 원하는 결과값이 나오지는 않을것입니다.
이번 글에서는 이렇게 동일한 1개의 참조범위에서 복수조건을 만족하는 개수를 세우기 위해서 수식을 어떻게 작성해야 하는지 확인해 보도록 하겠습니다.
Sumifs함수 역시 매우 자주 사용되는 함수로 sumifs함수의 복수 조건을 만족하는 내용도 함께 확인해 보시면 좋겠습니다.
예제.
예제는 간단하게 작성해 봤습니다. A열에 연차, 보건, 월차, 해외출장, 연수 텍스트 값을 입력하고 C2셀에 연차, 보건, 월차 텍스트가 입력된 개수를 수식을 작성해서 결과값을 가져오려고 합니다.
countifs 동일한 참조 범위내 복수조건을 만족하는 수식.
예제 데이터에서 연차, 보건, 월차 입력 데이터 개수를 세워보면 3개로 참조 범위는 A열로 A2:A6 범위입니다.
따라서 countifs기본 수식 구문으로 수식을 작성해 보면 =countifs(A2:A6,”연차”,A2:A6,”보건”,A2:A6,”월차”)로 작성할 수 있지만 결과값은 0으로 나오거나 원하는 결과값이 나오지는 않습니다.
동일한 참조범위에서 여러 조건을 1개의 수식에서 작성하지 못한다는것인데 다중조건으로 작동하기 위해서는 참조범위 역시 조건을 찾을 범위가 다중 범위여야 하는것으로 생각해 볼 수 있습니다.
동일한 참조 범위에서 복수조건의 개수를 결과값으로 반환 하기 위해서는 결국 단일 조건을 하나씩 찾아서 합산해 주면 결과값을 가져올 수 있습니다.
| =COUNTIFS($A$2:$A$6,”연차”)+COUNTIFS($A$2:$A$6,”보건”)+COUNTIFS($A$2:$A$6,”월차”)
위 수식을 조금 더 단순화 시켜 보면 다음과 같이 작성해 볼 수 있습니다. =SUM(COUNTIFS($A$2:$A$6,{“연차”,”보건”,”월차”})) → 찾을 조건을 배열 인수로 작성해서 수식을 단순화 시켜볼 수 있습니다. sum함수는 sumproduct함수로 수정해서 작성해도 동일한 결과값을 반환할 수 있습니다. |
위 수식에서 볼 수 있듯이 복수 조건을 배열 인수로 작성하는것으로 동일한 참조 범위내에서 복수 조건 개수를 계산할 수 있는데 찾을 조건을 배열로 작성하면 찾을 조건의 값을 하나씩 입력시켜줘야 하기 때문에 찾을 조건이 수정되면 매우 불편할 수 있습니다.
이러한 경우 찾을 조건을 F2셀부터 F4셀에 연차, 보건, 월차 순서로 작성후에 이름관리자 “복수조건”의 이름으로 설정하면 {“연차”,”보건”,”월차”} 배열로 작성한 찾을 조건 인수 대신에 이름관리자에서 설정한 “복수조건“이라는 이름으로 대체시켜서 수식을 작성하면 수식을 더 단순화 시킬 수 있습니다.
찾을 조건 값의 표를 이름관리자로 설정한 후 수식을 작성하면 다음과 같습니다.
| =SUM(COUNTIFS($A$2:$A$6,복수조건)) → X
=SUMPRODUCT(COUNTIFS($A$2:$A$6,복수조건)) → O |
찾을 조건의 범위를 이름관리자로 설정하여 수식을 다시 작성해 보면 sum함수 수식에서는 잘못된 값을 반환하게 되고, sumproduct함수로 작성한 수식에서만 원하는 결과값을 반환하게 되는것을 확인할 수 있습니다.
sumproduct함수는 인수의 배열을 찾아 합계를 구하도록 작동하는 함수이기 때문에 sumproduct함수에서만 작동하는것으로 생각해 볼 수 있습니다.
이렇게 이름관리자로 찾을 조건의 이름을 지정해 놓으면 찾을 조건의 개수에 따라 이름관리자에서 설정한 범위만 변경해 주거나 조건을 수정해주는것만으로도 수식을 건들지 않고 결과값을 반환할 수 있어 수식을 조금 더 효율적으로 활용해 볼 수 있습니다.
글을 마치며…[행아아빠]
본문에서 countifs 동일한 참조범위 내에서 복수 조건을 만족하는 개수 수식을 작성해 봤는데 알고나면 생각보다 간단하게 작동하는것을 알 수 있습니다.
수식의 작성 방법에는 방법이 정해져 있는것이 아니라 자신이 알고 있는 함수로부터 하나씩 접근해서 수식을 작성하는것이 중요하고 수식이 길어지거나 수식의 인수를 직접 수정해줘야 하는 번거로움이 생긴다면 오히려 더 불편한 서식이 될것입니다.
따라서 수식이 길어지더라도 직접 수식을 작성해보는것이 중요하고 작성한 수식을 조금 더 단순화 시킬수 있는 함수와 수식을 생각해본다면 매우 훌륭하고 효율적인 자동 문서 서식을 만들 수 있을것입니다.
