COUNTIF(s) for date range on referenced sheet



I've been searching all forums and can't seem to find a formula to fit what I need. I feel like it should be relatively simple but nothing I've tried has worked.

I'm trying to get a count of patients who had their consultation during the year of 2020. I am referencing another sheet for the count.

The columns are:

I use the "Info Added to NexTech" as a way to count the total in a separate count, so I thought it could be used, but maybe it's unnecessary? I've tried with and without and it still won't calculate.

The first (include the checkbox count):

=COUNTIFS({Reference1}, 1, AND({Reference2}, (@cell >= DATE(2020, 01, 01), @cell <= DATE(2020, 12, 31))

Reference 1 = "Info Added to NexTech" column

Reference 2 = "Initial Consult Date" column

The second (without the checkbox count):

=COUNTIF({Reference 2}, (@cell >= DATE(2020, 01, 01), @cell <= DATE(2020, 12, 31))

There are no blank date fields.

Thank you!

Best Answer

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Answer ✓

    Try this...

    =COUNTIFS({Reference1}, 1, {Reference2}, AND(@cell >= DATE(2020, 01, 01), @cell <= DATE(2020, 12, 31))

    Make sure you're references are only the one column selected and multiple columns too. Did that work?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!