Count Dates / Checkbox Formulas
I am in process of transferring a large excel document into Smartsheet (over 2000 lines). The excel version is tracking 2007 projects, some of which are complete, some no longer active, and some active. I need to count how many active projects will be completed in year 2018, year 2019, and year 2020. Currently, our excel document has a COUNTIF formula to count the active projects within a certain time frame. For example, projects with an estimated completion date of 2018 have this formula in Excel: =COUNTIFS('DEX MASTER'!H:H,"Yes",'DEX MASTER'!U:U,"<=12/31/2018").
From what I have found, Smartsheet does not have the ability to count text and dates within one formula. As a workraround, I have created 3 check columns, one for each completion year, so I can then create a separate formula to count the check boxes. The formula I have is: =IF(AND([Yr3]997 < DATE(2018, 12, 31)), 1)
This has been working, with two exceptions: some projects that are no longer active have the dates removed, and the checkbox is still being checked when it shouldn't be (see photo). Also, we put a "c" in the date column once a project has been completed, which then gives me an error in the checkbox formula (see second photo). Any help with these two issues would be appreciated!
Comments

don't use countif. Instead use =count(collect()) This will work with dates numbers and boolean expressions. You don't need the checkboxes or anything.
https://help.smartsheet.com/function/collect

I used your suggestion with this formula: =COUNT(COLLECT(Active17:Active2022, "Yes", [Yr3]17:[Yr3]2022, <=DATE(12 / 31 / 2018)))
It only comes back as 1 when it should be over 400. What am I doing wrong?
Also, the formula for the following years, it will need to count in between dates 01/01/2019 and 12/31/2019. What would that formula look like?
Thank you so much! 
you have the formula for collect wrong. It is kinda weird, but you need to specify a single return column, which is the first set of data in the formula
=COUNT(COLLECT(Active17:Active2022,Active17:Active2022, "Yes", [Yr3]17:[Yr3]2022, <=DATE(12 / 31 / 2018)))

in this case it returns every value of "Active" from row 17  2022 for which the value of "Active" is "Yes" and the value of "Yr3" is less than 12/31/2018.
Also I think you have your date formula mixed up, there should be commas, and it should be completely filled out.
=COUNT(COLLECT(Active17:Active2022,Active17:Active2022, "Yes", [Yr3]17:[Yr3]2022, <=DATE(2018,12,31)))

Thank you so much! That worked!
Help Article Resources
Categories
Check out the Formula Handbook template!