# Countifs formula error

Options
edited 12/09/19

within the same report as my list of projects I'm trying to return a count of projects that are due to start within the current week (day 0 - 7) from "today" then return a count of project for next week from (day 8 - 13) then week 3 (day 14 - 20).   here's the formula I came up with but it's not working.

=COUNTIFS(Start1:Start127,"<="&TODAY(+0) ,Start1:Start127,"<="&TODAY(+7)

=COUNTIFS(Start1:Start127,"<="&TODAY(+8) ,Start1:Start127,"<="&TODAY(+13)

Any ideas how to fix?

• Employee
Options

Hi Steve,

Looks like you've got a few syntax issues with your functions. I'd recommend reviewing our help article on functions to know more about the expected syntax in Smartsheet: https://help.smartsheet.com/articles/2476171-create-and-edit-formulas-in-smartsheet

Here's our functions list as well which typically includes examples: https://help.smartsheet.com/functions

Try functions more like this in your sheet:

=COUNTIFS(Start:Start,<=TODAY() ,Start:Start,<=TODAY(7))

=COUNTIFS(Start:Start,<=TODAY(8) ,Start:Start,<=TODAY(13))

Note that I removed the cell references on these so they'll count the entire range of your Start column. If you don't want the entire range to be counted, you can add the cell references back as you had them.

• Options

Thanks Shaine.  Your Functions worked perfectly.  Thanks for the links and help!

• Options

I Spoke to soon.  After re-checking my data the formula isn't counting the correct amount of projects.  Formula is counting projects from day 0 through 13 instead of only day 8-13.  I also need to Exclude "completed" project from the count in the status column.  Any ideas?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!