엑셀에서 COUNTIF, AND, OR 함수를 사용해서 다중조건에 해당되는 값의 합계를 구하는 방법을 알아 보겠습니다. 결론부터 말하면 COUNTIF 함수와 AND, OR 함수는 같이 사용하는 것이 불가능합니다. 따라서 이 글에서는 이를 대체할 수 있는 방법을 알려 드리겠습니다.
COUNTIF AND OR 함수로 다중조건 합계 구하기
< 영상설명 >
< 그림설명 >
1.COUNTIF AND 함수로 다중조건 합계 구하는 법
먼저 COUNTIF 와 AND 함수를 같이 사용해서 다중조건 합계를 구하는 법을 알아보겠습니다.
근데 COUNTIF 함수, AND 함수는 같이 사용하는 게 불가능 합니다. 때문에 이를 대체하기 위해 SUMPRODUCT 함수를 사용해 보겠습니다.
아래 예시에서 ‘1학년’ 이면서, 동시에 영어 점수가 ’80점 이상’ 인 학생 수를 구해 볼게요. 아래와 같이 함수식을 입력 합니다.
=SUMPRODUCT((C3:C10=”1학년”)*(E3:E10>=80))
SUMPRODUCT 함수 내의 첫 번째 괄호 안에 ‘1학년’ 이라는 조건을 입력해 주면 되는데요. 이때 ‘1학년’ 은 반드시 큰따옴표(“”) 안에 입력 해 주셔야 됩니다. 그 후 곱하기(×) 기호를 입력해 주고요. 두 번째 괄호 안에 ‘영어 점수가 80점 이상’ 이라는 조건을 입력 해 줍니다.
그 후 엔터 키를 누르면 2라는 결과 값이 나옵니다. 그렇다면 어떤 원리로 이 같은 결과 값이 나오는 걸까요?
아래 사진을 봅시다. C열에 1학년에 해당되는 경우 1이라고 표시되게 했고요. 1학년이 아닌 경우 0이라고 표시되게 했습니다.
이번에는 D열에 영어 점수가 80점 이상인 경우 1이라고 표시되게 했구요. 80점 이상이 아닌 경우 0이라고 표시되게 했습니다.
자, 마지막으로 E열에 C열의 각 행과 D열의 각 행을 곱한 결과 값을 산출 해 보았습니다. 이 경우 1이라는 숫자가 2개 나오죠? 그래서 2라는 결과 값이 나온 겁니다. 다시말해 ‘1학년’ 이라는 조건과 ‘영어점수 80점 이상’ 이라는 2가지 조건을 모두 충족하는 경우에만 E열에 1이라는 값이 산출되는 거고요. SUMPRODUCT 함수는 E열의 1이라는 값만 다 더해서 2를 반환 해 주는 겁니다.
2.COUNTIF OR 함수로 다중조건 합계 구하는 법
이번에는 COUNTIF 함수와 OR 함수를 사용할 건데요. 아래 3가지 조건 중 1가지 조건만 충족 해도 값을 카운트 하고, 카운트 한 값의 합계까지 내는 방법을 알아보겠습니다.
- 국어 80점 이상
- 영어 80점 이상
- 수학 80점 이상
먼저 IF 함수를 이용해서 조건을 충족하는 경우 1, 충족하지 않는 경우 0이 표시 되도록 해보겠습니다. 아래와 같이 함수식을 입력합니다.
=IF(((D3:D10>=80)+(E3:E10>=80)+(F3:F10>=80))>0,1,0)
첫 번째 괄호에는 ‘국어 점수가 80점 이상’ 인 조건을, 두 번째 괄호에는 ‘영어 점수가 80점 이상’ 인 조건을, 그리고 세 번째 괄호에는 ‘수학 점수가 80점 이상’ 인 조건을 입력한 것입니다. 이 조건들을 다 더한 값이 0을 초과하는 경우 1이라고 표시되게 할 거구요. 조건들을 다 더한 값이 0이라면 0이라고 표시되게 할 겁니다.
위 화면의 파란 칸처럼 조건을 하나라도 충족 하는 경우 1이라는 값이 표시 될 거구요. 조건을 단 하나도 충족하지 못 하는 경우, 즉 국어, 수학, 영어 중 어떤 과목도 80점을 넘지 못한 경우 0이라고 표시가 될 겁니다.
자, 이제 1가지 이상의 조건을 만족한 학생의 합계를 구해 볼 겁니다. 위에서 입력한 IF 함수식 맨 앞에 아래처럼 SUM 함수를 입력한 뒤 괄호를 엽니다. 그리고 수식 맨끝에 괄호를 닫아주고 엔터 키를 누릅니다.
=SUM(IF(((D3:D10>=80)+(E3:E10>=80)+(F3:F10>=80))>0,1,0))
그럼 6이라는 결과 값이 나옵니다. 다시말해 국어, 영어, 수학 중 한 과목이라도 80점 이상인 학생의 수가 6명이라는 뜻입니다.
< 추천글 >