COUNTIF is a very useful Excel function that counts the number of times cells that have some common parameter appear in a range.
It has two parameters:
- The range in which cells are found and;
- Criteria based on which we want to count the cells.
In the text below, we are going to show how to use this function when we have dates in our range.
Countif with Dates
For our example, we will use a filled questionnaire from a person that is traveling abroad (to Spain):
Now, if we want to know how many cells we have that contain a date, we will use the following formula:
1 |
=COUNTIF(B1:B8,">=1/1/1900") |
Our count will be as follows:
Which is the correct result of the dates we have in our range.
To simply explain how this formula works, we need to understand that Excel observes all dates as numbers. Number 1 indicates the date 1/1/1900. This date is the oldest date that we can have. So with this formula, we cover all the dates bigger than this one, which is every possible date.
There is, sadly, a downside to this formula. In all cells in column B, we only have the dates as numbers, since the passport number has the letter “B” in it. If we delete the letter “B”, we will get the following result of our COUNTIF formula:
Now we have the number 4, which means Excel recognized four values that are bigger than number 1 in our range.
Sadly, there is no workaround for this issue. So this can serve as a good warning. If you have a combination of numbers and dates, Excel will always observe them as equal, as dates are simply numbers in a different format.
Countif in a Combination with Isnumber
We can also use a COUNTIF formula in a combination with the ISNUMBER function to count the number of dates in our range.
For this purpose, we will create another sheet with traveling info data:
We have the passport number with the letter „B“ which means that this will not be included in our formula.
ISNUMBER is a simple Excel formula that checks if a value in our desired cell refers to a number or not. If it is a number, then it returns the value TRUE. If not, it returns FALSE.
The formula in cell B1 will be:
1 |
=ISNUMBER(B1) |
We will drag this formula till the end of our table (cell B8) and will have the following results:
Now we will count only those values that are true, and the formula in cell B9 will be:
1 |
=COUNTIF(C1:C8, "TRUE") |
The result will be number 3, as we could have guessed: