엑셀 자동 문서 서식을 작성하면서 합계외에도 개수를 결과값으로 반환하기 위한 수식을 작성 해야 하는 경우도 매우 많을것입니다.
그러나 단순히 참조 범위의 셀 개수를 세우는 경우보다는 참조 범위 내에서 조건을 만족하는 셀의 개수를 세우고자 하는 경우가 실제 업무에서 필요로 하는 경우가 많다고 할것입니다.
단일 조건 개수, 다중 조건 개수를 세워서 결과값으로 반환하는 countif, countifs함수 수식을 작성하면 조건을 만족하는 개수를 결과값으로 반환할 수 있지만 이번 글에서는 공백 개수는 제외한 실제 데이터가 입력된 셀 중에서 조건을 만족하는 개수를 결과값으로 반환하는 수식을 작성해 보도록 하겠습니다.
countif, countifs함수로 공백을 제외한 실제 데이터가 입력된 셀의 개수만 세우기 위한 수식을 작성하기 위해서 위 이미지에서 보는것과 같은 예제 데이터를 간단하게 입력 했습니다.
공백인 셀은 A2, D2, E2셀로 3개, B2, C2셀에는 09:00, 18:00의 시간이 입력된 셀 2개입니다.
단, A2셀은 공백인 셀 이지만 =IF(A3=””,””) 수식을 작성하여 결과값이 공백으로 반환 하도록 표현한 셀입니다.
수식이 입력되어 있더라도 결과값은 공백을 반환하고 있기 때문에 공백을 제외한 입력된 셀의 개수는 2개, 공백인 셀의 개수는 3개여야합니다.
공백을 제외한 데이터가 입력된 셀의 개수.
count, counta, countblank.
예제에서 공백을 제외한 데이터가 입력된 셀의 개수를 결과값으로 반한하기 위해서는 개수를 세우는 기초 함수 count함수로 수식을 작성 할 수 있습니다.
=COUNT(A2:E2)로 수식을 작성하면 비어 있지 않는 셀의 개수는 2개를 결과값으로 반환합니다.
사실상 공백을 제외한 데이터가 입력된 셀의 개수인 2개를 결과값으로 반환하는것으로 볼 수 있습니다.
범위에서 비어 있지 않는 셀의 개수를 세워보기 위해 counta함수 =COUNTA(A2:E2)수식으로 작성해 보면 2개의 결과값이 아닌 3개로 결과값이 반환됩니다.
비어 있는 셀의 개수 countblank함수 =COUNTBLANK(A2:E2) 수식을 작성해 보면 3개를 결과값으로 반환합니다.
알 수 있는것은 counta함수에서는 공백으로 결과값을 반환한 셀의 경우라도 수식이 입력되어 있는 셀이라면 공백으로 인식하지 않고 데이터값이 입력된 셀로 인식한다는것을 알 수 있습니다.
수식의 결과값이 공백인 셀의 개수.
count, counta, countblank 3개의 함수로 간단하게 결과값을 확인해 봤을때 count함수만으로도 수식이 입력된 결과값이 공백일 경우라도 모두 공란으로 인식하여 실제 데이터 값이 입력된 셀의 개수만을 세워서 결과값으로 반환할 수 있습니다.
그렇다면 수식의 결과값이 공란을 반환하는 셀의 개수는 어떻게 계산할 수 있을까?
비어 있지 않는 셀 개수 – 데이터값이 입력된 셀 개수 또는 비어 있는 셀의 개수 – 데이터값이 입력된 셀 개수로 계산할 수 있습니다.
이러한 계산식을 수식으로 작성해 보면 다음과 같습니다.
| =COUNTA(A2:E2)-COUNT(A2:E2) 또는 =COUNTBLANK(A2:E2)-COUNT(A2:E2)
위 수식의 결과값이 음수값으로 나오는 경우가 있다면 아래와 같이 수식을 수정할 수 있습니다. =IF(COUNTA(A2:E2)-COUNT(A2:E2)<0,(COUNTA(A2:E2)-COUNT(A2:E2))*-1,COUNTA(A2:E2)-COUNT(A2:E2)) 또는 =IF(COUNTBLANK(A2:E2)-COUNT(A2:E2)<0,(COUNTBLANK(A2:E2)-COUNT(A2:E2))*-1,COUNTBLANK(A2:E2)-COUNT(A2:E2)) |
위 수식에서 알 수 있듯이 실제 입력된 데이터값중에서 수식이 작성되어 공란을 결과값으로 반환하는 계산식 자체는 유사하다고 할 수 있지만 어떤 기준으로 개수를 세울것인지에 따라 사용하는 함수가 달라진다고 할 수 있겠습니다.
공백을 제외하고 데이터가 입력된 셀의 개수.
countifs.
위에서 언급했지만 수식의 결과값이 공백을 반환하고 있더라도 애초에 count함수 자체만으로도 공백을 제외한 데이터가 입력된 셀의 개수를 결과값으로 반환할 수 있습니다.
그러나 우리가 실제로 사용하는 엑셀 데이터에서는 다양한 형태로 작성되어 있을 수 있고, 공백을 제외하고도 추가 조건이 있을 수 있다는점에서 단일 조건, 다중 조건 개수를 세우는 형태의 함수로도 공백을 제외한 개수를 결과값으로 반환하는 수식을 작성해볼 필요가 있습니다.
이전 글에서 봤듯이 (엑셀 조건을 만족하는 합계 sumifs함수 수식.) 다중 조건 합계 sumifs함수는 단일 조건 sumif함수를 포함하고 있듯이 조건을 만족하는 개수를 세우는 함수 countif, countifs함수 역시도 다중 조건 개수 countifs함수가 단일 조건 개수 countif함수를 포함하고 있기 때문에 countifs만으로 단일 조건과 다중 조건의 개수를 모두 만족 하는 함수로 사용할 수 있습니다.
다만 sumif, sumifs함수에서는 수식 구문의 차이로 결과값을 반환할 범위를 첫째 인수로 작성하는지, 마지막 인수로 작성하는지 달라지지만 countif, countifs함수의 수식 구문에는 그 차이가 없다고 할 수 있습니다.
countifs함수를 사용해서 공백을 제외한 셀 개수를 세우기 위한 수식을 다음과 같이 작성해 볼 수 있습니다.
| countifs함수 수식 구문은 =countifs(조건을 찾을 참조 범위1, 찾을 조건1, 조건을 찾을 참조 범위2, 찾을 조건2,…)으로 작성할 수 있습니다.
=COUNTIFS($A$2:$E$2,”<>”) → 참조 범위 내에서 찾을 조건 “<>”은 텍스트, 숫자, 수식의 결과값이 공란이더라도 데이터가 입력된 모든 셀의 개수를 포함해서 세우는 조건을 찾아 계산하는것으로 결과값은 A2셀을 포함해서 3을 반환하게 됩니다. =COUNTIFS($A$2:$E$2,”><“) → 참조 범위 내에서 찾을 조건 “><“은 텍스트로 입력된 모든 셀의 개수를 찾아 계산하는것으로 예제에서는 텍스트로 입력된 데이터가 없기 때문에 결과값은 0을 반환하게 됩니다. =COUNTIFS($A$2:$E$2,”*”) → 참조 범위 내에서 찾을 조건 “*”은 참조 범위 내에서 데이터가 입력되어 있지 않은 셀을 제외한 수식등의 결과값으로 공란을 반환하고 있는 셀의 개수를 계산하는 조건입니다. 따라서 F2셀에 공란을 제외한 데이터가 입력된 셀의 개수만을 세우기 위한 계산 수식은 다음과 같이 작성할 수 있습니다. =COUNTIFS($A$2:$E$2,”<>”)-COUNTIFS($A$2:$E$2,”><“)-COUNTIFS($A$2:$E$2,”*”) |
글을 마치며…
엑셀에서 개수를 세우기 위한 함수는 기본적으로 count함수를 사용할 수 있지만 숫자 이외의 셀 개수는 계산할 수 없습니다.
비슷하게 counta, countblank와 같은 함수들은 특정적으로 지정하고 있는 표시 형식에 대해서만을 찾을 수 있기 때문에 사용자 자동 서식에서 주로 조건을 만족하는 값을 계산하고자 하는 문서에서의 사용은 한계가 있다고 할 수 있습니다.
따라서 조건을 만족 하는 개수를 세우기 위해서 countifs함수를 사용해서 수식을 작성할 수 있지만 찾을 조건으로 “<>“, “><“, “*“의 작동 방식을 알고 있다면 조금 더 사용자 자동 서식의 활용도를 높일 수 있을것입니다.
