Ask Your Question

Countif returning Zero

asked 2019-11-28 12:56:26 +0100

LeeTW gravatar image

I'm using LibreOffice on Mac, I have a spreadsheet saved as a .xlsx

The COUNTIF function returns the result as 0, the formula is basic: =COUNTIF(A4:A1500,1989)

The column it's searching only contains dates. It returns a 0 for all my COUNTIF searches, one for each year from 1989 through to 2019.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-11-28 13:08:40 +0100

keme gravatar image

updated 2019-11-28 13:27:41 +0100

Dates are stored internally as numbers. Specifically, "count of days from epoch", with default "epoch" for Calc being the 30th of December, 1899.

The number you are testing for in the given formula - 1989 - then corresponds to the 6th of November, year 1905

If your spreadsheet contains proper date values, you need to check for dates where the interval is between the first and last day of the given year. COUNTIFS() may be the function of choice. An alternative is to use the YEAR() function in a new column, and base your count on that.

To give dates for your range, it is best to use the DATE() function.

In many cases, dates exist as text content in a spreadsheet. Comparing text to a number should, by definition, return FALSE (i.e. not counted in this case). For that kind of dates you need a partial text compare.

If this does not help you to arrive at a solution, attach your xlsx file to your question (use the edit link below the actual question, then the paperclip Attachment tool.) Examining your file keeps us from guessing and enables us to determine likely causes why your attempts fail, and to reliably suggest a working solution.

edit flag offensive delete link more


Thank you. I hadn't considered that the date (as it's only the year) was entered as text, I changed them to numbers and now works.

Thanks again

LeeTW gravatar imageLeeTW ( 2019-11-28 14:12:00 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-11-28 12:56:26 +0100

Seen: 23 times

Last updated: Nov 28