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

Options
✭✭✭✭
edited 12/09/19

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!

Tags:

• edited 07/09/18
Options

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.

• Options

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))

• ✭✭✭✭✭✭
edited 07/09/18
Options

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...

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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)

• ✭✭✭✭
Options

That did it. Thank you so much!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!