<\/figure>\n\n\n\nNow, if we want to know how many cells we have that contain a date, we will use the following formula:<\/p>\n\n\n\n
=COUNTIF(B1:B8,\">=1\/1\/1900\")<\/code><\/pre>\n\n\n\nOur count will be as follows:<\/p>\n\n\n\n <\/figure>\n\n\n\nWhich is the correct result of the dates we have in our range.<\/p>\n\n\n\n
To simply explain how this formula works, we need to understand that Excel observes all dates as numbers. Number 1<\/strong> indicates the date 1\/1\/1900<\/strong>. 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.<\/p>\n\n\n\nThere is, sadly, a downside to this formula. In all cells in column B<\/strong>, we only have the dates as numbers, since the passport number has the letter \u201cB\u201d<\/strong> in it. If we delete the letter \u201cB\u201d<\/strong>, we will get the following result of our COUNTIF formula<\/strong>:<\/p>\n\n\n\n <\/figure>\n\n\n\nNow we have the number 4<\/strong>, which means Excel recognized four values that are bigger than number 1<\/strong> in our range.<\/p>\n\n\n\nSadly, there is no workaround for this issue. So this can serve as a good warning<\/strong>. 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.<\/p>\n\n\n\nCountif in a Combination with Isnumber<\/h2>\n\n\n\n We can also use a COUNTIF formula<\/strong> in a combination with the ISNUMBER function<\/strong> to count the number of dates in our range.<\/p>\n\n\n\nFor this purpose, we will create another sheet with traveling info data:<\/p>\n\n\n\n <\/figure>\n\n\n\nWe have the passport number with the letter \u201eB\u201c<\/strong> which means that this will not be included in our formula.<\/p>\n\n\n\nISNUMBER<\/strong> 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<\/strong>. If not, it returns FALSE.<\/strong><\/p>\n\n\n\nThe formula in cell B1<\/strong> will be:<\/p>\n\n\n\n=ISNUMBER(B1)<\/code><\/pre>\n\n\n\nWe will drag this formula till the end of our table (cell B8<\/strong>) and will have the following results:<\/p>\n\n\n\n <\/figure>\n\n\n\nNow we will count only those values that are true, and the formula in cell B9<\/strong> will be:<\/p>\n\n\n\n=COUNTIF(C1:C8, \"TRUE\")<\/code><\/pre>\n\n\n\nThe result will be number 3<\/strong>, as we could have guessed:<\/p>\n\n\n\n <\/figure>\n","protected":false},"excerpt":{"rendered":"COUNTIF is a very useful Excel function that counts the number of times cells that have some common parameter appear in a range….<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23],"tags":[170,190],"yoast_head":"\n
How to Use Countif with Dates in Excel<\/title>\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n\t \n\t \n\t \n