Count Dates / Checkbox Formulas

viknutson

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! 

 

L_123

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

 

viknutson

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!

L_123

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))) 

L_123

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))) 

viknutson

Thank you so much! That worked!

L_123

Sorry I missed your other question. Just use an and if there is a range

 

 

=COUNT(COLLECT(Active17:Active2022,Active17:Active2022, "Yes", [Yr3]17:[Yr3]2022, and(@cell&lt;=DATE(2018,12,31),@cell > Date(2017,01,1)