By Victor Alarsa
In essence, the COUNTIFS function is used to count the number of cells that meet one or multiple criteria, given a specific range of the array.
You may have noticed that COUNTIFS has an “S” in the end, which differs from its cousin COUNTIF, which is programmed to count the number of cells meeting only one condition and a single range, whereas COUNTIFS accepts several criteria.
The formula is comprised of ( criteria range 1, criteria 1 ), this is the required argument. In case you want to add new criteria, just add after the first two arguments
E.g. ( criteria range 1, criteria 1, criteria range 2, criteria 2).
You could add as many criteria as you want. The criteria range accounts for the array you want to count (highlighting them), and the criteria are the condition to be tested against those values.
Don’t forget that the range array should always have an equal number of cells highlighted, e.g., if the criteria range 1 highlight 5 cells, all the other criteria range should highlight 5 cells as well, or you won’t be able to complete the formula.
Examples of criteria:
– Fixed values (e.g. 1, 2, 3, …)
– Text (e.g. “example1”, “example2”) *text should always be written in quotes)
– Greater or less than (e.g. “<1”, “<=3”, “>2”)
Let’s start with a few examples:
First, a simple one (only one criteria, like COUNTIF), to find the number of properties rented above £200. You only need to highlight the price array and type “>200” in the criteria. The result should be: 5, all but Charlton.
Now, let’s add a second criteria, Let’s say that apart from finding rent price above £ 200 you want to find those places with 5 or more properties. Then, from the example before, you just need to add the second range array, which in this case is “No of Properties” and type in criteria 2: “>=5”. The result should be 3. Canning Town, Chalk Farm, and Chelsea.
Let’s make things more challenging. What if you need to count the number o properties to be paid in a specific period, for example, between the second and fifth of January? For the first and second criteria range you should select the same range of dates, and for the criteria use the “greater or less than” model. E.g. “>=” 02/01/19 and “<=” 05/01/19. Like in the image below.
The result should be 4 properties meeting these criteria. Properties 2, 3, 4, and 6.
Lastly, let’s add a new layer to this example, a list of priorities. Apart from the criteria above, we want to find priority 01. Therefore, you only need to follow the example above and add a third criteria range, highlighting the priority list and selecting the criteria, in this case, “1”. The result should be 1: Property2.
This is only one way you can use COUNTIFS, whilst using your creativity you can create several other different scenarios in which COUNTIFS could be simple, clean, and ingenious.
After all, modelling is not only about how to automate a spreadsheet but is the art of doing it using fewer formulas as possible. Hope you found this article useful for your future model.
If you want to improve your Financial Modelling skills, why not to book a place in our Real Estate Investment and Financial Modelling course?
Otherwise you can have a look at our Calendar for more courses and dates.