Countifs formula error

Steve Fullmer

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?

 

Shaine Greenwood

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.

Steve Fullmer

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

Steve Fullmer

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?