Updated: Apr 9
These two functions are immensely important and very useful in the interpretation of data and getting the required results. Both of them will be discussed in this post and will be explained to their fullest to avoid any confusion and complexity.
The COUNTIF Function
Two functions are combined, as you can see – COUNT and IF. Very powerful to count a range of cells meeting a certain condition.
The syntax or the way of using this function is as follows:
The range explanation, as for all other functions, is the same. You can either use an absolute reference or a relative one. The benefit of using absolute reference is that when you copy a formula to some other cell the range used in the COUNTIF function will not change, while with the relative reference the range will change according to the jump made in copying and pasting.
As you can see above that the “condition” is put within the quotes. The condition can be anything, text, a number, a number equal or greater, a number less or equal, a date, a time, etc.
Used for a Text entry
When looking for a text value, which in computer terms is also called a string value, we will use the function as below example:
As you can see above, I have entered a range from B2 to B15, followed by a “,”, and then put a condition or a criterion, i.e., “Golf”. I am asking Excel to count in the range B2 to B15 the word Golf. This is what COUNTIF is all about. Count a range where the given condition is met. The result will be seen as follows:
Using for a Number entry
We can use the COUNTIF function for numbers in quite different ways.
Method 1: We can use the number as a string (or text) and use it as above, like for example the below example:
The formula will return, after counting in the said range for 1992.
Method 2: We can use mathematical operators for the numbers, unlike text. The “=”, “>”, or “<” can be used within the strings. In my table, if I want to count car model years more than 2000, I will use the following formula:
Notice that within the range C2:C14, Excel will look for values greater than 2000 and will count them and display the result:
As you can see there are 7 entries more than 2000. The 2000 year will be ignored, as we have asked to count greater than 2000 and 2000, in itself is not greater than 2000. If you want to include 2000 in it as well, what will you do? That’s right, you will include the operator “=”:
Now as you can see, excel will count all value which is equal and greater than 2000. Let’s change the formula in my data and see the results:
Now, instead of 7, the result is showing 8, because 2000 is also included.
Likewise, you can use <= or < operator as you like.
Method 3: Use the COUNTIF function to count cells containing a number, using the number without the quotes.
Numbers can be used as conditions without the quotes, but you cannot use the operators with them.
Use of “<>” operator with COUNTIF or any other function
The operator “<>” is used for “not equal to” and can also be read as “not nothing”. It can be used with any text or number value within the strings (“”) and will always mean not containing that particular value, for example:
The above formula will count all the values from C2 to C14 which do not contain the value “Golf”. Likewise, you can use the same with the numbers as well:
You can compare this formula with the one we have used to count all cells containing “Golf”, but we didn’t use the “=” sign which is not mandatory. If you use it, it will give the same result as not using it.
The COUNTIFS Function
You have noticed the “S” after the IF, and you are very much right. This function is used to count cells meeting more than one condition. The syntax used in:
Look at the above formula, there are two different conditions with the parenthesis of COUNTIFS. I have marked them in different colors. Notice that each condition is written the same as COUNTIF, i.e., give a range to count in, followed by a condition to match in parenthesis. This is immediately followed by another statement, separated by a “,”. The second statement is then written as the first one. Count the range having 2000 in them.
So, COUNTIFS will look simultaneously within 2 cells at one time and when both the conditions are met, it will make a count. To elaborate it, let’s see it like this:
First, the COUNTIFS will look into B2, if it is having Golf, it will look into C2, if it contains 2000, it will make a count. Then it will jump to B3. If it contains Golf, it will look into C3. If the C3 does not contain 2000, it will not make a count. Then it will jump to B4, if it does not contain Golf, it will not make a count and jump directly to B5, no need even to go to C4.
It means, that both the conditions must be met within the same relativity of cell values before a count is made. Of course, you can use more than 2 criteria. Excel allows up to 127 criteria within one COUNTIFS function.
=COUNTIFS(A2:A7, "<" & A6,B2:B7,"<" & B4)
The above three examples demonstrate how beautifully you can use COUNTIFS with more than one condition and in combination with operators.
That will all for this part. If you have any questions, please do not hesitate to ask in the comments.