Countifs formula error

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?

Find more posts tagged with

Formulas

Project Management

Comments

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?

Sign in to join the conversation!

Quick Links

Trending discussions

Match or Index
I am trying to pull a phone number and email from our master roster that is on another sheet in Smartsheet. I cannot figure out the formula. This is the formula I have tried. =INDEX([EMPLOYEE NAME]@row , MATCH({IMS Master Roster Range 2}), 0)
Workflow Generate Impact Report PDF in Impact Form Response Sheet has become invalid
Hi All, I keep getting this annoying error when I try to trigger a PDF generation. I initially set up to trigger the PDF generation to kick off once the last field the needs data is "not blank". That is when the error started. I modified the sheet and added a yes/no drop down to trigger the PDF generation when the value in…
INDEX COLLECT MIN
Desired result is to pull the Initiative Name where the Days to Due Date is the least (so, due the soonest); then to make an index for the Top 10 Initiatives Due Soonest Currently using this formula: =INDEX(COLLECT({Project Master - Initiative}, {Project Master - Days to Due}, MIN({Project Master - Days to Due})), 1) This…