The COUNTIFS function in excel is basically a combination of the Count Function and use of Logical tests (similar to the IF function). If you recall our previous posts on COUNTA and COUNT functions, you will remember that the COUNTA function counts non-empty cells whilst the COUNT function counts only numerical values within a range. The COUNTIFs function on the other hand gives you the liberty to specify cells to be counted within a range, based on those cells meeting the logical tests / criteria you predefine.
The COUNTIFS function is the successor to the COUNTIF function which allows logical test of a single criteria only whereas you could test up to 127 criteria using the COUNTIFS Function.
In essence, the COUNTIFS function returns the count of cells that meet one or more criteria. Unlike the other COUNT or COUNTIF functions stated above, the COUNTIFS function is a lot more flexible. Your criteria could be cells falling within a particular date, cells containing a particular number or text, cells meeting logical statements indicated by logical operators such as (>, <, <>, =, >=, <=) or partial matching cells using wildcards (*?).
In today’s post, we would take you through all you need to know about the COUNTIFS function and how you can apply it to real life financial modeling scenarios. First, let’s take a look at the Syntax and Arguments for the COUNTIFS Function.
Syntax & Argument
The Syntax of the COUNTIFS function varies depending on the number of criteria being evaluated. Recall, we stated that the function returns count of cells that meet “one or more” criteria in our introduction above. You should note however that each COUNTIFS function can take only 127 range / criteria pair, and each range/criteria pair makes up an argument. Let’s see some examples of syntax we can have for this function.
=COUNTIFS(range1, criteria1, range2, criteria2) // 2 conditions
=COUNTIFS(range1, criteria1, range2, criteria2, range3, criteria3) // 3 conditions
The Arguments for the COUNTIFS assuming 2 conditions / criteria:
- range1: This is the range of cells to evaluate for the first criteria.
- criteria1: The criteria to use for range1
- range2: The second range of cells to evaluate for the second criteria
- criteria2: The criteria to use for range2
Usage Notes
- All ranges must be the same size, meaning that they must all reference the same start and end row. For example, let’s assume our first criteria range is in column A and second criteria range is in column D. To return a result they must have the same start and end row, say A2:A75 and D2:D75. Any deviation from the above would result in a #VALUE error.
- The function would count only cells that meet all criteria, i.e all conditions must be true.
- Criteria can include logical operators, but they must be enclosed in double quotations for example: “>90”, “<=”&B5, “<>”&Today() .
- The ampersand (&) character is used to connect the logical operator to the cell or formula containing the value required to complete the criteria as seen in the point above. This is because excel would need to evaluate cell references and formula first to obtain a value that can be joined to an operator.
- To add new conditions, you would need a separate range and criteria
Let’s look out how to define the criteria in the next section:
Defining the Criteria
In the previous section, we discussed how logical operators and the ampersand (&) character can be used in defining our criteria. In this section we would show you the syntax for the criteria argument in various scenarios:
| Objective | Criteria |
|---|---|
| Cells greater than 50 | “>50” |
| Cells less than 100 | “<100” |
| Cells equal to 75 | 75 or “75” |
| Cells greater than or equal to 200 | “>=200” |
| Cells less than or equal to 150 | “<=150” |
| Cells equal to Blue | “Blue” |
| Cells not equal to Green | “<>”red |
| Cells that are blank | “” |
| Cells that are not blank | “<>” |
| Cells that begin with T | “T*” |
| Cells greater than B5 | “>”&B5 |
| Cells Less than Now | “<“&NOW() |
Now all you need to do is to select the range and choose any of the criteria syntax above and Khalas, done. Nevertheless, we would still illustrate the function with more detailed examples below.
COUNTIFS Function examples
There are various ways to specify the criteria for your ranges. As already touched on in the syntax & argument sections, you could hardcode the criteria in double quotations, enclose a logical operator in double quotation alongside the numerical value, or join a logical operator to a cell or formula by using the & operator. Enough talk, let’s see some examples below:
Hardcoded Criteria
The double quotations are a must for text values, for numerical values, you could just type in the number
=COUNTIFS(A3:A19,”Diagon”,C3:C19,”Valley”) // Diagon and Valley conditions
Logical Operator & Numerical Value
You could use a logical operator to specify the criteria and type in the numerical value right next to the logical operator. For example:
=COUNTIFS(A15:A45,”>50″) // Count greater than 50
=COUNTIFS(D5:D30,”<=30″) // Count less than or equal to 30
Value from another cell
The COUNTIFS function also allows you refer to values on other cells.
=COUNTIFS(A5:A20,”>”&D7) // Count cells greater than D7
Blank Cells
COUNTIFS function can count blank or non-blank cells and we can set the criteria as follows:
=COUNTIFS(C5:C15,””) // Count cells that are blank
Not equal to
The not equal to sign in excel is represented by “<>”, and we can use this in our COUNTIFS formula as follows:
=COUNTIFS(A15:A45,”<>MBT”) // not “MBT”
Dates
With the COUNTIFS formula, you can specify periods as your criteria. You can choose to refer to a cell containing the date, or you can implement within the formula by using the DATE Function.
Use the DATE function to specify the period for your COUNTIFS
Wildcards
When we speak of wildcards in excel, the wildcard character that comes to mind is the asteriks (*), and this is because it is the most flexible of the wildlife characters. The question mark is also well known, but it is seriously limited as it is used to match the length of characters and not necessarily the text.
OR Logic
The criteria in a regular COUNTIFS Function works the way an AND function works, meaning that all criteria must be met before the cells are counted. You could however need just one criteria to be met before counting the cell, and with this simple tweak you can get the COUNTIFS function to work that way:
Note that you must combine the COUNTIFS with a simple SUM formula else you get a Spill notification. The reason is that the single COUNTIFS function counts and returns each result separately and you need a SUM function to add it all up.
Practical Use of COUNTIFS In Financial Modeling
In preparing our budget and Long-range plan in my FP&A unit, I was responsible for the modeling of our leases in line with the IFRS16 (Leases) requirements. Below is a snapshot of a copy of an input sheet for all IFRS16 leases for a Telecommunications company in the USA. We would use COUNTIFS function in excel to return the number of new leases for each month from Jan-2022 to Dec-2022
We have created a summary sheet that should show the count of new leases for each month. That’s where the COUNTIFS function comes in handy. In your summary sheet, select the cell you have prepared to output count of new leases in Jan-22 and type the formula as seen in the screenshot below:
Explaining the cell references
- ‘Input Sheet’!$D$5:$D$54: This is the first Criteria range which is the category (Renewal / New Lease).
- “New Lease”: We are interested in the count of new leases only, so our first criteria would be new lease.
- ‘Input Sheet’!$E$5:$E$54: This represents our second criteria range which is the column for the lease commencement month.
- ‘Summary Sheet’!C$2: Second criteria is the month in our summary sheet.
Result:
If done correctly for each month, you should have the following result:
We would show how to calculate the total annual payment amount using the SUMIFS function in our SUMIFS Post.
