CountIF between 2 dates

aelmore

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

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.

aelmore

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

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

Robert S.

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

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.

aelmore

This is exactly what I needed! Thanks so much!

Amy