COUNTIF(s) for date range on referenced sheet

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!

Find more posts tagged with

Accepted answers

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?

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.