Count Dates / Checkbox Formulas

viknutson ✭✭
edited 12/09/19 in Formulas and Functions

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



  • viknutson
    viknutson ✭✭
    edited 04/12/18

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

  • Thank you so much! That worked!

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!