Trying to get a formula to work

Becky S

I am currently trying to get a formula to work in Smartsheet that will count the requests we have outstanding that havent been completed and are over 41 days old. But that will also take into account when new lines are added to the document.

 

The formula I have got working in excel is: =COUNTIFS(B:B,"<"&TODAY()-39,C:C,"")

I've tried to adapt it to work in Smartsheet, using the below, but I always get error messages or it doesn't work it out properly;

 

=COUNTIFS([Date Received]:[Date Received], "<&Today"()-41, [Days to complete]:[Days to complete], " ")

 

=COUNTIFS([Days to complete]:[Days to complete], "", [Days since received]:[Days since received], (420))

 

=COUNTIFS([Date Received]:[Date Received], "*/*/**", [Days to complete]:[Days to complete], "*/*/**")

 

Please could someone help and tell me if it's possible?

 

thanks!

Brett Evans

Becky,

 

Can you scrub the data of anything sensitive and publish a copy of the sheet?  For me, complex formulas are easier to figure out with context..

 

-Brett

Travis

Hi Becky! When using a COUNTIFS function, you cannot count if dates are "< TODAY(-41)". But there is a workaround...

 

Add a Checkbox column to your sheet with a formula that will check the box if the corresponding date is < TODAY(-41), then use the Checkbox column in your COUNTIFS.

 

You can reference an entire column (so you wont need to update the formula when you add new rows) by removing the row numbers from the cell ranges. 

 

I made a quick sheet to show you this. The formula is located in the purple cell.

 

https://app.smartsheet.com/b/publish?EQBCT=e787e67e62b7446ba162ab1147745484

Becky S

Hi

 

Sorry I didn't reply sooner, the work around worked perfectly thank you!!

 

Kind regards

Becky