[엑셀] Countifs 함수 - 수식이 입력된 공란을 제외한 결과값이 입력된 셀의 갯수 카운팅

안녕하세요.

행아아빠입니다.

​

오늘은 알고나면 사실은 너무 간단한 #엑셀 내용 입니다.

​

갯수를 카운팅하는 #COUNTIFS #함수

보기에는 공란처럼 보이지만, 사실은 수식이 입력되어 있어 결과값을 공란으로 표기한 것일 수도 있습니다.

이럴때 #COUNT 함수 또는 COUNTIFS 함수를 사용해 카운팅 하려고 할때,

결과값은 공란이지만 수식이 입력되어 있기 때문에 카운팅 갯수가 올라가게 됩니다.

​

오늘 내용도 많은 분들께 도움되시길 바라겠습니다.

도움이 되셨다면 구독과 좋아요, 영상공유 부탁드리겠습니다. 감사합니다.

---

Q.첫번째로,

각 직원의 출/퇴근 시간을 원시자료(세콤)데이터에서 vlookup 함수로 추출하여 표와같이 나타냈습니다.

두번째로 계의 셀에 김희선이사의 출/퇴근시간 값이 나타난 개수를 구하고자 합니다.

(반드시 시간으로 나타낸 값의 개수를 구하고자 합니다. 빈값이나 기타 다른 문자(연차,지각,출장)등은 x)

 

▲ 질문과 함께 위와 같은 그림파일을 첨부해 주셧습니다.

    파일이 아닌 그림파일을 캡쳐해서 확대해서 보여드리니 그림이 깨져서 잘 안보이니 양해 부탁드립니다.

​

    질문과 그림파일의 내용을 요약해보면,

    그림에서 빨간네모박스안에 들어가 있는 출, 퇴근 시간이 입력된 셀의 갯수를 카운팅해서

    그 결과값을 노란색 음영으로 처리한 셀에 입력하고 싶으시다는 내용입니다.

​

    그림파일만 봤을때는 결과값이 "3"이 나와야 겠습니다.

▲ 수식을 적용시켜 보기 위해서 샘플 양식을 만들어 보았습니다.

    M3 셀에 수식을 적용해 결과값이 나오도록 해야 하는데요.

​

    우선은 갯수를 카운팅 해야 하기 때문에 COUNT 함수를 사용해야 할 것 같죠~??

​

    1. 빈 공란은 0으로 카운팅

    2. 데이터가 입력된 셀만을 카운팅이라는 조건이 있으니

    COUNTIF 또는 COUNTIFS 함수를 사용하면 되겠습니다.

▲ 수식을 입력하니

    결과값이 시간형식으로 나오게 되네요!!!

​

    이건 종종 말씀 드렸지만, 표기형식을 숫자 또는 일반으로 바꾸어 주시면 됩니다.

    꼭, 기억해 두세요~!! **표기형식**

▲ 보시는 것처럼

    수식은

    =COUNTIFS(A3:L3,"<>")-COUNTIFS(A3:L3,"><")

    이렇게 입력을 해줬고요,

    "<>"은 참조할 범위내에 텍스트나 숫자, 수식등이 입력되어 있는 모든 셀의 갯수를 카운트 하라는 의미 입니다.

    "><"은 참조할 범위내에 텍스트로 입력된 모든 셀의 갯수를 카운트 하라는 의미가 됩니다.

    다시 정리해보면, 사실 위 그림에서 참조할 범위내에는 시간형식으로만 입력되거나 수식이 입력되는 경우라고 봐야하기 때문에

    =COUNTIFS(A3:L3,"<>") ← 이렇게만 작성해 주셔도 결과값은 "4"로 같은 값이 나오게 됩니다.

    그리고 표기형식을 일반으로 바꾸어 주었더니 결과값이 정수 형태로 바뀌었죠~~??

​

    엇!!!

    그런데.. 입력된 셀의 갯수는 3개인데 결과값은 4로 나옵니다.

  ▲ 보이시나요??

    A3 셀을 보시면 결과값은 공란형태로 나오지만

    수식입력줄을 보면, 수식이 입력되어 있습니다.

​

    질문자 해 주신분께서, 참조할 범위내에 공란을 보이지만, 수식이 입력되어 있다는 추가질문을 주셧기에

    임의로 수식은 입력되어 있지만, 결과값은 공란으로 표기하도록 임의의 수식을 작성해서 넣었습니다.

​

    때문에 수식이 입력된 셀과 시간이 입력된 셀 3개를 더해서 계의 결과값은 "4"가 나오게 되는것이죠!!

​

    그렇기 때문에 무엇이든 입력된 셀의 총 갯수에서 공란으로 표기된 셀의 갯수를 빼주게되면, 원하는 결과값이 나오게 되겠죠~?

▲ =COUNTIFS(A3:L3,"<>")-COUNTIFS(A3:L3,"><")

​    기존에 작성한 수식에서 추가로 공란의 갯수를 카운팅 할 수 있는 수식

    COUNTIFS(A3:L3,"*") ← 이 수식을 빼주시면 원하는 결과값 "3"이 나오게 됩니다.

​    "*" ← 공란을 카운팅 하라는 의미 입니다.

---

이번 내용은 COUNTIFS 함수로 간단하게 작성되는 수식을 빼주기만 하면 결과값이 나오는 단순하면서도

엑셀 함수를 어렵게 생각하기 때문에 생각치 못한 수식이 될 수 있습니다.

​

오늘 내용도 그럼 많은 분들께 도움이 되시길 바라겠습니다.

좋은 하루 되세요~^^!