Count the # of projects with end date 60 days from today

ilwiny

I want to count the number of "Active" projects with an End Date within 60 days of today. I tried this and got an invalid operations error.

=COUNTIFS({sheetname range},"Active",{sheetname range} <=TODAY(60))

The first sheetname range is the Type column (shows Active, Completed, Planned, etc.)

The second sheet name is the column with all the End Dates. Every project has an End Date, there are no blanks.

If possible, I would like to say Type <> Completed. But I was trying to start with it being easier and just select Active. It would be nice to include all the different Types (like Planned, Initiate and Active) and just exclude Completed.

Once I get this to work, I'm going to flip to count the number of projects starting within 60 days.

Thank you in advance

jmyzk_cloudsmart_jp

Hi @ilwiny

The formula structure looks fine, except if you reference a different range with the {sheetname range}.😅

The demo sheet below uses Sheet Summary fields and ranges, Status:Status and Finish:Finish. However, the formula structure is the same as if I referenced from another sheet with ranges like {Status} and {Finish}.

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

ilwiny

Thank you very much.😀

jmyzk_cloudsmart_jp

@ilwiny

Happy to help!😁

jmyzk_cloudsmart_jp

Hi @ilwiny

The formula structure looks fine, except if you reference a different range with the {sheetname range}.😅

The demo sheet below uses Sheet Summary fields and ranges, Status:Status and Finish:Finish. However, the formula structure is the same as if I referenced from another sheet with ranges like {Status} and {Finish}.

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

ilwiny

Thank you very much.😀

jmyzk_cloudsmart_jp

@ilwiny

Happy to help!😁