Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

CountIF between 2 dates

aelmore
aelmore
edited 12/09/19 in Archived 2017 Posts

Hello! 

I'm trying to count the number of children whose "Final Approval Date" is between 6/1/17 and 10/1/17. I don't even know where to start with this formula. I know I've got my weekly totals like this:

=COUNTIF([FINAL APPROVAL DATE]:[FINAL APPROVAL DATE], AND(@cell <= TODAY(7), @cell > TODAY(-7)))

Any help is appreciated!

Thanks!

Amy

Tags:

Comments

  • JamesR
    JamesR ✭✭✭✭✭✭

    Amy,

    Often it is useful to create a hidden Checkbox Column than has the Criteria Formula in it to check if it matches.  You can then use a formula to count the ticks.

  • Hi JamesR!

    Happy New Year! That's a great idea. I'll definitely do that. But I'm still not sure how I would check the box if the "Final Approval Date" column is between 2 dates...

    =IF(([FINAL APPROVAL DATE], AND@cell <= ???, @cell > ???), 1,0)

    I don't know how to show a specific date in a formula rather than a "TODAY" value. I'll be extremely excited when I can use formulas in Reports!

    Thanks for your help!

    Amy

  • JamesR
    JamesR ✭✭✭✭✭✭

    The Uploaded image should give you an idea as to how to use the Checkbox and to count them.

    Count Checks.PNG

  • Robert S.
    Robert S. Employee

    Hello Amy,

    Thanks for the question. JamesR is right that this method can often be useful, however this calculation is also possible using a single formula without adding a column of checkboxes. This formula can be written very similarly to your weekly totals formula, and would look something like this:

     

    =COUNTIFS(CHILDREN([Final Approval Date]1), @cell >= DATE(2017, 6, 1), CHILDREN([Final Approval Date]1), @cell < DATE(2017, 10, 1))

     

    This will work if it's placed in a text/number column, however if you'd like to put this formula in the parent row and same column the dates are in (Final Approval Date), you can write it something like this instead:

     

    =COUNTIFS(CHILDREN(), @cell >= DATE(2017, 6, 1), CHILDREN(), @cell < DATE(2017, 10, 1)) + ""

     

    Since this is in the parent cell being referenced in the CHILDREN() function, you don't need to include anything within the CHILDREN()'s parentheses. Also, since this column is a date column and the result is a number, I've added + "" to the end to convert the result into text rather than a number so that you don't get a #DATE EXPECTED error. More on formula errors can be found here (https://help.smartsheet.com/articles/2476176).

  • JamesR
    JamesR ✭✭✭✭✭✭

    Robert,

    You are correct, I suggested my method as an alternative for those who struggle with the complexity of nested or multi parameter formula.

    Additionally having these interim calculation can allow the user to reuse in other Formula.

  • This is exactly what I needed! Thanks so much!

    Amy

This discussion has been closed.