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

Margaret Griffin

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.

=COUNTIFS(H:H,">=07/01/2017",H:H,"<=06/30/2018")

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!

AverageUser

I found the discussion linked below that says that "COUNTIFS can only use < > if you are referring to numbers - not dates."

https://community.smartsheet.com/discussion/countif-dates

It offers a workaround that might help.

AverageUser

And I found another article with a better work-around:

https://community.smartsheet.com/discussion/countif-between-2-dates

Try this formula:

=COUNTIFS({Completed Date}, @cell >= DATE(2017, 7, 1), {Completed Date}, @cell < DATE(2018, 6, 30))

 

Paul Newcome

In Smartsheet formulas, 7/30/18 would be read as 7 divided by 30 divided by 18 which leaves your criteria as "0.012962962962963". Dates in Smartsheet are referenced as DATE(YYYY,MM,DD)

 

Also... < and > can both be used in reference to dates.

 

You will also need to click the link in the formula help box that says "Reference Another Sheet...". When referencing another sheet in a formula, the value will be displayed as {Reference Sheet Range 1}. See below...

Mike Wilday

I can confirm Paul's post. You will need to use the date formula to use dates in a countifs formula. 

https://help.smartsheet.com/function/date

Margaret Griffin

Big thanks to everyone for your responses!! 

Bo's formula worked like a charm: =COUNTIFS({Completed Date}, @cell >= DATE(2017, 7, 1), {Completed Date}, @cell < DATE(2018, 6, 30))

I figured the date configuration is what is what I was getting wrong. While Excel is able to read dates/data using this logic: "<=06/30/2018", Smartsheet requires the actual DATE formula: DATE(YYYY,MM,DD)

Margaret Griffin

That did it. Thank you so much!