COUNTIF(s) for date range on referenced sheet

Joelle Roehl

Hello,

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!

Mike Wilday

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?

Joelle Roehl

I realized I was missing a couple of the end ) so I tried both again, but still having the same error.

Mike Wilday

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?

Joelle Roehl

It did work! Thank you so much.

Mike Wilday

Awesome. Glad I could you out!