COUNTIF 함수와 AND OR 함수 함께쓰기
{ COUNTIF 함수와 AND OR 함수 함께 쓰기 }
COUNTIF 함수로 조건에 맞는 개수를 세다 보면
조금 복잡한 조건을 만족하는 경우의 수를 세어야 할 때가 있습니다
그럴 때 흔히
COUNTIF 함수와 AND
또는
COUNTIF 함수와 OR
함수를 중첩해서 사용하고 싶은데
이 함수들은 중첩해서 사용할 수
없습니다
COUNTIF 와 AND OR 함수는 중첩해서 사용할 수 없기 때문에
COUNTIF AND 함수를 함께 사용해야 할 곳에는 배열함수나 SUMPRODUCT 함수를 사용해야 하고
COUNTIF OR 함수를 함께 사용해야 할 곳에서는 COUNTIF 함수를 두 번 사용하면 됩니다
COUNTIF 함수와 OR 함수의 중첩 사용방법은 불가능 하기 때문에
COUNTIF 함수와 OR 함수의 중첩사용과
같은 결과를 가져오는 방법은
=COUNTIF(범위,조건) + COUNTIF(범위,조건)
입니다
여러 가지 조건 중에서 하나만 만족하는 경우의 수를 셀 때 사용하게 되는데
국어 점수가 90점 이상이거나
영어 점수가 90점 이상이거나
수학 점수가 90점 이상인 학생의 수를 구하려고 합니다
=COUNTIF(B3:B17,">=90")+COUNTIF(C3:C14,">=90")+COUNTIF(D3:D14,">=90")
와 같이 수식을 구성하면 됩니다
COUNTIF 함수와 AND 함수는 중첩해서 사용할 수 없으므로
배열함수를 사용해야
합니다
두 가지 조건을 모두 만족하는 경우의 수를 셀 때는
=SUMPRODUCT((범위1=조건1)*(범위2=조건2)*(범위3=조건3).....)
또는
{=SUM((범위1=조건1)*(범위2=조건2)*(범위3=조건3).....)}
입니다
배열함수와 SUMPRODUCT 함수가 어떻게 결과를 얻는지 간단히
설명하겠습니다
=SUMPRODUCT((B3:B14>=90)*(C3:C14>=90)*(D3:D14>=90))
{=SUM((B3:B14>=90)*(C3:C14>=90)*(D3:D14>=90))}
(B3:B14>=90) 해당 배열에서 90점과 같거나 이상인 수에 대해 TRUE와 FALSE 값을 반환해
배열로 저장합니다
마찬가지로
(C3:C14>=90)
(D3:D14>=90)
배열도 TRUE와 FALSE 값을 배열로 저장합니다
(B{{
배열함수에서
TRUE는 1 FALSE는 0 이므로
1과 0으로 변환한
뒤
수식대로
=SUMPRODUCT((B3:B14>=90)*(C3:C14>=90)*(D3:D14>=90))
배열끼리 곱해주면 그림과 같은 결과가 나옵니다
마지막으로 SUMPRODUCT 함수 (또는 SUM) 함수로 결과값을 모두 더해주면
2가 나오게 됩니다
countif, countifs
범위에서 조건에 맞는 합계를 반환합니다.
▷ 함수 구문
countif(A1:A10, "Taxi")
▷ 파라메터
1. 범위
: <기준>에 따라 테스트되는 범위입니다.
2. 기준
: <범위>에 적용할 패턴 또는 테스트 입니다.
▷ 사용 예제
원본 정보가 아래와 같이 지출 항목이 <Expense>에 표시되고 <Today>에 해당 지출 금액이 표시되는 table이 있습니다. 여기서 <Expense>Taxi항목으로 몇개가 있는 지 확인하겠습니다.
<Expense>에서 Taxi로 표시된 항목 수 찾기
countif의 첫번재 입력은 합계를 구하기 위한 범위를 넣습니다.
<Expense>정보 중 'Taxi'를 찾을 예정이니 범위의 입력값은 A2에서 부터 A9이까지가 됩니다.
다음으로 몇개가 있는 지 찾는 조건은 'Taxi'를 넣어서 'Taxi'의 갯수를 확인합니다.
<Today>에서 10이상인 값을 가지는 항목 수 찾기
countif의 첫번재 입력은 합계를 구하기 위한 범위를 넣습니다. 이번엔 찾으 범위가 <Today>로 변경되었으니 범위를 'B2:B9'로 변경합니다.
<Today>정보 중 값이 10보다 큰 항목의 갯수를 찾을 예정이니 ">=10"를 조건으로 입력합니다. 쌍따옴표(")가 들어가니 주의해주세요.
결과는 아래와 같습니다. 10을 포함하는 4개으 항목을 노란색으로 표시하였습니다. 결과도 4로 표시됩니다.
여러가지 조건을 만족하는 항목 갯수 찾기
countif의 경우 한가지 조건만을 확인하여 갯수를 알려줍니다. 여러가지 조건을 만족하는 항목의 개수를 확인하는 경우에는 countifs() 함수를 사용합니다. 's'가 붙은 복수형임을 참고하세요.
하나의 조건만 넣으면 countif()와 동일합니다. 따라서 countifs() 를 사용하는것이 더 활용성이 좋습니다.
사용 방법은 <범위>, <조건>을 하나의 쌍으로 여러개 입력합니다. 위에서 확인한 내용 2개를 모두 입력하면 아래와 같이 사용됩니다.
| 범위 1 | 조건 1 | 범위 2 | 조건 2 |
| A2:A9 | 'Taxi' | B2:B9 | ">=10" |
결과로 나타나는 값은 <조건 1>과 <조건 2>을 모두 만족하는 항목의 갯수입니다.
아래의 그림과 같이 <조건 1>을 만족하는 경우 녹색으로 표시하고 <조건 2>를 만족하는 경우 노란색으로 표시하였습니다.
두 조건을 모두 만족하는 경우는 빨간색 네모 부분으로 1개가 됩니다. 필요한 조건이 있다면 <범위>와 <조건>을 쌍으로 계속 추가하시면 됩니다.