Using a COUNTIFS formula to calculate # of rows in a set date range

Margaret Griffin
Margaret Griffin ✭✭✭✭
edited 12/09/19 in Formulas and Functions

I'm trying to calculate the number of tasks completed in the prior fiscal year, to be used as a dashboard metric. I've created a sheet that uses a COUNTIFS fomula to reference the relevant column in another sheet. Here is my formula, which returns a '0' result.

=COUNTIFS({Completed Date},">=07/01/2017",{Completed Date},"<=06/30/2018")

When I export the master sheet to Excel and run the following calculation on the same column, it works immediately - there are 247 dates in this date range.


The two formulas are essentially identical, so I'm unsure why the one in Smartsheet isn't working. The fact that the formula isn't returning an 'UNPARSEABLE' result makes me think it just can't read the date data. I've tried entering the dates as both mm/dd/yy and mm/dd/yyyy formats, but am still getting a '0' result. Any suggestions are much appreciated!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!