COUNTIFS Function helps us get conditional counts within a range based upon multiple criteria’s. It’s a enhanced version of it’s sister function COUNTIF which also gives us conditional counts but only with a single criteria.
This is a suprebly handy function to know and understand if you need to frequently lookup for conditional counts.
Say, we have a data consisting of Students, Name, Attendance and Annual Grades. If you want to quickly get a count of No of students who have scored 80%+ and have an attendance of 80%+. These are the situations where COUNTIF will make your job easy.
In this tutorial we will take you through the all the important aspects of COUNTIFS Function.
Table of Contents
Purpose
COUNTIFS helps us to get conditional counts basis multiple filtering criteria’s.
Syntax and Parameter Definition
This is how google lay’s out the syntax for COUNTIFS
= COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])However, to simply it, this is also how is can be represented
= COUNTIFS(range1, criteria1, range2, criteria2, range3, crieria3 ..........)- criteria_range1/range1: It’s the group of cells(like A3:A7, B2:B10 etc) which needs to be checked, against criterion1.
- criterion1: If the rule or condition(like “>10”, “=5”, “TOM”) which which will be used to test upon the criterion1. You can also include comparison operators like <, > , <=, >=, = , <>.
Apart from the above two, rest of the arguments follow similar meaning & syntax and are completely optional to have.
Important Points to Note:
- Criterion must be enclosed in double quotes like “=5”, “>7” , “STAR”
- All Ranges(range1, range2, range3 etc) must have the same number of rows and columns
- One can easily use wildcard characters like *, ~ etc while specifying the criterion for a text condition
- Text Criterions should be specified for Text Ranges and Number Criterions for Numerical Ranges
- Text Criterions checks against the corresponding text_range will be case-insensitive
- COUNTIFS is case-insensitive while checking for text criterions
Expected Output
COUNTIFS returns number of matches which satisfy all the criterions.
But what does that mean? Lets take a looks at some basic to advanced examples in the next section to capturing more practical dimensions of using COUNTIFS.
Examples
Multiple Criteria Count – Basic Example
Say, we have the follow data for students performance. We want to count number of Students who scored over 80% and had an Attendance% of over 80%.
We will be using the below formula to arrive at our results.
=COUNTIFS(C3:C10,">=80%",D3:D10,">=80%")
Count Cells Between Two Numbers : Multi-Criteria Same Range
Say you just wanted to find the number of entries greater than 50 and less than 80 in column C below.
Smart way is get this done is to improvise using COUNTIFS function. In the below formula, we have used same range more than once and modified criteria each time.
=COUNTIFS(C3:C10,">=50%",C3:C10,"<=80%")
COUNTIFS with Text and Numerical Criteria’s
Say we have the shipment log by different country and value below. We want to know number of shipments arrived from US of value more than a million dollars.
=COUNTIFS(B3:B10,"US",C3:C10,">1000000")
Visual Demo of COUNTIFS Function
Before we end here is a sample visual demonstration of COUNTIFS Function in action.
That’s it on this topic. Keep browsing SheetsInfo for more such useful information 🙂
