The COUNTIF function allows you to count how many times you have an item in a list. But when you want to do a count on several criteria, the function COUNTIF shows its limits.
That’s why Microsoft’s development teams have built since Office 2007 a new feature of counting multiple criteria, it’s the COUNTIFS function. The maximum number of criteria that can be integrated in this COUNTIFS is … 256 (wow) 🙄
To function, the function COUNTIFS needs one or more criteria and also the beaches of data. For example, we will have the following example where you want to know how many single men are in your customer list.
We’ll start by writing the formula
Then you specify the range of cell where you want to search your first criteria (the column C)
Next, you enter the value or the text you want to search (“Man”). You can write this term directly or the cell which contains this value.
The word “Man” is written between double-quotes because it’s a string and all strings must be written between double-quotes in Excel.
Following this first test, you will select the column E as the second range of criteria and then you write the word “Single” as the second criteria:
Note: It is compulsory that the range of your cell are exactly the same for the ” criteria. For example COUNTIFS= C2:C20,“Man”,E2:E21,“Single”) will return the #/VALUE! Because the two ranges are not equal.
Greater than / Less than
In the same way as the function COUNTIF, you can add logical symbols “>”, “<“, “>=”, “<>” to expand your criteria of selection.
Consider the following example you want to count the number of orders that exceed 1500 for the client Smith. In this exercise, we will make a count on the names and on the column Total. The formula is:
Now, if you write your formula not with the value but with the cell references, you will write your formula as follow:
Note: the symbol & is needed to link the operator >= and the cell reference. Without this symbol, your test can’t work
Count over a period of time
With the COUNTIFS function, it is also possible to calculate the number of elements for a specific period of time and without difficulties.
In the following example, we will try to calculate the number of times we sold hard drives between the 1rt December 2008 to the 31th December 2008.
Our first criteria is very simple. B2:B5000 to the reference range and J3 (Hard Drive) for the criteria.
Then we will build two criteria for the same column A (the date)
- once to get the orders greater than or equal to 01/12/2008
- another for orders under stringent 01/01/2009 (or less than or equal to 31/12/2008)
In these two cases, the reference range is the same (A2: A5000) and only the criteria will be different. The complete formula becomes:
Count over a variable time period
So we will replace in our formula the dates by another formula wich returns a variable date. In this case, we will use the DATE function to find the first and last day in function of the value in K2 and K3.
So for the first date we have DATE($K$2,$K3$,1) and for the last date DATE($K$2,$K3$+1,0). Applied to our formula:
The last example is obviously the most complete but also the more useful because many dashboards are built on the basis of monthly reports. With this formula, you can easily change values of the month of the year and see the result immediately in cell K4.