SOLVED - COUNTIFS Problem

Outofmydepth

Hi,

I'm trying to return a status within a range when the finish date is within 7 days, I've tried a few alternatives with the latest below (using @cell) which I know isn't correct but I'm hoping I'm close. Any advice would be greatly appreciated.

=COUNTIFS({RangeStatus}, [Not Started]1, {RangeFinish}, @cell <=TODAY(), @cell > TODAY(7)))

Shaine Greenwood

Hello,

I noticed a few things here:

First off, you don't need @cell, you can type your criteria directly after the comma: =COUNTIFS({RangeStatus}, [Not Started]1, {RangeFinish}, <=TODAY())

COUNTIFS will only count cells that meet all criteria. I don't believe that a date can both be less than or equal to the current date and also greater than the current date plus 7 days.

If you're wanting to add together the count of dates that meet the [Not Started]1 criteria and only ONE of the two date criteria, try adding COUNTIFS statements together:

=COUNTIFS({RangeStatus}, [Not Started]1, {RangeFinish}, <=TODAY()) + COUNTIFS({RangeStatus}, [Not Started]1, {RangeFinish}, >TODAY(7))

Outofmydepth

Hi Shaine,

Thanks for your response, my original formula was

=COUNTIFS({RangeFinish}, <=TODAY(7), {RangeStatus}, $[Not Started]$1)

which I though would work but it didn't. I tried the @cell as an option but that obviously wouldn't work.

Smartsheet support have actually come back to me with this example formula:

 =COUNTIFS(Enddate:Enddate, "not started", DueTime:DueTime, >TODAY(), DueTime:DueTime, <=TODAY(7))

but alas that doesn't return the correct value - I have 5 rows set at "Not Started" that have Finish dates due within 7 days but the above formula only returned a value of 1.

I'm baffled.

 

 

Outofmydepth

Solved by adding an extra =. 

=COUNTIFS(Enddate:Enddate, "not started", DueTime:DueTime, >=TODAY(), DueTime:DueTime, <=TODAY(7))