Countifs formula to count a value within a date range?

Our company has ongoing surveys and the raw data is organized by different dates (dates the survey was complete). We want to only count the cells for a particular date range in our calculations sheet to give us the survey results for that time frame.

For example: I only want smartsheet to look at the surveys completed between 1/1/2021 and 12/31/2021 and within that, only look at the values that equal "5" for a particular question (column).

Would this be a countifs formula? If so, how would it be structured? The goal is some sort of automation as more surveys are completed.

Find more posts tagged with

Sheets

Formulas

Reports

Solution Center

Automations

Accepted answers

All comments

Paul Newcome

You have a few different options. If you want specific dates:

=COUNTIFS([Date Column]:[Date Column], AND(@cell>= DATE(2021, 01, 01), @cell<= DATE(2021,12,31)), Question:Question, @cell = "5")

If you just want a year:

=COUNTIFS([Date Column]:[Date Column], IFERROR(YEAR(@cell), 0) = 2021, Question:Question, @cell = "5")

If you want a specific year and month (January in this example):

=COUNTIFS([Date Column]:[Date Column], AND(IFERROR(YEAR(@cell), 0) = 2021, IFERROR(MONTH(@cell), 0) = 1), Question:Question, @cell = "5")

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.